Working with SQL Plan Directives

New in 12c are SQL Plan Directives, these help in situations where the optimizer is unable to correctly get the cardinality calculation for what is referred to as a SQL Expression.  More or less this is linked to a predicate.  (Not exactly more like a column or set of columns used within a statement.)

Oracle provides a nice package and set of views to administer these. Here are a couple little scripts you might want to use to help with work with them.  If you like the scripts and use them, all I ask is that you don’t remove the header comments to give us credit for these scripts.  Thanks! 

Note: these scripts are for 12.1.0.2.  A few columns have changed in the views between 12.1.0.1 and 12.1.0.2; this affects the listing script the most. 

This first one will LIST all the SQL Plan Directives for a given table.

-- File name hspdl.sql

-- SQL Plan Directives LIST

-- for a given table

--

-- May 2015 RVD initial coding

--

set termout on

set tab off

set verify off

set serveroutput on

accept hdspl_owner prompt 'Enter the owner name: '

accept hdspl_table prompt 'Enter the table name: '

DECLARE

     v_owner   dba_sql_plan_dir_objects.owner%type := '&hdspl_owner' ;

     v_tab     dba_sql_plan_dir_objects.object_name%type := '&hdspl_table' ;

     v_cnt     number := 0;

BEGIN

  -- Make sure all directives are flushed to the Data Dictionary 

  DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

  FOR v_spd IN

      (select spd_o.directive_id dir_id, spd_o.subobject_name col_name,

              spd_o.object_type,

              spd_d.enabled enabled, spd_d.state state,

              spd_d.reason, spd_d.type

       from dba_sql_plan_dir_objects spd_o, dba_sql_plan_directives spd_d

       where spd_d.directive_id=spd_o.directive_id

       and spd_o.owner = upper(v_owner) and spd_o.object_name = upper(v_tab))

  LOOP

    dbms_output.put_line('***********************');

    dbms_output.put_line('SQL Plan Directive ID '||to_char(v_spd.dir_id));

    dbms_output.put_line('..........Object Type '||to_char(v_spd.object_type));

    dbms_output.put_line('..........Column name '||to_char(v_spd.col_name));

    dbms_output.put_line('.............Enabled? '||to_char(v_spd.enabled));

    dbms_output.put_line('................State '||to_char(v_spd.state));

    dbms_output.put_line('.................Type '||to_char(v_spd.type));

    dbms_output.put_line('...............Reason '||to_char(v_spd.reason));

  END LOOP;

  select count(distinct directive_id) into v_cnt

  from dba_sql_plan_dir_objects

  where owner = upper(v_owner) and object_name = upper(v_tab);

  dbms_output.put_line('*******************************');

  dbms_output.put_line('Number of SQL Plan Directives :'||v_cnt);

END;

/

set serveroutput off

clear columns

This next one will DROP all the directives on a table.    I would expect you would use this mostly in testing where you’d like to run SQL with and without the directives on a table. 

-- File name hspdd.sql

-- SQL Plan Directives DROP

-- for a given table

--

-- Copyright (c); 2015 by Hotsos Enterprises, Ltd.

--

-- May 2015 RVD initial coding

--

set termout on

set tab off

set verify off

set serveroutput on

accept hspd_owner prompt 'Enter the owner name: '

accept hspd_table prompt 'Enter the table name: '

DECLARE

     v_owner   dba_sql_plan_dir_objects.owner%type := '&hspd_owner' ;

     v_tab     dba_sql_plan_dir_objects.object_name%type := '&hspd_table' ;

     v_cnt     number := 0;

BEGIN

  FOR v_dir_id IN

      (select distinct directive_id from dba_sql_plan_dir_objects

       where owner = upper(v_owner) and object_name = upper(v_tab))

  LOOP

    -- dbms_output.put_line('SQL Plan Directive ID '||to_char(v_dir_id.directive_id));

    DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE(v_dir_id.directive_id);

    v_cnt := v_cnt + 1;

  END LOOP;

  dbms_output.put_line('Number of SQL Plan Directives droped:'||v_cnt);

END;

/

set serveroutput off

clear columns

Here is a sample run of listing all the directives on a table:

SQL> @hspdl

Enter the owner name: op

Enter the table name: big_tab

***********************

SQL Plan Directive ID 12211892554193285223

..........Object Type COLUMN

..........Column name OBJECT_TYPE

.............Enabled? YES

................State SUPERSEDED

.................Type DYNAMIC_SAMPLING

...............Reason SINGLE TABLE CARDINALITY MISESTIMATE

***********************

SQL Plan Directive ID 12211892554193285223

..........Object Type TABLE

..........Column name

.............Enabled? YES

................State SUPERSEDED

.................Type DYNAMIC_SAMPLING

...............Reason SINGLE TABLE CARDINALITY MISESTIMATE

***********************

SQL Plan Directive ID 3647637080393143009

..........Object Type COLUMN

..........Column name OWNER

.............Enabled? YES

................State USABLE

.................Type DYNAMIC_SAMPLING

...............Reason SINGLE TABLE CARDINALITY MISESTIMATE

***********************

SQL Plan Directive ID 3647637080393143009

..........Object Type COLUMN

..........Column name OBJECT_TYPE

.............Enabled? YES

................State USABLE

.................Type DYNAMIC_SAMPLING

...............Reason SINGLE TABLE CARDINALITY MISESTIMATE

***********************

SQL Plan Directive ID 3647637080393143009

..........Object Type TABLE

..........Column name

.............Enabled? YES

................State USABLE

.................Type DYNAMIC_SAMPLING

...............Reason SINGLE TABLE CARDINALITY MISESTIMATE

*******************************

Number of SQL Plan Directives :2