Problematic SQL ? PL/SQL is your friend.

Posted by

So then… I’ve written a crappy piece of SQL. It wouldn’t be the first time I’ve done it… and it probably won’t be the last time I do it Smile

But at least I’ve done one thing right…I’ve encapsulated the SQL inside a PL/SQL procedure.



SQL> create or replace procedure P is
  2    x int;
  3  begin
  4  --
  5  -- this is my proc, and it has one very poor SQL
  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_objects;
 18
 19    select count(*)
 20    into   x
 21    from   dba_objects, dba_objects;
 22
 23  end;
 24  /

Procedure created.

Let’s give that procedure a run …



SQL> exec P
[waiting]


We’re going to be waiting a while for that one to finish… a long while 🙂

As a performance tuner, you might want to see what’s been running for a long time on your system. And that’s easy with a query to V$SESSION


SQL> select username, sql_id
  2  from   v$session
  3  where  status = 'ACTIVE'
  4  and    last_call_et > 10
  5  and    username is not null;

USERNAME                                      SQL_ID
--------------------------------------------- -------------
MY_USER                                       ff35fbgz27513


And since I’ve got the SQL_ID, its just as easy to look up the SQL text in V$SQL


SQL> select sql_text
  2  from   v$sql
  3  where  sql_id = 'ff35fbgz27513';

SQL_TEXT
----------------------------------------------------------------
SELECT COUNT(*) FROM DBA_OBJECTS, DBA_OBJECTS

But now what ? Somewhere in my application code, is a SQL statement that starts with “SELECT COUNT(*)” and its running badly. How do I find it ? (This is the polite way of saying “How do I locate the desk of the person that wrote it” Smile )

Luckily for me, it is coming from PL/SQL. Because finding the source of the statement, is then trivial. On V$SQL there is also two columns of interest:


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

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

And once I’ve got those, I now have a direct link back to the originating code for that problematic SQL, including the line number where its run.


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

OWNER                          OBJECT_NAME
------------------------------ -----------------
MY_USER                        P

Easy peasy

5 comments

  1. Very useful, thanks Connor 🙂
    This inspired me to create script dba_slow_sql.sql with following content:

    /*
    || Basing on a blog post by connor mcdonald
    || https://connor-mcdonald.com/2016/01/20/problematic-sql-plsql-is-your-friend/
    */
    set linesize 150
    set trimout on trimspool on
    set verify off

    col SQL_ID new_value SQLID
    col SQL_TEXT for a80 wrap
    col USERNAME for a15

    prompt
    prompt [INFO]: list long running SQL statements …
    prompt

    select USERNAME,
    SQL_ID
    from v$session
    where STATUS = ‘ACTIVE’
    and LAST_CALL_ET > 10
    and USERNAME is not null
    ;

    prompt
    accept sql_id char prompt “[INPUT]: provide an sql id to display the full statement [&SQLID.]: ” default “&SQLID.”
    prompt

    prompt
    prompt [INFO]: Identify the SQL text for SQL_ID=’&sql_id.’ …
    prompt

    col SQL_TEXT for a80 wrap

    select SQL_ID,
    SQL_TEXT
    from v$sql
    where SQL_ID = ‘&sql_id.’
    ;

    prompt
    prompt [INFO]: Identify program and line nr. for SQL_ID=’&sql_id.’ …
    prompt

    col PROGRAM_ID new_value PROGRAM_ID
    col PROGR_LINENO new_value PROGR_LINENO

    select PROGRAM_ID,
    PROGRAM_LINE# as PROGR_LINENO
    from v$sql
    where SQL_ID = ‘&sql_id.’
    ;

    — —————————————————
    — Just in case if there was no SQL_ID found
    — and sqlplus variable PROGR_LINENO was not set
    — (a default value of -99 is used then)
    — —————————————————
    set termout off
    var lnr number
    execute :lnr := nvl( ‘&PROGR_LINENO.’, -99);
    select trim(‘&PROGRAM_ID.’) as PROGRAM_ID,
    :lnr as PROGR_LINENO
    from DUAL;
    set termout on

    prompt
    prompt [INFO]: Identify object/program for OBJECT_ID=’&PROGRAM_ID.’ …
    prompt

    col OWNER for a12 new_value OWNER
    col OBJECT_NAME for a30 new_value OBJECT_NAME

    select OWNER,
    OBJECT_NAME,
    OBJECT_TYPE,
    OBJECT_ID
    from DBA_OBJECTS
    where OBJECT_ID = trim(‘&PROGRAM_ID.’)
    ;

    prompt
    prompt [INFO]: Identify suboptimal SQL in object/program for OBJECT_ID=’&PROGRAM_ID.’ …
    prompt

    col LINE for 99999 hea “Line|Nr”
    col TEXT for a100
    col MARK for a4

    select case when nvl(LINE, -1) = &PROGR_LINENO.
    then ‘—>’
    else NULL
    end as MARK,
    LINE,
    TEXT
    from DBA_SOURCE
    where OWNER = trim(‘&OWNER.’)
    and NAME = trim(‘&OBJECT_NAME.’)
    and LINE between &PROGR_LINENO.-3
    and &PROGR_LINENO.+3
    order by LINE
    /

    undefine sql_id
    undefine PROGRAM_ID
    undefine PROGR_LINENO
    undefine OWNER
    undefine OBJECT_NAME
    undefine LINE
    undefine TEXT

  2. This post was great! I’ve been chasing a problem for days, trying to find how a sql statement was in v$session! I had no idea about v$sql.program_id. I used it and viola!

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.