We found a bug in some old code. And we fixed it. Then other stuff started failing. It turned out that there were some other code, using the same function, that worked because of the bug. That forced us to roll back the fix and thereby re-introduce the initial bug that weren’t as critical.
So – we had to find a way to find out which code-paths were working because of the bug so we could fix those.
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.
Thanks to all of you that attended my PL/Scope session at Code One in San Fransisco!
You can find the scripts from my presentation about using PL/Scope to find your way through a PL/SQL code base in my github repository here: https://github.com/nicetheory/PL-Scope
Working on a routine for dropping old partitions from a log table, I realized that I don’t have to know the name of the partition I want to drop. I can just specify a value for the partition key and Oracle resolves the partition for me. Here’s how.
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.
