Subscribe

Share

DBA

A Higher-Level Perspective on SQL Tuning, Part 2, PL/SQL

Tracking SQL back to a PL/SQL stored program unit

The following steps demonstrate how to use the V$SQL PROGRAM_ID, and PROGRAM_LINE# columns to track SQL back to a PL/SQL stored program unit.

First I create a simple procedure that executes some SQL statements:

SQL> create or replace procedure MY_USER.MY_PROC is
  2    x int;
  3  begin
  4  --
  5  -- procedure to count some dictionary views
  6  --
  7    select count(*)
  8    into   x
  9    from   dba_views;
 10
 11    select count(*)
 12    into   x
 13    from   dba_tables;
 14
 15    select count(*)
 16    into   x
 17    from   dba_synonyms;
 18
 19    select count(*)
 20    into   x
 21    from   dba_objects;
 22
 23  end;
 24  /

Procedure created.

After the procedure has run, I can track the SQL information in V$SQL, using the normalized version of the SQL text.

SQL> select sql_id, sql_text
  2  from   v$sql
  3  where  sql_text like 'SELECT COUNT%DBA_OBJECTS';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------
ff35fbgz27513 SELECT COUNT(*) FROM DBA_OBJECTS

For this SQL_ID, I mine the PROGRAM_ID and PROGRAM_LINE# columns, which track the SQL back to its parent procedure.

SQL> select PROGRAM_ID, PROGRAM_LINE#
  2  from   v$sql
  3  where  sql_id = 'ff35fbgz27513';

PROGRAM_ID PROGRAM_LINE#
---------- -------------
    102001            19

SQL> select owner, object_name
  2  from   dba_objects
  3  where  object_id = 102001;

OWNER                          OBJECT_NAME
------------------------------ -----------------
MY_USER                        MY_PROC