Problematic SQL ? PL/SQL is your friend.

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

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” )

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