Remove TDE online

 

One question came from a co-worker, was how to disable/remove TDE (at tablespace level) in Oracle database.

 

This blog post will make use the script provided in the earlier post for demonstration.

 

Though the documentation is not clear on disabling the TDE, thought it might worth a blog post to discuss that in detail here.

 

Starting with 12.2 and above, not only TDE can be done online, also the decryption can be done online.

 

demo@ORA12C> select tablespace_name,encrypted

  2  from dba_tablespaces

  3  where encrypted='YES';

 

TABLESPACE_NAME                ENC

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

TS_CLEAR                       YES

 

demo@ORA12C> select owner,segment_name,segment_type

  2  from dba_segments

  3  where tablespace_name ='TS_CLEAR';

 

OWNER      SEGMENT_NAME                   SEGMENT_TYPE

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

DEMO       T                              TABLE

DEMO       T_IDX                          INDEX

 

So got a tablespace TS_CLEAR with encryption enabled.

 

To remove the encryption, all we have to do is decrypt it.

 

demo@ORA12C> alter tablespace ts_clear

  2  encryption online

  3  decrypt

  4  file_name_convert = ( 'TS_CLEAR_ENC_01.dbf','TS_CLEAR_01.dbf'

  5     ,'TS_CLEAR_ENC_02.dbf','TS_CLEAR_02.dbf' );

 

Tablespace altered.

 

demo@ORA12C> select file_name

  2  from dba_data_files

  3  where tablespace_name ='TS_CLEAR' ;

 

FILE_NAME

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

D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_01.DBF

D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_02.DBF

 

 

Post that no encryption is available at the tablespace level.

 

 

demo@ORA12C> select tablespace_name,encrypted

  2  from dba_tablespaces

  3  where encrypted ='YES';

 

no rows selected

 

 

Once decrypted, we can close the Key store safely and access the tables without any errors.

 

 

demo@ORA12C> conn syskm/Password-1@ora12c as SYSKM

Connected.

syskm@ORA12C> administer key management set keystore close identified by foobar;

 

keystore altered.

 

syskm@ORA12C> conn demo/demo@ora12c

Connected.

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

 

  COUNT(*)

----------

        47

 

demo@ORA12C>

 

 

So accessing the tables with wallet being closed, confirms that encryption is no more in place.

 

SQL Plan Directives Part II

 

This will be extension of the previous post, incase if you have not gone through that, please read that before to proceed here.

 

The below demo was from 12cR2 (12.2.0.1) database.

 

Starting with 12.2 the parameter OPTIMIZER_ADAPTIVE_FEATURES got depreciated and replaced with two new parameter OPTIMIZER_ADAPTIVE_PLANS to control adaptive plans (which is True by default) and OPTIMIZER_ADAPTIVE_STATISTICS to control adaptive statistics (which is False by default)

 

 

demo@ORA12C> show parameter optimizer_adaptive_statistics

 

NAME                                 TYPE        VALUE

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

optimizer_adaptive_statistics        boolean     FALSE

 

 

Creating a table with appropriate stats as in 12.1

 

 

demo@ORA12C> create table t

  2  as

  3  select a.*,

  4     mod(rownum,2) as flag1,

  5     mod(rownum,2) as flag2,

  6     mod(rownum,2) as flag3,

  7     mod(rownum,2) as flag4

  8  from all_objects a;

 

Table created.

 

demo@ORA12C> set serveroutput off

demo@ORA12C> select /*+ gather_plan_statistics */ count(distinct owner)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

COUNT(DISTINCTOWNER)

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

                  30

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  7qsfw42st98nq, child number 0

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

select /*+ gather_plan_statistics */ count(distinct owner) from t where

flag1 = 1 and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2359337548

 

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

| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |

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

|   0 | SELECT STATEMENT     |          |      1 |        |      1 |

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

|   2 |   VIEW               | VW_DAG_0 |      1 |     30 |     30 |

|   3 |    HASH GROUP BY     |          |      1 |     30 |     30 |

|*  4 |     TABLE ACCESS FULL| T        |      1 |   4528 |  36224 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

 

22 rows selected.

 

 

As in 12.1 we have misestimate the cardinality, the cursor is marked as re-optimizable.

 

 

demo@ORA12C> select sql_id,child_number,is_reoptimizable

  2  from v$sql

  3  where sql_id ='7qsfw42st98nq';

 

SQL_ID        CHILD_NUMBER I

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

7qsfw42st98nq            0 Y

 

 

As in 12.1, cardinality feedback kicks in and fixes the misestimated cardinality, on the very next execution.

 

 

demo@ORA12C> select /*+ gather_plan_statistics */ count(distinct owner)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

COUNT(DISTINCTOWNER)

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

                  30

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  7qsfw42st98nq, child number 1

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

select /*+ gather_plan_statistics */ count(distinct owner) from t where

flag1 = 1 and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2359337548

 

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

| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |

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

|   0 | SELECT STATEMENT     |          |      1 |        |      1 |

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

|   2 |   VIEW               | VW_DAG_0 |      1 |     30 |     30 |

|   3 |    HASH GROUP BY     |          |      1 |     30 |     30 |

|*  4 |     TABLE ACCESS FULL| T        |      1 |  36224 |  36224 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

Note

-----

   - statistics feedback used for this statement

 

 

26 rows selected.

 

demo@ORA12C> select sql_id,child_number,is_reoptimizable

  2  from v$sql

  3  where sql_id ='7qsfw42st98nq';

 

SQL_ID        CHILD_NUMBER I

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

7qsfw42st98nq            0 Y

7qsfw42st98nq            1 N

 

demo@ORA12C>

 

 

We can see that a new SPD got created:

 

 

demo@ORA12C> exec dbms_spd.flush_sql_plan_directive;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select directive_id,type,enabled,state,notes,reason

  2  from dba_sql_plan_directives

  3  where directive_id in ( select directive_id from dba_sql_plan_dir_objects where owner = user

  4  and object_name ='T' );

 

DIRECTIVE_ID TYPE                    ENA STATE      NOTES                                    REASON

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

  1.1853E+19 DYNAMIC_SAMPLING        YES USABLE     <spd_note>                               SINGLE TABLE CARDINALITY MISESTIMATE

                                                      <internal_state>NEW</internal_state>

                                                      <redundant>NO</redundant>

                                                      <spd_text>{EC(DEMO.T)[FLAG1, FLAG2, FL

                                                    AG3, FLAG4]}</spd_text>

                                                    </spd_note>

 

 

However in 12.2 this newly created SPD is not get used for subsequent execution of queries with similar predicates and still lead to cardinality deviation.

 

 

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

  COUNT(*)

----------

     36224

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  0n020wm1vka2r, child number 0

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

select /*+ gather_plan_statistics */ count(*) from t where flag1 = 1

and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    1580 |

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    1580 |

|*  2 |   TABLE ACCESS FULL| T    |      1 |   4528 |  36224 |00:00:00.03 |    1580 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

 

20 rows selected.

 

 

However setting this parameter OPTIMIZER_ADAPTIVE_STATISTICS to True (at session level) and re-running the above test case, allowed SPD to kick in.

 

 

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

  COUNT(*)

----------

     36224

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  0n020wm1vka2r, child number 0

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

select /*+ gather_plan_statistics */ count(*) from t where flag1 = 1

and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1580 |

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.02 |    1580 |

|*  2 |   TABLE ACCESS FULL| T    |      1 |  33267 |  36224 |00:00:00.02 |    1580 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

Note

-----

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

   - 1 Sql Plan Directive used for this statement

 

 

25 rows selected.

 

demo@ORA12C>

 

 

This created a new directive type called DYNAMIC_SAMPLING_RESULT – a new feature of 12.2 - That is the results of this dynamically sampled data is stored in the repository, so that the results can be reused by other queries in future.

 

 

demo@ORA12C> exec dbms_spd.flush_sql_plan_directive;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select directive_id,type,enabled,state,notes,reason

  2  from dba_sql_plan_directives

  3  where directive_id in ( select directive_id from dba_sql_plan_dir_objects where owner = user

  4  and object_name ='T' );

 

DIRECTIVE_ID TYPE                    ENA STATE      NOTES                                    REASON

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

  1.5724E+19 DYNAMIC_SAMPLING_RESULT YES USABLE     <spd_note>                               VERIFY CARDINALITY ESTIMATE

                                                      <internal_state>NEW</internal_state>

                                                      <redundant>NO</redundant>

                                                      <spd_text>{(DEMO.T, num_rows=72447) -

                                                    (SQL_ID:cvgnggk0cmpaf, T.CARD=33267[-2 -

                                                    2])}</spd_text>

                                                    </spd_note>

 

  8.2364E+18 DYNAMIC_SAMPLING        YES USABLE     <spd_note>                               SINGLE TABLE CARDINALITY MISESTIMATE

                                                      <internal_state>MISSING_STATS</interna

                                                    l_state>

                                                      <redundant>NO</redundant>

                                                      <spd_text>{EC(DEMO.T)[FLAG1, FLAG2, FL

                                                    AG3, FLAG4]}</spd_text>

                                                    </spd_note>

 

 

Now the internal state of this directive is in MISSING_STATS, Oracle will create the extended stats when gathering stats next time.

 

 

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',options=>'gather auto',no_invalidate=>false);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select column_name,num_buckets,histogram

  2  from user_tab_col_statistics

  3  where table_name ='T'

  4  and column_name like 'SYS%';

 

no rows selected

 

demo@ORA12C> select * from user_stat_extensions where table_name ='T';

 

no rows selected

 

 

Regathering the stats doesn’t created any extended stats in 12.2 database as it did in 12.1 database.

 

However starting with 12.2 creating extended stats is controlled by the newly added global preference AUTO_STAT_EXTENSIONS – which is OFF by default.

 

Setting that preference to ON and regathering the stats, resulted in creation of extended stats (similar to 12.1 database behavior)

 

 

demo@ORA12C> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;

 

DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')

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

OFF

 

demo@ORA12C> exec dbms_stats.set_global_prefs('AUTO_STAT_EXTENSIONS','ON') ;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;

 

DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')

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

ON

 

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',options=>'gather auto',no_invalidate=>false);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select column_name,num_buckets,histogram

  2  from user_tab_col_statistics

  3  where table_name ='T'

  4  and column_name like 'SYS%';

 

COLUMN_NAME          NUM_BUCKETS HISTOGRAM

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

SYS_STS##3#UAEFD##2J           2 FREQUENCY

M$8KNJ1P32

 

 

demo@ORA12C> select * from user_stat_extensions where table_name ='T';

 

TABLE_NAME           EXTENSION_NAME                 EXTENSION                                     CREATO DRO

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

T                    SYS_STS##3#UAEFD##2JM$8KNJ1P32 ("FLAG1","FLAG2","FLAG3","FLAG4")             SYSTEM YES

 

demo@ORA12C>

 

 

Once the query is executed again, the internal state changes to HAS_STATS (same as 12.1)

 

 

demo@ORA12C> select /*+ gather_plan_statistics */ sum(object_id)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

SUM(OBJECT_ID)

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

    1462835630

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  f8z55za1k8sfw, child number 0

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

select /*+ gather_plan_statistics */ sum(object_id) from t where flag1

= 1 and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1580 |

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.02 |    1580 |

|*  2 |   TABLE ACCESS FULL| T    |      1 |  33267 |  36224 |00:00:00.01 |    1580 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

Note

-----

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

   - 1 Sql Plan Directive used for this statement

 

 

25 rows selected.

 

demo@ORA12C> exec dbms_spd.flush_sql_plan_directive;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select directive_id,type,enabled,state,notes,reason

  2  from dba_sql_plan_directives

  3  where directive_id in ( select directive_id from dba_sql_plan_dir_objects where owner = user

  4  and object_name ='T' );

 

DIRECTIVE_ID TYPE                    ENA STATE      NOTES                                    REASON

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

  1.5724E+19 DYNAMIC_SAMPLING_RESULT YES USABLE     <spd_note>                               VERIFY CARDINALITY EST

                                                      <internal_state>NEW</internal_state>

                                                      <redundant>NO</redundant>

                                                      <spd_text>{(DEMO.T, num_rows=72447) -

                                                    (SQL_ID:cvgnggk0cmpaf, T.CARD=33267[-2 -

                                                    2])}</spd_text>

                                                    </spd_note>

 

  8.2364E+18 DYNAMIC_SAMPLING        YES SUPERSEDED <spd_note>                               SINGLE TABLE CARDINALI

                                                      <internal_state>HAS_STATS</internal_st

                                                    ate>

                                                      <redundant>NO</redundant>

                                                      <spd_text>{EC(DEMO.T)[FLAG1, FLAG2, FL

                                                    AG3, FLAG4]}</spd_text>

                                                    </spd_note>

 

 

 

The next execution of the query uses the stats instead of SPD.

 

 

 

demo@ORA12C> select /*+ gather_plan_statistics */ max(object_id)

  2  from t

  3  where flag1 = 1

  4  and flag2 = 1

  5  and flag3 = 1

  6  and flag4 = 1 ;

 

MAX(OBJECT_ID)

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

        116844

 

demo@ORA12C>

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  38x0rq12q1squ, child number 0

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

select /*+ gather_plan_statistics */ max(object_id) from t where flag1

= 1 and flag2 = 1 and flag3 = 1 and flag4 = 1

 

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1580 |

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1580 |

|*  2 |   TABLE ACCESS FULL| T    |      1 |  36224 |  36224 |00:00:00.01 |    1580 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))

 

 

20 rows selected.

 

demo@ORA12C>

 

 

So in summary there were some changes in 12.2 compared with 12.1 database.

 

·         Having OPTIMIZER_ADAPTIVE_STATISTICS = False (by default) – doesn’t disable the creation of SPD, instead it prevents them from being used in conjunction with Dynamic statistics to influence new execution plans – Hence SPD got created but doesn’t involve in further optimization – subsequent execution of sql with similar combination of predicates produced cardinality mismatch – “statistics feedback” kick-in again.

 

·         Starting with 12.2 automatic column group creation is controlled by DBMS_STATS preference AUTO_STAT_EXTENSIONS – which is OFF by default, setting it to ON and re-running my demo, created the extended stats automatically.

 

·         Starting with 12.2 new SPD of type DYNAMIC_SAMPLING_RESULT will be created, that is the results of this dynamically sampled data is stored in the repository, so that the results can be reused by other queries in future.