Вебинар: новые возможности СУБД 12.2

Вебинар: новые возможности СУБД 12.2

http://connect.fors.ru/p41753521/

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 :

exec dbms_stats.gather_system_stats('EXADATA');

Новая опция появилась в сборе системной статистики:
exec dbms_stats.gather_system_stats('EXADATA');

На какие параметры она влияет? Возьмем базу, в которой никогда системная статистика не собиралась, соберем ее и посмотрим, какие параметры изменились:

SQL> select * from aux_stats$;
SNAME           PNAME            PVAL1 PVAL2
--------------- ----------- ---------- ----------------
SYSSTATS_INFO   STATUS                 COMPLETED
SYSSTATS_INFO   DSTART                 11-30-2012 18:21
SYSSTATS_INFO   DSTOP                  11-30-2012 18:21
SYSSTATS_INFO   FLAGS                1
SYSSTATS_MAIN   CPUSPEEDNW  2588.93427
SYSSTATS_MAIN   IOSEEKTIM           10
SYSSTATS_MAIN   IOTFRSPEED        4096
SYSSTATS_MAIN   SREADTIMSYSSTATS_MAIN   MREADTIMSYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR
13 rows selected.

SQL> set timing on
SQL> exec dbms_stats.gather_system_stats('EXADATA');
PL/SQL procedure successfully completed.
Elapsed: 00:00:37.97
SQL> select * from aux_stats$;
SNAME           PNAME            PVAL1 PVAL2
--------------- ----------- ---------- ----------------
SYSSTATS_INFO   STATUS                 COMPLETED
SYSSTATS_INFO   DSTART                 01-14-2013 15:06
SYSSTATS_INFO   DSTOP                  01-14-2013 15:06
SYSSTATS_INFO   FLAGS                1
SYSSTATS_MAIN   CPUSPEEDNW        2586
SYSSTATS_MAIN   IOSEEKTIM            3
SYSSTATS_MAIN   IOTFRSPEED      164076
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC               128
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR
13 rows selected.

MBRC = 128 * 8K = 1M.

Интересно, команда dbms_stats.gather_system_stats('EXADATA') измеряет ли что-то или записывает одни и те же данные? Для продолжения эксперимента возьмем вторую БД с другим размером блока (32К) и повторим запуск:

SNAME          PNAME            PVAL1 PVAL2
-------------- ----------- ---------- ----------------
SYSSTATS_INFO  STATUS                 COMPLETED
SYSSTATS_INFO  DSTART                 12-21-2012 11:34
SYSSTATS_INFO  DSTOP                  12-27-2012 10:38
SYSSTATS_INFO  FLAGS                1
SYSSTATS_MAIN  CPUSPEEDNW  2602.46361
SYSSTATS_MAIN  IOSEEKTIM           10
SYSSTATS_MAIN  IOTFRSPEED        4096
SYSSTATS_MAIN  SREADTIM          .708
SYSSTATS_MAIN  MREADTIM         5.327
SYSSTATS_MAIN  CPUSPEED          2591
SYSSTATS_MAIN  MBRC                11
SYSSTATS_MAIN  MAXTHR
SYSSTATS_MAIN  SLAVETHR

SQL> exec dbms_stats.gather_system_stats('EXADATA');
PL/SQL procedure successfully completed.

SQL> select * from aux_stats$;
SNAME          PNAME            PVAL1 PVAL2
-------------- ----------- ---------- ----------------
SYSSTATS_INFO  STATUS                 COMPLETED
SYSSTATS_INFO  DSTART                 01-14-2013 15:21
SYSSTATS_INFO  DSTOP                  01-14-2013 15:21
SYSSTATS_INFO  FLAGS                1
SYSSTATS_MAIN  CPUSPEEDNW        2580
SYSSTATS_MAIN  IOSEEKTIM            3
SYSSTATS_MAIN  IOTFRSPEED      235038
SYSSTATS_MAIN  SREADTIM          .708
SYSSTATS_MAIN  MREADTIM         5.327
SYSSTATS_MAIN  CPUSPEED          2591
SYSSTATS_MAIN  MBRC                32
SYSSTATS_MAIN  MAXTHR
SYSSTATS_MAIN  SLAVETHR


Очевидно, что поменялись те же самые 4 параметра.
MBRC получился тот же самый: 32*32К= 1M, а вот IOTFRSPEED подрос.
На CPUSPEED, SREADTIM и MREADTIM опция 'EXADATA' не влияет.