On our production system we’ve enabled the collection of PL/Scope metadata. Since this is a SmartDB/PinkDB-application (business logic and queries in the database), this makes it really easy to find, inspect and modify the source code of queries that doesn’t run efficiently. Now it’s even easier using reports in Oracle SQL Developer.

Since PL/Scope, from 12c onwards, collects information about SQL statements and presents them through the all_-/user_statements view, finding the source of a given SQL_ID is really easy, provided that:

• Your statements are in packages/procedures/functions
• Your statements are of type static (not dynamic)
• Your statements are not composed and sent from a client (end user, middle tier, anywhere outside the database)
• Gathering of PL/Scope metadata has been turned on – see the links at the bottom of the article.

If you have a SQL_ID from querying gv$sql, an AWR-report, a Statspack report, or anywhere else, you can simply query for it like this:

select s.owner, s.object_name, s.object_type, s.line, s.full_text
from all_statements s
where s.sql_id = '0g3qc8x27dqqx';

I’ve made a report, “Find a SQL by SQL_ID in source”, for Oracle SQL Developer to quickly find the query by the ID, and it also has a drill-down capability to jump straight into the line in the source code, provided it’s not wrapped.

A right click on the line and selecting “Go to <OBJECT_NAME>”, in this case P_EMPLOYEES, brings you directly to the package and at the line where the statement begins.

Quick and easy!

I’ve also expanded this concept a bit

In Oracle SQL Developer, there’s a set of reports, listing Top SQL-statements. However, these do not include the SQL_ID of the statements listed.

To make all of this easier, I’ve made a copy of the Top SQL reports in SQL Developer and added a column with the SQL_ID. This column is used as a drill-down key into the report I mentioned above:

So, selecting one of these, for example “Top SQL by CPU”, gives you the right-click option to find the particular SQL_ID in the source.

In some cases, the line that you are interested in is a call to a packaged function or procedure, like line 1 in the example below. In these cases it might very well happen that the load comes from a series of statements which each on their own isn’t resource consuming enough to get to the top of the regular lists.

In these cases you can use the report I’ve named “Top SQL in an Object”.

This does an outer join from all_statements to gv$sql, so that you can see what statements exists and which ones are in the cache. From there you can do the usual drill down as above.

Line 1-3 above is because I’ve added a twist with duplicated statements. As long as these have the same SQL_ID, we can’t differentiate between them. I’ll address that in a later blog-post.

This report is also nice to use to get a better overview over a package or packages performance, so that you can have a more holistic view over all queries and find solutions that applies across instead of just the one that came to your eye.

Note – the “Purge a cursor from cache” is of type “use at your own risk”, but it has the capability to purge just one cursor, based on the SQL_ID, from the cache. I think I based that on Carlos Sierras example.

I’ve uploaded the reports to my github repo, https://github.com/nicetheory/PL-Scope, so you can download and import them into your Oracle SQL Developer.

More info on PL/Scope (setting up) is found here: Finding dead code using PL/Scope

Edit: I’ve added a column, PLScope, to indicate if the query is found in all_statements.