Friday, October 15, 2021

Getting the SQL_ID in the cloud or not


Oracle as a company is always adding cool stuff to the database, and often these things are just small enough to get missed by many of us.  Here are two things that I have happened to miss in some recent releases. 

 

 

This first one came out in 11 timeframe, so not super new.  It was enhanced in 12 and is quite cool.  One of the real headaches as a SQL Optimizer is PL/SQL blocks of code.  When you run a block (a procedure for example) that has several SQL statements in it, how do you get the SQL_ID for each of those statements?  It’s not trivial to figure it out.  The new PLScope setting makes it much easier.

 

 

PLScope is intended to be used in a development like environment.  Using it can slow down compile time for a program unit in particular since it’s recording information about the program unit that isn’t normally tracked.   

 

 

Turning it on is pretty simple:  

 

ALTER SESSION SET plscope_settings = 'IDENTIFIERS:ALL,STATEMENTS:ALL';

 

This gets “everything”.  The Identifiers setting has several options, look in the documentation for the full list.  With ALL you get all the source code identifiers.  These will be things like procedure/function names and variable names.  The Statements setting is either ALL or NONE.  This is the one of interest to me for SQL optimization, this gets information about the statements in the PL/SQL code.  Key here is the SQL_ID. 

 

 

The data is stored in tables that are viewable with the typical Oracle views, DBA/USER/ALL then either STATEMENTS or IDENTIFIERS.  As in DBA_STATEMENTS and DBA_IDENTIFIERS.   

 

 

Some sample output of these here (some code will be at the end):

 

 

DBA_STATMENTS:

 

SQL_ID        OBJECT_TYPE  TYPE              TEXT                                    

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

5w0tcp062c753 PROCEDURE    SELECT            SELECT COUNT(STATUS) FROM BIG_TAB WHERE 

                                             OWNER = :B1 AND STATUS = 'VALID'       

 

fxu1q76gwaqm0 PROCEDURE    SELECT            SELECT DISTINCT OWNER FROM BIG_TAB WHERE

                                              OWNER IN ('OP','HR','PUBLIC','BI') ORDE

                                             R BY OWNER                              

 

              PROCEDURE    EXECUTE IMMEDIATE                                         

b2nuzkxjcqntj PROCEDURE    SELECT            SELECT COUNT(STATUS) FROM BIG_TAB WHERE 

                                             OWNER = :B1 AND STATUS = 'INVALID'      

 

g4ucahfccny0q PROCEDURE    SELECT            SELECT COUNT(DISTINCT OBJECT_TYPE) FROM 

                                             BIG_TAB WHERE OWNER = :B1              

 

aft61pqqpr284 PROCEDURE    SELECT            SELECT COUNT(1) FROM BIG_TAB WHERE OWNER

                                              = :B1 AND SHARING = 'NONE'             

 

c8d7w66f75fcb PROCEDURE    SELECT            SELECT COUNT(DISTINCT OBJECT_NAME) FROM 

                                             BIG_TAB WHERE OWNER = :B1               

 

 

Notice that the one statement that is an EXECUTE IMMEDIATE  type doesn’t have a SQL_ID.  That is because it’s a dynamic SQL statement that will be parsed and run at run time.  The SQL_ID can’t be computed now since the statement is built on the fly.  Keep in mind, this information is recorded when the block is compiled, not when it run.  

 

 

For Identifiers you can get some cool output like this:

 

 

IDENTIFIER_USAGE_CONTEXTS                                    

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

Load_Table_Demo1.... procedure           declaration         

  Load_Table_Demo1.. procedure           definition          

    Owntab.......... nested table        declaration         

      Big_Tab....... table               reference           

        Owner....... column              reference           

    Bt_Own.......... variable            declaration         

      Owntab........ nested table        reference           

    V_Obj_Nm_Cnt.... variable            declaration         

      Number........ number datatype     reference           

    V_Obj_Typ_Cnt... variable            declaration         

      Number........ number datatype     reference           

    V_Vld_Cnt....... variable            declaration         

      Number........ number datatype     reference           

    V_Invld_Cnt..... variable            declaration         

      Number........ number datatype     reference           

    V_S_Non_Cnt..... variable            declaration         

      Number........ number datatype     reference           

    Sql_Stmt........ variable            declaration         

      Varchar2...... character datatype  reference           

    I............... iterator            declaration         

      Bt_Own........ variable            reference           

      Bt_Own........ variable            reference           

      Sql_Stmt...... variable            assignment          

 

 

This is some very good information that can be quite handy while developing code and I think the statement level stuff in particular could be useful for run time debugging and optimization as well. 

 

 

The other new thing is also about getting the SQL_ID.  This one gets at the age-old question; how do I get the SQL_ID of the statement I just ran?  Well, this isn’t that hard to do, but it’s even easier now.  In SQL Plus (along with SQLcl and SQL Developer) you can now set FEEDBACK to give you the SQL_ID!  This is new as of 18.   This is really simple:

 

 

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.2.0.0.0

 

SQL> set feedback on sql_ID

SQL> select to_char(sysdate, 'DD-MON-YYYY hh24:mi') "Now" from dual;

 

Now

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

12-OCT-2021 17:39

 

1 row selected.

 

SQL_ID: 7w778r58td0nv 

SQL> 

SQL> select count (*) from ric.emp;

 

  COUNT(*)

----------

      14

 

1 row selected.

 

SQL_ID: 33qh87w6f8rcd

 

 

 

Boom!  The SQL_ID right after each SQL statement.  Man, I wish I had had this for the 100’s of years I’ve been working with Oracle.  

 

 

Normally I’d give all the code for examples in my post.  For the PLScope the code is rather long.  I’m just providing here the queries I used to get the output you saw above.  If someone really-really wants the code for the table and procedure I used in my test case, send me an email and I’ll get it to you as soon as I can.  

 

 

Code for select to get the statement information:

 

 

SELECT

    sql_id, object_type, type, text

  FROM

    DBA_statements

 WHERE

    object_name = upper('load_table_demo1');

 

Code for the identifier information (this is from the documentation originally):

 

 

WITH ID_CTE AS (

  SELECT   

      Line, Col, INITCAP(NAME) Name, LOWER(TYPE)  Type,

      LOWER(USAGE)  Usage, USAGE_ID, USAGE_CONTEXT_ID

    FROM DBA_IDENTIFIERS

      WHERE Object_Name = upper('load_table_demo1')

)

SELECT

     RPAD(LPAD(' ', 2*(Level-1)) ||

                 Name, 20, '.')||' '||

                 RPAD(Type, 20)||

                 RPAD(Usage, 20)

                 IDENTIFIER_USAGE_CONTEXTS

  FROM ID_CTE

  START WITH USAGE_CONTEXT_ID = 0

  CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID

  ORDER SIBLINGS BY Line, Col;

 

No comments:

Post a Comment