In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.
Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?
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.
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.
Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!
PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.
I’m currently working on a project that involves a system that is basically unknown to me. We also have to make changes (additions) to the system as part of our project. The system is a PL/SQL hard-shell application with all business logic and queries in the Oracle database and has about 1 million lines of code. Being a PL/SQL application, it has the great advantage that the source code is easily available for quick searches through views like all_source. We also know that all queries are in the database.
My requirement was to intercept and change the logic around the update of a specific column in a specific table. This means that I will have to find and examine all statements that inserts into or updates this table.Read More »