Prince2 Certification

The second week of this year I had spend on Prince2 training. I can say, that the training was intense - every day I had to read ~80 pages and learn a lot of stuff. Additionally, the training included two exams (second and fifth days) :). Well, today I have received the result and I am glad to say that I'm a qualified Prince2 Practitioner.

dsSize function and TTSize utility in TimesTen

I didn't have an opportunity to write anything lately, because had spent last two months in Nottingham.  But now I have a couple days to relax, so I have decided to write about one useful function and one utility in TimesTen. 7.0.5 version is used for the demo (I understand that this is a very old version, but I am a lazy person :) )

As you know, TimesTen stores all information into the memory (RAM) and obviously there are limits for RAM space (nowadays you can buy a server with a couple of TBs of RAM, but still it is not 100 TBs). In this situation, TimesTen users should know how to control the space and how to determine the necessary volume of memory for your  system. I have already written one post about the memory allocation here, but now I would like to describe the following:

• dsSize function
• ttSize utility

The first one is used to reports the current sizes of the permanent and temporary database partitions. For example:

[oracle@localhost info]$ ttisql

Copyright (c) 1996-2008, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.


Command> connect dslab1;
Enter password for 'ttdemo': 
Connection successful: DSN=dslab1;UID=ttdemo;DataStore=/app/oracle/product/datastore/dslab1;DatabaseCharacterSet=CL8MSWIN1251;ConnectionCharacterSet=US7ASCII;DRIVER=/app/oracle/product/7.0.5/TimesTen/tt70/lib/libtten.so;LogDir=/app/oracle/product/datastore/dslab1/log;PermSize=120;TempSize=50;TypeMode=0;
(Default setting AutoCommit=1)
Command>
Command> dssize;

  PERM_ALLOCATED_SIZE:      122880
  PERM_IN_USE_SIZE:         1222
  PERM_IN_USE_HIGH_WATER:   1372
  TEMP_ALLOCATED_SIZE:      51200
  TEMP_IN_USE_SIZE:         3587
  TEMP_IN_USE_HIGH_WATER:   3587

Command>

I just created the connection and checked current sizes of the permanent and temporary database partitions. If you add more information in your database, the size will grow. For example, let's create two tables and insert a couple of rows and after that compare results:

[oracle@localhost sql]$ ttisql -f lab1.sql dslab1

Copyright (c) 1996-2008, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=dslab1";
Enter password for 'ttdemo': 
Connection successful: DSN=dslab1;UID=ttdemo;DataStore=/app/oracle/product/datastore/dslab1;DatabaseCharacterSet=CL8MSWIN1251;ConnectionCharacterSet=US7ASCII;DRIVER=/app/oracle/product/7.0.5/TimesTen/tt70/lib/libtten.so;LogDir=/app/oracle/product/datastore/dslab1/log;PermSize=120;TempSize=50;TypeMode=0;
(Default setting AutoCommit=1)

run "lab1.sql";

set autocommit 0;

create table emp (id number not null,
                  creation date, 
                  name_1   varchar2(40),
                  name_2   varchar2(40),
                  surname  varchar2(40),
                  dept_id  number,
                  Primary key (id) );

commit;
create table dept (dept_id    number not null,
                   dept_name  varchar2(40), 
                   Primary key (dept_id) );

commit;

insert into dept (dept_id, dept_name) values (1, 'Sales');
1 row inserted.

...

insert into dept (dept_id, dept_name) values (9, 'CEO office');
1 row inserted.

commit;

insert into emp (id, creation, name_1, name_2, surname, dept_id) values (1, sysdate, 'Pety1','Petrovich1', 'Ivanov1',1);
1 row inserted.

…

insert into emp (id, creation, name_1, name_2, surname, dept_id) values (100, sysdate, 'Pety100','Petrovich100', 'Ivanov100',9);
1 row inserted.

commit;

exit;
Disconnecting...
Done.


Command> tables;
   ...
  TTDEMO.DEPT
  TTDEMO.EMP
   ...
Command> dssize;

  PERM_ALLOCATED_SIZE:      122880
  PERM_IN_USE_SIZE:         1372
  PERM_IN_USE_HIGH_WATER:   1372
  TEMP_ALLOCATED_SIZE:      51200
  TEMP_IN_USE_SIZE:         3650
  TEMP_IN_USE_HIGH_WATER:   3713

We see the difference in PERM_IN_USE_SIZE values as expected. ttSize utility is used to estimates the amount of space that a given table in the data store will consume when it grows to include particular amount of rows. For example, we created the table emp, so let's have a look how we can use ttSize utility:

[oracle@localhost ~]$ ttSize -tbl emp -rows 1000000 dslab1
Enter password for 'ttdemo': 

Rows = 1000000

Total in-line row bytes = 207078073

Indexes:
  T-tree index TTDEMO.EMP adds 10541265 bytes
  Total index bytes = 10541265

Total = 217619338

This command shows the approximate amount of memory required for storing 1M rows in emp table.