In the part
I, part
II and part
III of the new advanced index compression level of “High”, we discussed how
it can significantly decrease the size of index in a manner not previously possible.
This can result in the significant reduction of index storage and potential
subsequent reduction in IO and memory related overheads.
However for applications which have tables/indexes that are very
sensitive regarding DML performance, then need to exercise the caution before
compressing the indexes in this manner. This can be due to the extra CPU
overheads and file IO wait times that can result in maintaining the highly
compressed index structures.
To quickly demonstrate this point, let’s first look at the CPU times
when performing DML with an associated index that has no compression and index
with advanced index compression LOW/HIGH.
demo@ORA12C>
create table stage
2 as
3
select a.* ,
4
mod(rownum,10) as x1,
5
rownum as x2
6 from
big_table a;
Table created.
demo@ORA12C>
create table t_uncompress as
2
select *
3 from
stage
4
where 1 = 0;
Table created.
demo@ORA12C>
create table t_compress_low as
2
select *
3 from
stage
4
where 1 = 0;
Table created.
demo@ORA12C>
create table t_compress_high as
2
select *
3 from
stage
4
where 1 = 0;
Table created.
demo@ORA12C>
create index t_uncompress_idx on t_uncompress( x1,x2 );
Index created.
demo@ORA12C>
create index t_compress_low_idx on t_compress_low( x1,x2 ) COMPRESS advanced low;
Index created.
demo@ORA12C>
create index t_compress_high_idx on t_compress_high( x1,x2 ) COMPRESS advanced high;
Index created.
demo@ORA12C>
@tkfilename.sql
D:\APP\VNAMEIT\VIRTUAL\diag\rdbms\ora12c\ora12c\trace\ora12c_ora_10108.trc
demo@ORA12C>
@tktrace.sql
PL/SQL
procedure successfully completed.
demo@ORA12C>
insert into t_uncompress select * from stage;
1000000 rows
created.
demo@ORA12C>
commit;
Commit complete.
demo@ORA12C>
insert into t_compress_low select * from stage;
1000000 rows
created.
demo@ORA12C>
commit;
Commit
complete.
demo@ORA12C>
insert into t_compress_high select * from stage;
1000000 rows
created.
demo@ORA12C>
commit;
Commit complete.
demo@ORA12C>
update t_uncompress set
2
x1 = 42,
3
x2 = object_id
4
where id <= 250000;
250000 rows
updated.
demo@ORA12C>
commit;
Commit
complete.
demo@ORA12C>
update t_compress_low set
2
x1 = 42,
3
x2 = object_id
4 where id <= 250000;
250000 rows
updated.
demo@ORA12C>
commit;
Commit
complete.
demo@ORA12C>
update t_compress_high set
2
x1 = 42,
3
x2 = object_id
4
where id <= 250000;
250000 rows
updated.
demo@ORA12C>
commit;
Commit
complete.
demo@ORA12C>
exit
Disconnected
from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
And tkprof shows this.
insert into
t_uncompress select * from stage
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.01 0.01 0 0 0 0
Execute 1
10.63 47.28
19420 48712 3118345
1000000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
10.65 47.29 19420
48712 3118345 1000000
Misses in library
cache during parse: 1
Misses in
library cache during execute: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ---------- ---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL T_UNCOMPRESS
(cr=48790 pr=19420 pw=0 time=47270999 us starts=1)
1000000
1000000 1000000 TABLE ACCESS FULL STAGE (cr=19431 pr=19419
pw=0 time=17643165 us starts=1 cost=4895 size=128000000 card=1000000)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.11 0.11
direct path read 167 0.46 19.76
db file sequential read 1 0.00 0.00
log buffer space 115 0.83 13.98
PGA memory operation 1 0.00 0.00
log file switch (private strand flush
incomplete)
4 0.19 0.51
latch: cache buffers chains 2 0.04 0.04
latch: shared pool 2 0.05 0.10
latch: enqueue hash chains 1 0.07 0.07
log file sync 1 0.27 0.27
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID:
2gh474uvyg8n5 Plan Hash: 2239145125
insert into
t_compress_low select * from stage
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.04 0.02 0 0 0 0
Execute 1
11.48
56.62 19421 47027
3082263 1000000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
11.52 56.65
19421 47027 3082263
1000000
Misses in
library cache during parse: 1
Misses in
library cache during execute: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL T_COMPRESS_LOW
(cr=47035 pr=19420 pw=0 time=56594718 us starts=1)
1000000
1000000 1000000 TABLE ACCESS FULL STAGE (cr=19431 pr=19419
pw=0 time=4083223 us starts=1 cost=4895 size=128000000 card=1000000)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
db file sequential read 2 0.03 0.05
direct path read 296 0.45 35.72
log buffer space 67 0.52 6.26
log file switch (private strand flush
incomplete)
2 0.19 0.22
log file switch completion 1 1.13 1.13
PGA memory operation 1 0.00 0.00
log file sync 1 0.27 0.27
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID:
d8f4nsdrw519y Plan Hash: 2239145125
insert into
t_compress_high select * from stage
call count cpu
elapsed disk query
current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.03 0.03 0 0 0 0
Execute 1
37.81
61.60 19428
47400 2988192 1000000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
37.84 61.63 19428
47400 2988192 1000000
Misses in
library cache during parse: 1
Misses in
library cache during execute: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL T_COMPRESS_HIGH
(cr=47406 pr=19427 pw=0 time=61573558 us starts=1)
1000000
1000000 1000000 TABLE
ACCESS FULL STAGE (cr=19431 pr=19419 pw=0 time=8773917 us starts=1 cost=4895
size=128000000 card=1000000)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
db file sequential read 9 0.68 1.14
direct path read 111 0.44 12.36
PGA memory operation 68 0.00 0.01
buffer exterminate 121 0.02 1.09
log buffer space 55 0.42 7.06
latch: object queue header operation 1 0.00
0.00
log file switch (private strand flush
incomplete)
1 0.13 0.13
log file switch completion 1 0.46 0.46
log file sync 1 0.55 0.55
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
update
t_uncompress set
x1 = 42,
x2 = object_id
where id <=
250000
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.01 0.00 0 0 2 0
Execute 1
6.30
46.55 20967 42944
1783004 250000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
6.31 46.56 20967
42944 1783006 250000
Misses in
library cache during parse: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
UPDATE T_UNCOMPRESS (cr=42953
pr=20967 pw=0 time=46555263 us starts=1)
250000
250000 250000 TABLE ACCESS FULL T_UNCOMPRESS (cr=39055
pr=19736 pw=0 time=3539014 us starts=1 cost=2 size=52 card=1)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
db file scattered read 537 1.25 28.52
db file sequential read 1232 0.28 8.45
db file parallel read 3 0.28 0.51
log buffer space 11 0.82 1.51
log file switch completion 2 0.13 0.17
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
update
t_compress_low set
x1 = 42,
x2 = object_id
where id <=
250000
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.03 0.00 0 0 2 0
Execute 1
7.25
31.49 20804 42328
1780726 250000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
7.28 31.49 20804
42328 1780728 250000
Misses in
library cache during parse: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
UPDATE T_COMPRESS_LOW (cr=42343
pr=20804 pw=0 time=31495999 us starts=1)
250000
250000 250000 TABLE ACCESS FULL T_COMPRESS_LOW (cr=39055
pr=19745 pw=0 time=750610 us starts=1 cost=2 size=52 card=1)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
db file scattered read 537 0.33
15.06
db file sequential read 1060 0.25 7.12
db file parallel read 3 0.28 0.57
log buffer space 16 0.18 0.79
log file switch completion 2 0.25 0.35
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
update
t_compress_high set
x1 = 42,
x2 = object_id
where id <=
250000
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 2 0
Execute 1
14.91
32.16 20464 41389
1776994 250000
Fetch 0
0.00 0.00 0 0
0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
14.91 32.16 20464
41389 1776996 250000
Misses in
library cache during parse: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
UPDATE T_COMPRESS_HIGH (cr=41389
pr=20464 pw=0 time=32164422 us starts=1)
250000
250000 250000 TABLE ACCESS FULL T_COMPRESS_HIGH (cr=39055
pr=19765 pw=0 time=2497389 us starts=1 cost=2 size=52 card=1)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
db file scattered read 537 0.27 9.99
db file sequential read 700 0.35 4.51
PGA memory operation 37 0.00 0.00
db file parallel read 3 0.30 0.62
log buffer space 13 0.22 1.22
log file switch completion 1 0.27 0.27
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
We see there’s a significant increase in (CPU) timings when both
inserting into the table and when updating the highly compressed indexed
column.
Therefore we need to consider the impact of dml’s performance due to
the additional resource required in maintaining the highly compressed indexes.
No comments:
Post a Comment