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
Connor McDonald

Connor McDonald is a former Oracle ACE Director who has joined the Oracle Developer Advocates Team. Over the past 25 years, he has worked with systems in Australia, the United Kingdom, Southeast Asia, Western Europe, and the United States. He has co-authored three books and has been a popular speaker at Oracle conferences around the world, specializing in topics regarding the database engine and PL/SQL. 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.