Advanced index compression (HIGH) - Part I

 

Oracle introduced the advanced index compression in Oracle 12c Release 1 and have discussed that before.

 

With Oracle 12c Release 2 we can now use advanced index compression High to further improve the index compression ratio. Instead of simply de-duplicating the index entries with in the leaf block, High index compression uses more complex compression algorithm and store the index entries in compression units (similar to the one used with HCC in Exadata platform). The net result is generally the much better level of compression, but at the potential cost of more CPU resource to both access and maintain the index structures.

 

To get an idea about advanced index compression High, let’s re-run the demo from the previous example.

 

demo@ORA12C> create table t

  2  as

  3  select owner,object_name,subobject_name,object_id,

  4     data_object_id,object_type,created,last_ddl_time,

  5     timestamp,status,temporary,generated,secondary,

  6     namespace,edition_name,sharing,editionable,

  7     oracle_maintained, case when id between

  8                     0 and 250000 then 0

  9                     else id end id

 10  from big_table;

 

Table created.

 

 

The column ID has 25% of duplicate values and 75% of unique values, we then create an index on this ID column and check the size.

 

 

demo@ORA12C> create index t_idx on t(id) nologging;

 

Index created.

 

demo@ORA12C> col index_name format a10

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      DISABLED             2124

 

 

Now we use the normal prefix compression on this index

 

 

demo@ORA12C> alter index t_idx rebuild compress nologging;

 

Index altered.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      ENABLED              2685

 

 

We noticed that the index size got increased with compression, due to the overheads associated with resultant prefix table.

 

If we use advanced index compression LOW introduced in 12.1 database

 

demo@ORA12C> alter index t_idx rebuild compress advanced LOW nologging;

 

Index altered.

 

demo@ORA12C> 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         2054

 

 

We noticed that the index size got decreased, by effectively compressing 25% of data where compression is beneficial and not touched the 75% of index where compression wasn’t possible.

 

If we however use the new 12.2 advanced index compression HIGH option:

 

demo@ORA12C> alter index t_idx rebuild compress advanced HIGH nologging;

 

Index altered.

 

demo@ORA12C> 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

 

 

The leaf block is zero due the bug 22094934. We need to gather the index statistics to see the new index size.

 

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

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> 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         777

 

 

The index is now substantially smaller than before. Without compression it was 2124 blocks and with advanced compression low it is 2054 block and with advanced compression high it is only 777 blocks.

 

Starting with 12c release 2, the default option for advanced index compression is HIGH.

 

demo@ORA12C> create table t as select * from all_objects;

 

Table created.

 

demo@ORA12C> create index t_idx on t( object_name )

  2  nologging

  3  compress

  4  advanced ;

 

Index created.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      ADVANCED HIGH           0

 

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

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      ADVANCED HIGH         188

 

So Index Advanced Compression, with the now default “HIGH” option can substantially reduce index sizes. Note this new capability of course requires the Advanced Compression Option.

 

SQL Plan baselines re-enable

 

Consider the following execution plan it is associated with a sql plan baselines.

 

demo@ORA12C> explain plan for

  2  select count( distinct status )

  3  from t

  4  where object_id = 42;

 

Explained.

 

demo@ORA12C> @xplan

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2359337548

 

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

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

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

|   0 | SELECT STATEMENT     |          |     1 |     5 |   313  (15)| 00:00:01 |

|   1 |  SORT AGGREGATE      |          |     1 |     5 |            |          |

|   2 |   VIEW               | VW_DAG_0 |     1 |     5 |   313  (15)| 00:00:01 |

|   3 |    HASH GROUP BY     |          |     1 |    12 |   313  (15)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL| T        |     1 |    12 |   312  (15)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter("OBJECT_ID"=42)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_01nrwm0nv64tsb2385278" used for this statement

 

 

The notes section shows that we are using the sql plan baselines, let’s disable it.

 

demo@ORA12C> begin

  2     :n := dbms_spm.alter_sql_plan_baseline(

  3                     sql_handle =>'SQL_00d2fc9829b31338',

  4                     plan_name =>'SQL_PLAN_01nrwm0nv64tsb2385278',

  5                     attribute_name =>'ENABLED',

  6                     attribute_value =>'NO');

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> print n

 

         N

----------

         1

 

demo@ORA12C> select sql_handle,plan_name,enabled,accepted

  2  from dba_sql_plan_baselines ;

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC

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

SQL_00d2fc9829b31338           SQL_PLAN_01nrwm0nv64tsb2385278 NO  YES

 

 

The sql execution plan for this statement is no longer constrained, the data in this table will change and at some point, we get new plans. For the purpose of this example let’s say we got a unique index on object_id column, then plan changes like this:

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor );

 

PLAN_TABLE_OUTPUT

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

SQL_ID  3v77781b798nk, child number 0

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

select count( distinct status ) from t where object_id = 42

 

Plan hash value: 1391718225

 

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

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

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

|   0 | SELECT STATEMENT               |          |       |       |     2 (100)|

|   1 |  SORT AGGREGATE                |          |     1 |     5 |            |

|   2 |   VIEW                         | VW_DAG_0 |     1 |     5 |     2   (0)|

|   3 |    SORT GROUP BY NOSORT        |          |     1 |    12 |     2   (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |    12 |     2   (0)|

|*  5 |      INDEX UNIQUE SCAN         | T_IDX    |     1 |       |     1   (0)|

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

 

Predicate Information (identified by operation id):

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

 

   5 - access("OBJECT_ID"=42)

 

 

Our sql plan baselines is disabled, so the optimizer is not constrained. It is free to use its new plan. There is no “Notes” section either, so we can be sure that we are not using the sql plan baselines. At this point everything looks right: the sql plan baselines is disabled so the execution plan is not constrained in any way.

 

If we have taken a look at the sql plan baseline the moment after the new “index plan” was chosen by the optimizer, this is what you would have seen.

 

 

demo@ORA12C> select sql_handle,plan_name,enabled,accepted

  2  from dba_sql_plan_baselines ;

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC

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

SQL_00d2fc9829b31338           SQL_PLAN_01nrwm0nv64ts6ccbdc46 YES NO

SQL_00d2fc9829b31338           SQL_PLAN_01nrwm0nv64tsb2385278 NO  YES

 

 

What happened? We got two sql plan baselines for the same statement.

 

Bear in mind that when you disable a sql plan baseline we disable an individual sql plan baseline and not the ability of a particular sql statement to acquire new plan baselines. When the optimizer sees a new sql execution plan for a sql statement, it will create a new sql plan baseline if a sql plan baseline already exists for the statement. It does even if the auto capture is disabled (optimizer_capture_sql_plan_baselines=false) and even if the existing sql plan baseline associated with the statement are disabled.

 

The new execution plan is enabled, but it has not yet verified and accepted by the sql plan management evolution, so we see ACCEPTED = NO. At this point our sql statement will not use either of our sql plan baselines and there is no “notes” section show in the plan.

 

demo@ORA12C> explain plan for

  2  select count( distinct status )

  3  from t

  4  where object_id = 42;

 

Explained.

 

demo@ORA12C> @xplan

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1391718225

 

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

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

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

|   0 | SELECT STATEMENT               |          |     1 |     5 |     2   (0)|

|   1 |  SORT AGGREGATE                |          |     1 |     5 |            |

|   2 |   VIEW                         | VW_DAG_0 |     1 |     5 |     2   (0)|

|   3 |    SORT GROUP BY NOSORT        |          |     1 |    12 |     2   (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |    12 |     2   (0)|

|*  5 |      INDEX UNIQUE SCAN         | T_IDX    |     1 |       |     1   (0)|

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

 

Predicate Information (identified by operation id):

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

 

   5 - access("OBJECT_ID"=42)

 

17 rows selected.

 

 

If the auto evolution task is enabled in your database (which is the default in Oracle 12c) or if you run evolution manually, then the sql plan baseline is subjected to verification and evolution because it is not disabled. When a sql plan baseline is verified, evolution might accept it. This is what we will see after the evolution.

 

 

demo@ORA12C> select sql_handle,plan_name,enabled,accepted

  2  from dba_sql_plan_baselines ;

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC

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

SQL_00d2fc9829b31338           SQL_PLAN_01nrwm0nv64ts6ccbdc46 YES YES

SQL_00d2fc9829b31338           SQL_PLAN_01nrwm0nv64tsb2385278 NO  YES

 

 

We now have a sql plan baseline that is enabled and accepted. The first generated sql plan baseline remains disabled, but the optimizer will be able to use the new sql plan baselines.

 

 

demo@ORA12C> explain plan for

  2  select count( distinct status )

  3  from t

  4  where object_id = 42;

 

Explained.

 

demo@ORA12C> @xplan

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1391718225

 

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

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

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

|   0 | SELECT STATEMENT               |          |     1 |     5 |     2   (0)|

|   1 |  SORT AGGREGATE                |          |     1 |     5 |            |

|   2 |   VIEW                         | VW_DAG_0 |     1 |     5 |     2   (0)|

|   3 |    SORT GROUP BY NOSORT        |          |     1 |    12 |     2   (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |    12 |     2   (0)|

|*  5 |      INDEX UNIQUE SCAN         | T_IDX    |     1 |       |     1   (0)|

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

 

Predicate Information (identified by operation id):

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

 

   5 - access("OBJECT_ID"=42)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_01nrwm0nv64ts6ccbdc46" used for this statement

 

21 rows selected.

 

 

In summary

 

·         When you disable a sql plan baseline, you disable an individual sql plan baseline but not the mechanism of plan capture and evolution at statement level.

 

·         If the goal is to disable the sql plan baseline for a statement, then drop that plan baseline using DBMS_SPM.DROP_SQL_PLAN_BASELINE.

 

·         To keep the “backup copy” then use DBMS_SPM.PACK_STGTAB_BASELINE to pack the relevant baseline into a staging table before dropping it, however we can always put it back using DBMS_SPM.UNPACK_STGTAB_BASELINE.

 

 

Loading sql plans into SPM from AWR

 

Starting from Oracle 12cR2 it is now possible to load a SPM plan baseline from AWR historical execution plans. Prior to that only the plans from cursor cache can be loaded into baselines.

 

Let’s say I need to configure SPM for the sqlid 94dwfa8yd87kw with plan hash value as 3321871023 with its plan available from AWR repository.

 

demo@ORA12C> select count(*) from dba_sql_plan_baselines;

 

  COUNT(*)

----------

         0

 

demo@ORA12C> select sql_text from dba_hist_sqltext where sql_id ='94dwfa8yd87kw';

 

SQL_TEXT

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

select count(*) from t2

 

demo@ORA12C> select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id ='94dwfa8yd87kw';

 

   SNAP_ID SQL_ID        PLAN_HASH_VALUE

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

      4718 94dwfa8yd87kw      3321871023

 

 

With the above details all I need to invoke LOAD_PLANS_FROM_AWR function (new in 12.2 database) inside the DBMS_SPM package.

 

 

demo@ORA12C> variable x number

demo@ORA12C> begin

  2     :x := dbms_spm.load_plans_from_awr( begin_snap=>4717,end_snap=>4718,

  3                             basic_filter=>q'# sql_id='94dwfa8yd87kw' and plan_hash_value='3321871023' #' );

  4  end;

  5  /

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> print x

 

         X

----------

         1

 

Post that we can verify the details and plans from SMB.

 

demo@ORA12C> select count(*) from dba_sql_plan_baselines;

 

  COUNT(*)

----------

         1

 

demo@ORA12C> select t2.*

  2  from dba_sql_plan_baselines t1,

  3    table( dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name) ) t2 ;

 

PLAN_TABLE_OUTPUT

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

 

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

SQL handle: SQL_2064bcd93885dd48

SQL text: select count(*) from t2

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

 

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

Plan name: SQL_PLAN_20t5wv4w8bra81c6cf506         Plan id: 476902662

Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-AWR

Plan rows: From dictionary

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

 

Plan hash value: 3321871023

 

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

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

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

|   0 | SELECT STATEMENT   |      |       |   154 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |     0   (0)|          |

|   2 |   TABLE ACCESS FULL| T2   | 43196 |   154  (12)| 00:00:01 |

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

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

 

25 rows selected.

 

 

Finally we could quickly do an Explain plan over the sql to confirm if the plan baselines were used – as highlighted in the notes section below.

 

 

demo@ORA12C> set autotrace traceonly explain

demo@ORA12C> select count(*) from t2;

 

Execution Plan

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

Plan hash value: 3321871023

 

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |   152  (11)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T2   | 36339 |   152  (11)| 00:00:01 |

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

 

Note

-----

   - SQL plan baseline "SQL_PLAN_20t5wv4w8bra81c6cf506" used for this statement