I'm an ACE!

BIG thanks to Tim Gorman and everyone who submitted an endorsement for me.  I very much appreciate being recognized for my years of working with Oracle and my active support of the Oracle community.

Let's keep on rockin! 

Getting the SQL_ID/HASH_VALUE with DBMS_SQL_TRANSLATOR

This little script will read in a SQL file and return to you the SQL_ID and HASH_VALUE of a statement without running it.  It uses DBMS_SQL_TRANSLATOR.SQL_ID and .SQL_HASH to get these values.  The tricky part was to make sure the file is in the same format as what the optimization process uses when computing these values.  Key is that the file is in UNIX format, meaning there is only a linefeed at the end of each line.  Also the optimizer strips out trailing spaces or tabs (white space) before calculating these values as well.  

It was this second one that took me awhile to figure out.  I didn’t know at first that the optimizer was doing this.  Once I knew what was going on it was rather simple to fix.  (Isn’t that always the case?  It’s finding the real problem that is the problem!)  My solution is rather brute force, but I didn’t want to truly “parse” the statement, as in go thru it character by character.   So I instead just look for a space or tab with a line feed after it, if there is at least one of either I replace “both” thru out the entire CLOB.  Like I said not eloquent but it gets the job done.  Part of the issue was I also had to leave the spacing alone inside a line.  The optimizer only strips off the white space at the end of the line, not within the line. 

The PL/SQL engine is different; it reduces ALL white space to a single space and removes all line feeds.  Hence it turns your SQL statement into one long string.   But it does all this before it sends the SQL to the optimizer.  I’m not trying to mimic the PL/SQL engine with this script.

So here it is.  If your find any flaws with this please let me know.  I’ve tested it on several statements and on UNIX and Windows, so far it has been correct with each test.  If you like this and use this please keep the header information to give me credit for it is all I ask.  Thanks!

For those who were at the Great Lakes Oracle Conference this week, #GLOC15, here is the working version I told you I was still working on in my session about execution plans.

-- File name hgetsqlid.sql

-- Get the SQLID/HASH for a SQL statement in a file

-- The file must contain only ONE SQL statement

-- The Directory "SQL_FILE_LOCATION" must be defined

-- This is the location where this will read the file from

-- Example

-- CREATE OR REPLACE DIRECTORY SQL_FILE_LOCATION AS '/home/oracle/OP';

-- The file MUST end with a "/" on last line

-- Exmple

-- Select * from emp

-- /

--

-- May 2015 RVD initial coding

--

set termout on

set tab off

set verify off

set serveroutput on

column directory_path format a100 wrap

prompt Current setting of SQL_FILE_LOCATION:

select directory_path from dba_directories where directory_name = 'SQL_FILE_LOCATION';

prompt ******************************************

accept hgetsqlid_file prompt 'Enter the full file name (with extension): '

DECLARE

    v_bfile BFILE;

    v_clob  CLOB;

    v_sqlid VARCHAR2(13);

    v_sqlhash number;

    v_slash integer;

    e_noslash exception;

BEGIN

    v_bfile := BFILENAME ('SQL_FILE_LOCATION', '&hgetsqlid_file');

    IF DBMS_LOB.FILEEXISTS (v_bfile) = 1 THEN

        DBMS_LOB.OPEN (v_bfile);

        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);

        DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));

        DBMS_LOB.CLOSE (v_bfile);

        -- remove all carrage returns (ASCII 13) from the clob

        -- each line must end with only a line feed (ASCII 10)

        v_clob := replace(v_clob, CHR(13) , '');

        -- remove any trailing spaces or tabs (ASCII 9)

        while DBMS_LOB.INSTR (v_clob, ' '||CHR(10)) > 0 or

              DBMS_LOB.INSTR (v_clob, CHR(9)||CHR(10)) > 0

             loop

             v_clob := replace(v_clob, ' '||CHR(10), CHR(10));

             v_clob := replace(v_clob, CHR(9)||CHR(10), CHR(10));

             end loop;

        -- Find the / at the end of the file

        v_slash := DBMS_LOB.INSTR (v_clob,'/');

        IF v_slash = 0 THEN RAISE e_noslash; END IF;

        -- remove the line with the slash and everything after it

        DBMS_LOB.TRIM (v_clob,v_slash-2);

        v_sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (v_clob);

        v_sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (v_clob);

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

        dbms_output.put_line ('The SQL ID is '||v_sqlid);

        dbms_output.put_line ('Hash value is '||v_sqlhash);

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

    ELSE

        dbms_output.put_line ('** File not found **');

    END IF;

    EXCEPTION

        when e_noslash then

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

        dbms_output.put_line ('Slash not found!');

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

END;

/

Here is an example:

SQL> get sqlid_test.sql

  1  SELECT e.employee_id, e.last_name, j.job_title, d.department_name

  2  FROM employees e, jobs j,   departments d

  3  WHERE e.department_id IN (10,20)

  4  AND j.job_id = e.job_id

  5  AND e.department_id = d.department_id

  6  ORDER BY e.last_name

  7* /

SQL>

SQL> @hgetsqlid

Current setting of SQL_FILE_LOCATION:

DIRECTORY_PATH

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

C:\OP

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

Enter the full file name (with extension): sqlid_test.sql

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

The SQL ID is ap3yukgck02pd

Hash value is 3642755757

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

SQL> select sql_id, hash_value from v$sql where sql_text like 'SELECT e.employee_id, e.last_name,%';

SQL_ID             HASH_VALUE

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

ap3yukgck02pd      3642755757

SQL>

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