Thursday, January 21, 2016

How many CPU consume the index during insert ?

I recently researched the question "how many CPU consume the index during insert "
I did an empty table and load the data into it from pattern table size of 20MB.
 

Zero (base) run - loaded into a table without indexes and measured baseline statistics.
 

1nd run: created 10 indexes 1-column each and loaded data from the pattern table again.
And compared the CPU-statistics from each download.


2rd run: created 10 indexes 2-column each ...

3th run: created 10 indexes 3-column each

4th run: created 10 indexes 4-columns each

5th run: created 10 indexes 5-columns each

=============================================
grant dba to yu identified by yu;
conn yu/yu

drop table base;
drop table test;
create table BASE ... ;

drop table test;
purge recyclebin;
create table TEST as select * from BASE where 1=2;

conn yu/yu

insert into TEST select * from BASE;
commit;

set pages 100
col name for a50
select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic#
and s.sid = userenv('sid') and s.value>0
and n.name in ('CPU used by this session','db block changes','session logical reads','redo size')
order by n.name;
  
 
Run 1                          
create index I1 on test(1);
create index I2 on test(2);
create index I3 on test(3);
create index I4 on test(4);
create index I5 on test(5);
create index I6 on test(6);
create index I7 on test(7);
create index I8 on test(8);
create index I9 on test(9);
create index I10 on test(10);
Run 2
create index I1 on test(1,2);
create index I2 on test(2,3);
create index I3 on test(3,4);
create index I4 on test(4,5);
create index I5 on test(5,6);
create index I6 on test(6,7);
create index I7 on test(7,8);
create index I8 on test(8,9);
create index I9 on test(9,10);
create index I10 on test(10,1);
Run 3
create index I1  on test(1,2,3);
create index I2  on test(2,3,4);
create index I3  on test(3,4,5);
create index I4  on test(4,5,6);
create index I5  on test(5,6,7);
create index I6  on test(6,7,8);
create index I7  on test(7,8,9);
create index I8  on test(8,9,10);
create index I9  on test(9,10,1);
create index I10 on test(10,1,2);
Run 4
create index I1  on test(1,2,3,4);
create index I2  on test(2,3,4,5);
create index I3  on test(3,4,5,6);
create index I4  on test(4,5,6,7);
create index I5  on test(5,6,7,8);
create index I6  on test(6,7,8,9);
create index I7  on test(7,8,9,10);
create index I8  on test(8,9,10,1);
create index I9  on test(9,10,1,2);
create index I10 on test(10,1,2,3);

Run 5
create index I1  on test(1,2,3,4,5);
create index I2  on test(2,3,4,5,6);
create index I3  on test(3,4,5,6,7);
create index I4  on test(4,5,6,7,8);
create index I5  on test(5,6,7,8,9);
create index I6  on test(6,7,8,9,10);
create index I7  on test(7,8,9,10,1);
create index I8  on test(8,9,10,1,2);
create index I9  on test(9,10,1,2,3);
create index I10 on test(10,1,2,3,4);

=============================================

The raw results in 1/100 second are in table:



You can see: baseline is 25 1/100 sec.
But with 10 indexes 1-column is 554 .

 Let calculate one 1-col index CPU time:  (554-25)/10 = 52.9  1/100 sec.
So, insert into one 1-col index takes 52.9/25 = 2.12  more CPU time as compared to insert into the table.

The normalized table is :






Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...