Search This Blog


Thursday, January 21, 2016

How many CPU take the index during insert ?

I recently researched the question "how many CPU take 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;

set pages 100
col name for a50
select, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic#
and s.sid = userenv('sid') and s.value>0
and in ('CPU used by this session','db block changes','session logical reads','redo size')
order by;
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 :

Wednesday, December 2, 2015

What content will be cached in Exadata Smart Flash Cache ?


As we know, there are 2 Flash Cache modes in Exadata: WhiteThrough and WriteBack.

The WhiteThrough mode is obvious: the FC content is read from hard disk, so temporary content (temporary tables, sorts, hash join temporary data) cannot be written to FC in this mode.

In order to improve Write-performance for user processes we usually enable WriteBack mode the cells. In this mode FC is opened for writes from database level. And some time ago I asked the question in Oracle Community and community experts said that "FC will cache temporary content".

But real life have to have some doubts in expert statement.
Look at AWR report for our last test.
It is easy to distinct the temporary tablespace (BIG16K_TMP%) and other tablespaces by the name.
Look at the read and write time column (ms) and compare values:

 You can see the big difference in read and write time for TEMP and not TEMP datafiles.
50-60-70 ms - it cannot be a flash latency, it is definitely a HDD latency.

Let look into the Express:

We clearly see the 0-1 ms latency for Flash and 10+ ms latency for HDD.

So, who is right? Is temporary content cached in Flash Cache or not ?

What we found:
Oracle divide the IO into 2 classes: Large IO and Small IO.
Large IOs are > 128K
Small IOs are <= 128K.

(Oracle Exadata Storage Server Software User's Guide, page 7-2: 
small means less than or equal to 128 KB, and large means more than 128 KB).

So, now we understand : 
Large IOs go to the HDD 
Small IOs go to Flash Cache.