Advanced index compression (HIGH) - Part V

 

Have discussed enough in the past how advanced index compression can automatically determine not only the correct number of columns to compress, but also the correct number of columns to compress within the specific leaf block of the index.

 

Also discussed how ordering of column within the index can be very important including with regard to the possible compression capability of an index.

 

Ordering of the columns in the index is still an important criteria for Key compressed b*Tree index, However with Advanced index compression (HIGH) this ordering is not an potential criteria( that is even with the columns having high distinct values in the leading portion of the index can be compressed very effectively using Advanced index compression techniques)

 

To illustrate, here is a simple example.

 

demo@PDB1> create table t as

  2  select *

  3  from big_table;

 

Table created.

 

The table T has around million rows, but now going to build an index on the effectively unique ID column in the leading position, followed by other columns that has many duplicate values.

 

demo@PDB1> select c.column_name, t.num_rows,c.num_distinct,c.num_nulls

  2  from user_tables t,

  3      user_tab_col_statistics c

  4  where t.table_name = c.table_name

  5  and t.table_name ='T'

  6  and c.column_name in ('ID','OWNER') ;

 

COLUMN_NAME            NUM_ROWS NUM_DISTINCT  NUM_NULLS

-------------------- ---------- ------------ ----------

OWNER                   1000000           17          0

ID                      1000000      1000000          0

 

demo@PDB1> create index t_idx on t(id,owner) nologging;

 

Index created.

 

demo@PDB1> col index_name format a10

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      DISABLED             2905

 

The index without compression has 2905 leaf blocks.

 

As the leading column is effectively unique, we simply can’t now compress this index effectively. That’s because the compression requires there to be duplicate index entries. If the leading column has few or no duplicate values, then by compressing the index oracle is effectively creating a prefixed entry with in the leaf block for each and every index entry. The whole point of index compression is to effectively de-duplicate index values, but there’s nothing to de-duplicate if there are no repeating values in the leading column of the index.

 

If we just attempt to compress fully the index, then

 

demo@PDB1> alter index t_idx rebuild compress nologging;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ENABLED              3745

 

Results in a bigger not a smaller index. The number of leaf blocks gone up from 2905 to 3745.

If we attempt to compress the first column then it makes no difference to the inefficiency of index compression. Since with compress 1 the number of entries in the prefixed table remains the same.

 

demo@PDB1> alter index t_idx rebuild compress 1 nologging;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ENABLED              3745

 

The good thing with advanced index compression is that we can give it a go, but it will “not” result in a bigger index structure.  If there is nothing to compress within the leaf block, oracle just ignores it and moves to the next leaf block. If there is nothing to compress within the index, then index remains the same as if it is not been compressed.

 

demo@PDB1> alter index t_idx rebuild compress advanced low;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED LOW         2905

 

The index is now back to 2905 leaf blocks, the same way as it is not compressed at all. However with advanced index compression HIGH it is still possible to compress the index effectively and order of the column is no significant.

 

demo@PDB1> alter index t_idx rebuild compress advanced high;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH           0

 

demo@PDB1> exec dbms_stats.gather_index_stats( user,'T_IDX' );

 

PL/SQL procedure successfully completed.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH        1006

 

demo@PDB1>

 

We now have an index structure at just 1006 leaf blocks that is indeed smaller than the uncompressed index (2905 leaf blocks).

 

The best of both worlds, where Index Advanced Compression can compress just the part of an index where it effectively can and ignore and not make matters worse in any parts of the index where index compression is ineffective.

 

Indexes and not equal condition

 

The CBO has to deal with different possible scenario when trying to determine the most optimal execution plan for a sql statement. There are all sorts of shortcuts and assumptions that are coded into the CBO to make its life little easier. However these shortcuts can sometimes be problematic if they are not recognized and handled appropriately.

 

One of these shortcut worth noting is how the CBO handles the queries with NOT IN or NOT Equal conditions.

 

Typically when we have a condition where we just say NOT equal, we are basically suggesting we are interested in the vast majority of possible values with the exception of the value specified in the NOT equal condition, we want most values but not if this is a particular value.

 

For example a condition like this will say

 

WHERE FLAG <> ‘N’

 

Means we want all other possible values of FLAG, just not those with a specific value of N.

 

Oracle will not use an index if generally a relatively “high” percentage of rows are to be selected. It would generally be more efficient and less costly to simply perform a full Table scan if most rows are going to be returned.

 

Therefore the CBO simply ignores the index when costing the NOT Equal conditions.  The refusal of CBO to consider an index with NOT Equal condition can be easily illustrated.

 

demo@PDB1> create table t

  2  as

  3  select a.* ,

  4     decode(rownum,1,'Y','N') flag

  5  from big_table a;

 

Table created.

 

demo@PDB1>

demo@PDB1> select flag,count(*)

  2  from t

  3  group by flag;

 

F   COUNT(*)

- ----------

Y          1

N     999999

 

demo@PDB1> create index t_idx on t(flag);

 

Index created.

 

demo@PDB1> select column_name,histogram

  2  from user_tab_col_statistics

  3  where table_name ='T'

  4  and column_name ='FLAG';

 

COLUMN_NAME     HISTOGRAM

--------------- ---------------

FLAG            NONE

 

So only one row has the value that is FLAG <> ‘N’, which means an index to retrieve this one and only row would be an efficient and appropriate execution path.

 

Let’s now execute this simple statement.

 

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select *

  2  from t

  3  where flag <> 'N';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   500K|    60M|  5255   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |   500K|    60M|  5255   (1)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("FLAG"<>'N')

 

Without histograms, CBO assumed uniform distribution of rows and estimated that this predicate is about to return half the volume of rows from the table and picked up the Full table scan (FTS).

 

With histograms in place, the optimizer cardinality estimates got improved but still ignored the index.

 

demo@PDB1> exec dbms_stats.gather_table_stats(user,'T',options=>'GATHER AUTO',no_invalidate=>false);

 

PL/SQL procedure successfully completed.

 

demo@PDB1> select column_name,histogram

  2  from user_tab_col_statistics

  3  where table_name ='T'

  4  and column_name ='FLAG';

 

COLUMN_NAME     HISTOGRAM

--------------- ---------------

FLAG            FREQUENCY

 

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select *

  2  from t

  3  where flag <> 'N';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   182 | 23114 |  5255   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |   182 | 23114 |  5255   (1)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("FLAG"<>'N')

 

demo@PDB1> set autotrace off

 

However forcing the sql with hints, the CBO performed INDEX FULL Scan – means reading all the leaf nodes that contain all unwanted values and still an inefficient option – but not an INDEX RANGE SCAN.

 

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select /*+ index(t,t_idx) */ *

  2  from t

  3  where flag <> 'N';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 146203489

 

---------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |       |   182 | 23114 |  1829   (1)

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |   182 | 23114 |  1829   (1)

|*  2 |   INDEX FULL SCAN                   | T_IDX |   182 |       |  1825   (1)

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("FLAG"<>'N')

 

The right option would be rewrite the query in a “positive” sense and the index is now considered and used.

 

demo@PDB1> select *

  2  from t

  3  where flag = 'Y';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 767293772

 

----------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |       |   182 | 23114 |     7   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |   182 | 23114 |     7   (0)|

|*  2 |   INDEX RANGE SCAN                  | T_IDX |   182 |       |     3   (0)|

----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("FLAG"='Y')

 

 

If there are many distinct values in the column that are not N, but which in total still constitute a relatively small percentage of total rows, then it could be rewritten as follows which can make use of the index in an effective manner, by concatenating two separate range scans.

 

demo@PDB1> select *

  2  from t

  3  where flag < 'N' or flag > 'N';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 179818988

 

-------------------------------------------------------------------------------------

| Id  | Operation                             | Name            | Rows  |Cost (%CPU)|

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                      |                 |   273 |   12   (0)|

|   1 |  VIEW                                 | VW_ORE_1B35BA0F |   273 |   12   (0)|

|   2 |   UNION-ALL                           |                 |       |           |

|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T               |    91 |    5   (0)|

|*  4 |     INDEX RANGE SCAN                  | T_IDX           |    91 |    3   (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T               |   182 |    7   (0)|

|*  6 |     INDEX RANGE SCAN                  | T_IDX           |   182 |    3   (0)|

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("FLAG"<'N')

   6 - access("FLAG">'N')

       filter(LNNVL("FLAG"<'N'))

 

Perhaps a NOT BETWEEN could do the same.

 

demo@PDB1> select *

  2  from t

  3  where flag NOT BETWEEN 'N' and 'N';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 179818988

 

--------------------------------------------------------------------------------------

| Id  | Operation                             | Name            | Rows  | Cost (%CPU)|

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                      |                 |   273 |    12   (0)|

|   1 |  VIEW                                 | VW_ORE_1B35BA0F |   273 |    12   (0)|

|   2 |   UNION-ALL                           |                 |       |            |

|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T               |    91 |     5   (0)|

|*  4 |     INDEX RANGE SCAN                  | T_IDX           |    91 |     3   (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T               |   182 |     7   (0)|

|*  6 |     INDEX RANGE SCAN                  | T_IDX           |   182 |     3   (0)|

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("FLAG"<'N')

   6 - access("FLAG">'N')

       filter(LNNVL("FLAG"<'N'))

 

Be cautious when using NOT EQUAL conditions and the impact they have with your indexes.

 

Advanced index compression (HIGH) - Part IV

 

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.