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 
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
Learning is not a spectator sport 
Good to know for troubleshooting
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
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!
Glad it was useful