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.