SGA close look?

SQL> select * from x$ksmsd;

ADDR INDX INST_ID KSMSDNAM KSMSDVAL
---------------- ---------- ---------- -------------------- ----------
000000006000C1D0 0 1 Fixed Size 2144824
000000006000C1F0 1 1 Variable Size 687867336
000000006000C210 2 1 Database Buffers 905969664
000000006000C230 3 1 Redo Buffers 7430144

SQL> show sga

Total System Global Area 1603411968 bytes
Fixed Size 2144824 bytes
Variable Size 687867336 bytes
Database Buffers 905969664 bytes
Redo Buffers 7430144 bytes

SGA close look?

Test case of Event 10224 - Dump Index Block Splits / Deletes

alter system set events '10224 trace name context forever, level 2'

create tablespace work datafile 'C:\ORACLE\ORADATA\DEMO\WORK.DBF' size 20M;

create table test ( x number ) tablespace work;

create unique index x_ind on test (x) tablespace work;

analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/

begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/

SQL> oradebug setmypid
Statement processed.

SQL> oradebug event 10224 trace name context forever,level 2
Statement processed.

SQL> begin
for i in 1..100 2 0 loop
insert into test values (i);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> analyze index x_ind validate structure;

Index analyzed.

column name format a15
column blocks headingSQL> "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 32 1 16351

SQL> SQL>
SQL> alter index x_ind rebuild;

Index altered.

SQL> analyze index x_ind validate structure;

Index analyzed.

SQL> column name format a15
column blocks hSQL> eading "ALLOCATED|BLOCKS"
colSQL> umn lf_blks heading "LEAF|BLOCKS"
column br_blks headiSQL> ng "BRANCH|BLOCKS"
column SQL> Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381

SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> delete from test where x <500;

489 rows deleted.

SQL> commit;

Commit complete.

SQL> delete from test;

511 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze index x_ind validate structure;

Index analyzed.

SQL> column name format a15
cSQL> olumn blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty headiSQL> SQL> SQL> ng "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
SQL>
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381

SQL> begin
for i in 1..1000 loop
insert into test values (i);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> analyze index x_ind validate structure;

column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
SQL> column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lfblks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381

SQL> delete from test;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/


PL/SQL procedure successfully completed.

SQL> SQL> commit;

Commit complete.

SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blSks heading "BRANCH|BLOCKS"
cSQL> olumn Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365

SQL> delete from test;

10000 rows deleted.

SQL> commit;

Commit complete.


############# I have deleted the rows but as we can see blocks are there .########################

Actually FREE BLOCKS are added to free list but not linked to index structure.Rebuilding the index will be linked.

kdimod adding block to free list,dba 0x21c0c018,time 16:46:33.868
kdimod adding block to free list,dba 0x21c0c019,time 16:46:33.877
kdimod adding block to free list,dba 0x21c0c014,time 16:46:34.86
kdimod adding block to free list,dba 0x21c0c015,time 16:46:34.153
kdimod adding block to free list,dba 0x21c0c013,time 16:46:34.277
kdimod adding block to free list,dba 0x21c0c011,time 16:46:34.352
kdimod adding block to free list,dba 0x21c0c012,time 16:46:34.355
kdimod adding block to free list,dba 0x21c0c017,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c016,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c01b,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c01a,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c00e,time 16:46:34.669
kdimod adding block to free list,dba 0x21c0c00b,time 16:46:34.733
kdimod adding block to free list,dba 0x21c0c00d,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00c,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00f,time 16:46:34.848
kdimod adding block to free list,dba 0x21c0c010,time 16:46:34.911



SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365

SQL>

SQL> alter index x_ind rebuild;

Index altered.

SQL> analyze index x_ind validate structure;
column name forma
Index analyzed.

SQL> t a15
column bloSQL> cks heading "ALLOCATED|BLOCKS"
column lfSQL> _blks heading "LEAF|BLOCKS"
SQL> column br_blks heading "BRANCH|BLOCKS"
columSQL> n Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 1 0 16383

SQL>

Systemtap - DTRACE for Linux?

I used to work on Solaris 10 , which has DTRACE far best troubleshooting utillity.

Had a chance to talk with first DTrace user Jarod Jenson.So if you need any help I am sure he will give you some hint..

Since Oracle is "forcing" Linux , in my new job guess what ... I am running Linux and of course once a while you will run into some problem but DTrace is not there ....

but Systemtap is very promissing...


SystemTap provides free software (GPL) infrastructure to simplify the gathering of information about the running Linux system.

Here is official page:

http://sourceware.org/systemtap/


also there is good link from Oracle Open World 2007

http://blogs.oracle.com/ezannoni/gems/OOW20073.pdf

Direct memory access using Pro*C /C code

I stared this blog by posting my crap that I wrote some time ago.
I have been to busy last few years doing regular database crap 24 by 7. You know what I mean.

Here is link to site which is hosted by Pete Finningan ( very good site about Oracle Security ).
Many usefull information there... so if you do not like my paper I am sure his post will make you worry ( hackers attacks... )


http://www.petefinnigan.com/Storing_Data_Directly_From_Oracle_SGA.pdf

I write things and let other to publish.... so they can blame them for my faults..