I was going through some of the Top SQL-reports in SQL Developer, running them against our production system.

One of the “culprits” that showed up was a procedure call, not a query. A quick investigation showed that this procedure was fairly large and consisted of quite a few queries.

BEGIN MySchema.SomePackage.AProcedure(:INDATA, :OUTCUR); END;

Since I, at a glance, couldn’t find a single query with a cpu/buffer gets consumption close to the procedure, I suspected that there could be several queries in the same unit that needed attention. I decided that I didn’t want to use too much energy on identifying these, so I simply put PL/Scope to the task. We actually have PL/Scope activated on this production system without any problems so far. One of the things that PL/Scope does, is collect all statements into the all_|user_statements view. It also records the sql_id for the query – the same sql_id that you can find in gv$sql. So – from there – I quickly wrote a query, listing the sql_id for all SQL-statements in the given package, joining them to gv$sql:

-- Find queries in a package in v$sql, using PL/Scope
SELECT st.LINE,
  s.EXECUTIONS,
  s.BUFFER_GETS,
  trunc(s.BUFFER_GETS / nvl(s.EXECUTIONS, 1)) gets_pr_exe,
  s.SQL_TEXT,
  s.SQL_ID,
  s.CPU_TIME,
  s.ROWS_PROCESSED,
  s.ELAPSED_TIME,
  s.LAST_ACTIVE_TIME
FROM   gv$sql s
JOIN   all_statements st ON s.SQL_ID = st.SQL_ID
WHERE  st.OBJECT_NAME = 'SOMEPACKAGE'
AND    st.OWNER = 'MYSCHEMA'
-- Pick something to order by
ORDER  BY
  --s.ELAPSED_TIME DESC,
  BUFFER_GETS / NVL(EXECUTIONS, 1) DESC;

And ta-daaa! Among a fairly sized list of statements, there were 8+ statements that stood out. Fixing them were easy (adding a couple of indexes), but finding them was even easier with the power of PL/Scopes insight. It’s almost so that the fun of the hunt is gone.

And – of course – you can reverse the query and find out what package and line number a given sql_id originates from. That part is left as an excercise to the reader.

See my earlier blog-post on PL/Scope for references to other PL/Scope resources.