Friday, May 22, 2015

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>

Thursday, May 21, 2015

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

Tuesday, March 24, 2015

OTech Magazine issue Spring 2015

http://otechmag.com/magazine/2015/spring/index.htmlHey!  I'm in Print!  Well in the internet age kind of "print".  Check out the latest issue of the OTech Magazine. 


Click Here or on the image -->

My article is "Know your Histograms".  Lots of other great articles in here too.

Enjoy!

Friday, March 20, 2015

Which indexes are NOT being used? UPDATED!


UPDATE!  Ok I really shouldn't write posts when I'm sick... The query was a not correct.  Thanks to Mikhail Velikikh  for pointing this it out.

Correct query is:



SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME
 FROM DBA_INDEXES
 WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'
 and (owner, index_name) not in (
 SELECT object_owner, OBJECT_NAME
 FROM DBA_HIST_SQL_PLAN
 WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX')
/
 

Have a great day!  

There is a lot of chatter about indexes and folks are realizing that more indexes isn’t better and too many can be bad.  The impact on DML is real.  The more indexes the more maintenance there is for all INSERTS and DELETES and maybe the UPDATES too (just depends on what you update of course).   Also more indexes can even cause the optimizer to slow down as it considers indexes that it ends up not using for selects.

I had a post a while back to show which indexes are being used (http://ricramblings.blogspot.com/2012/11/is-this-index-being-used.html).  Which certainly is nice to know but what about a system with lots of indexes out there, which ones aren’t being used?  It’s likely faster to zero in on those unused ones and figure out if you should keep it or not.  

The index monitoring is an option and it might give you an idea of which ones aren’t, however it’s not a great tool really and unfortunately the results aren’t reliable. (If you’d like, check out Richard Foote’s blog (https://richardfoote.wordpress.com/) and search for “Index Monitoring”, he has several posts talking about its limitations.)  Really what you want to know is “Which indexes are not being used in my SQL?”

For this the best approach is to use DBA_HIST_SQL_PLAN.  However you must be warned that this view is part of the Oracle Diagnostic Pack.  If you’re not licensed for this you cannot use this view.  Most large shops are, so this shouldn’t be an issue.  Assuming you have the Diagnostic Pack, here is a simple query that will tell you which indexes haven’t been used in SQL for a given schema for as long as you have history in the view:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES
WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'
MINUS
SELECT OBJECT_NAME, NULL, NULL FROM DBA_HIST_SQL_PLAN
WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'

That’s it.  I’ve excluded LOB indexes since they are mostly used internally and dropping those would be a very bad thing, but otherwise this will show you all the indexes that haven’t been used in a plan that is in the plan history. 

If you don’t have the Diagnostic Pack, you could change this to:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES
WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'
MINUS
SELECT OBJECT_NAME, NULL, NULL FROM V$SQL_PLAN
WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'


The only issue here is that this only shows plans that are currently in the shared pool, so you might have used an index in some query that has aged out for example.  But even this should give you a decent list of indexes worth investigation.  

Once you find some indexes that appear to not be used, now what?  Well if you’re a cowboy you can just drop ‘em and see what happens!  Of course that might not really be a great idea, so this is a case where making the index INVISIBLE for a while might be good, then after a few weeks (or months) drop them.