-- Demo case: Researching overhead of index advanced compression on 12.2
-- Date: 10-03-2017
-- References: https://richardfoote.wordpress.com/2017/03/10/12-2-index-advanced-compression-high-part-iv-the-width-of-a-circle/
-- Demo run on Oracle version: 12.2.0.1.0 and OEL 3.8.13-118.3.2.el6uek.x86_64


-- Setup data structure
create table bowie_noncomp (id number, code number, name varchar2(42));
create index bowie_code_idx_noncomp on bowie_noncomp(code) nocompress;

create table bowie_comp (id number, code number, name varchar2(42));
create index bowie_code_idx_comp on bowie_comp(code) compress advanced high;


-- Run DML with non compressed index
insert into bowie_noncomp select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;
update bowie_noncomp set code = 42 where id between 250000 and 499999;

-- Run DML with comp index advanced compression "high" 
insert into bowie_comp select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;
update bowie_comp set code = 42 where id between 250000 and 499999;


-- SQL trace & TKPROF output
insert into bowie_noncomp select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.02          0          0          0           0
Execute      1      7.02       7.36         15       8145      93140     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      7.09       7.38         15       8145      93140     1000000

update bowie_noncomp set code = 42 where id between 250000 and 499999
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          1           0
Execute      1     23.63      25.09          0       6318    1781923      250000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     23.64      25.09          0       6320    1781924      250000

insert into bowie_comp select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1     24.03      24.73          1       9188      90496     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     24.04      24.74          1       9188      90496     1000000

update bowie_comp set code = 42 where id between 250000 and 499999
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          1           0
Execute      1     50.59      51.60          0       5981    1781237      250000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     50.59      51.60          0       5983    1781238      250000


-- Increase of runtime is CPU only - need to profile with perf
perf record -e cpu-cycles -o /tmp/insert_noncomp.out -g -p <PID>
perf record -e cpu-cycles -o /tmp/update_noncomp.out -g -p <PID>
perf record -e cpu-cycles -o /tmp/insert_comp.out -g -p <PID>
perf record -e cpu-cycles -o /tmp/update_comp.out -g -p <PID>

perf script -i /tmp/insert_noncomp.out | ./FlameGraph/stackcollapse-perf.pl > /tmp/insert_noncomp.out.folded
perf script -i /tmp/update_noncomp.out | ./FlameGraph/stackcollapse-perf.pl > /tmp/update_noncomp.out.folded
perf script -i /tmp/insert_comp.out | ./FlameGraph/stackcollapse-perf.pl > /tmp/insert_comp.out.folded
perf script -i /tmp/update_comp.out | ./FlameGraph/stackcollapse-perf.pl > /tmp/update_comp.out.folded

./FlameGraph/flamegraph.pl /tmp/insert_noncomp.out.folded > /tmp/insert_noncomp.svg
./FlameGraph/flamegraph.pl /tmp/update_noncomp.out.folded > /tmp/update_noncomp.svg
./FlameGraph/flamegraph.pl /tmp/insert_comp.out.folded > /tmp/insert_comp.svg
./FlameGraph/flamegraph.pl /tmp/update_comp.out.folded > /tmp/update_comp.svg


-- URLs of FlameGraphs
---- DML with non compressed index
http://www.soocs.de/public/research/170310_overhead_index_advanced_compression_insert_noncomp.svg
http://www.soocs.de/public/research/170310_overhead_index_advanced_compression_update_noncomp.svg

---- DML with comp index advanced compression "high"
http://www.soocs.de/public/research/170310_overhead_index_advanced_compression_insert_comp.svg
http://www.soocs.de/public/research/170310_overhead_index_advanced_compression_update_comp.svg


-- ORADEBUG translation of C functions
oradebug setmypid
oradebug doc component

...
Components in library GENERIC:
--------------------------
...
  KDI			       Index Layer (kdi)
    KDIADHI		       Index Layer Compress Advanced High ((null))
    KDIADLO		       Index Layer Compress Advanced Low ((null))
    KDIL		       Index Load (kdil)
      KDILADHI		       Index Load Compress Advanced High ((null))
      KDILADLO		       Index Load Compress Advanced Low ((null))
    KDIS		       Index Split (kdis)
      KDISADHI		       Index Split Compress Advanced High ((null))
      KDISADLO		       Index Split Compress Advanced Low ((null))
    KDXT		       Index Reorg (kdxt)
      KDXTADHI		       Index ADLO Compress Advanced High ((null))
      KDXTADLO		       Index Reorg Compress Advanced Low ((null))
    KDIM		       Index Coalesce (kdim)
      KDIMADHI		       Index Coalesece Compress Advanced High ((null))
      KDIMADLO		       Index Coalesece Compress Advanced Low ((null))
    KDIF		       Index Scan (kdif)
      KDIFADHI		       Index Scan Compress Advanced High ((null))
      KDIFADLO		       Index Scan Compress Advanced Low ((null))
    KDIZADHI     	      Advanced High Index Codec (kdizoltp)
    KDIZADLO		       Advanced Low Index Codec ((null))
    KDIDML		       Index DML ((null))
      KDIDMLADHI	       Index DML Compress Advanced High ((null))
      KDIDMLADLO	       Index DML Compress Advanced Low ((null))
...