Tony’s Oracle Tips

January 17, 2010

Identifying the SQL_ID of static SQL in PL/SQL blocks

Filed under: Uncategorized — tonyhasler @ 8:34 pm

I was asked recently if it was possible for a PL/SQL block to identify information about SQL statements the block itself was running. For example, it might be nice during development of a rapidly changing piece of code to log the text and/or execution statistics of a piece of static SQL embedded in the block. 

All of this would be possible were it possible to identify the SQL_ID and CHILD_NUMBER of the static SQL you are interested in. To do this you need to understand how the following four columns in V$SESSION are populated: 

  • SQL_ID
  • SQL_CHILD_NUMBER
  • PREV_SQL_ID
  • PREV_CHILD_NUMBER

The basic idea is that the first two columns are supposed to relate to the current SQL statement and the last two to the previous statement. However, these terms are a little ambiguous when you consider that an anonymous PL/SQL block is considered a SQL statement and that, therefore, when a PL/SQL statement executes static SQL there are always at least two ‘current’ SQL statements. Indeed, when we consider recursive SQL and cursors there could be many ‘current’ SQL statements running at the same time. The rules that I have been able to demonstrate are: 

  • When a SQL statement begins ‘running’ (I can’t be much more precise than that) or regains control from another SQL statement it sets the SQL_ID and SQL_CHILD_NUMBER columns.
  • When a SQL statement completes it sets the PREV_SQL_ID and PREV_CHILD_NUMBER columns.
  • When an invalid SQL statement is issued the SQL_ID and SQL_CHILD_NUMBER are set to NULL.

Consider this PL/SQL block: 

DECLARE
   x   NUMBER;
BEGIN
   SELECT   1 INTO x FROM DUAL;

   DBMS_LOCK.sleep(10);
END;
/

When the PL/SQL block starts the SQL_ID column of V$SESSION references the entire block. While the embedded SELECT statement is running the SQL_ID column is changed to reference the SELECT statement but the PREV_SQL_ID column is unchanged. When the SELECT statement completes it sets PREV_SQL_ID. The PL/SQL block then regains control and sets SQL_ID back to reference itself. This is what you will see whilst the sleep is running because PL/SQL functions like DBMS_LOCK.SLEEP do not change the SQL_ID (unless, of course, they contain embedded SQL themselves). 

When the PL/SQL block as a whole completes the PREV_SQL_ID column is set to the same value as the SQL_ID column i.e., to reference the PL/SQL block. 

With this understanding we can now code a function to capture information about an embedded SQL statement in a PL/SQL block. The code will look something like this: 

CREATE OR REPLACE PACKAGE BODY DRD.SQL_UTIL
AS
   FUNCTION GET_LAST_SQLTEXT
      RETURN CLOB
   IS
      l_sql_text   CLOB;
   BEGIN
      SELECT   sql_fulltext
        INTO   l_sql_text
        FROM      v$sql sq
               JOIN
                  v$session se
               ON     sq.sql_id = se.prev_sql_id
                  AND se.prev_child_number = sq.child_number
                  AND sid = SYS_CONTEXT('USERENV', 'SID');

      RETURN l_sql_text;
   END;
END SQL_UTIL;
/

Let us try and use this: 

DECLARE
   x   NUMBER;
BEGIN
   select   1 INTO x FROM dual;
   DBMS_OUTPUT.put_line(sql_util.get_last_sqltext);
END;
/

The output from this PL/SQL block will be


SELECT 1 FROM DUAL

This is because the PL/SQL compiler always uppercases the statement and because the ‘INTO x’ is a PL/SQL specific extension to the SQL statement and not part of the SQL statement proper. 

For cursors you have to wait until the cursor is closed: 

DECLARE    CURSOR x    IS       SELECT /*+ tag1 */    dummy FROM DUAL;
    y        DUAL.dummy%TYPE;
BEGIN
   OPEN x; 
   LOOP
      FETCH x INTO y;
      EXIT;
   END LOOP; 

   CLOSE x; 

   DBMS_OUTPUT.put_line(sql_util.get_last_sqltext);
END;
/ 

The output of this block is:

SELECT /*+ tag1 */ DUMMY FROM DUAL

This demonstrates another cool point. Although most comments are removed from static SQL, you can use a tag that looks like a hint to help you identify your code uniquely.

Advertisements

4 Comments »

  1. Interesting info … keep on with the good work !!!

    Comment by Patrick Boulay — July 11, 2011 @ 7:34 am | Reply

  2. Known pseudo hint sql tagging method has alternative.

    Proved by you )

    Thanks

    Comment by Toleg — December 9, 2011 @ 4:08 pm | Reply

  3. hi, Tony. At trigger level get another sql, why could this be? Thanks
    DECLARE
    x NUMBER;
    BEGIN
    insert into hr.countries values (‘CL’,’CHILE’,2); — fire a trigger and prints but wrong SQL
    commit;
    DBMS_OUTPUT.put_line(‘2) ‘||get_last_sqltext); — Prints correct SQL
    END;
    /*
    *
    * trigger
    *
    */
    create or replace trigger trg_cafu_ariud
    after insert or update or delete
    on hr.countries
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    declare
    v_sql_statment varchar2(32767);
    begin
    v_sql_statment:= to_char(dbms_lob.substr(sys.get_last_sqltext));
    dbms_output.put_line(‘1) sql text: ‘||v_sql_statment);
    end;
    /*
    *
    * Output
    *
    */
    1) sql text: begin :id := sys.dbms_transaction.local_transaction_id; end;

    2) INSERT INTO HR.COUNTRIES VALUES (‘CL’,’CHILE’,2)

    Comment by Alex Honig — December 26, 2012 @ 7:09 pm | Reply

    • Alex,

      This issue is discussed on OTN here with links to Oracle Support articles on the topic:

      https://forums.oracle.com/forums/thread.jspa?messageID=10085890

      According to the above it should be possible in 10g to use SQL_ID and SQL_CHILD_NUMBER to get the details of the triggering SQL but I have been unable to get this to work in 10.2.0.4. Maybe this is an issue with patch levels or whatnot.

      Your example highlights something else of interest: statements sych as COMMIT and DDL statements do not have associated cursors or sql_ids so do not affect SQL_PREV_SQL_ID in V$SESSION.

      Comment by tonyhasler — December 27, 2012 @ 11:59 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: