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.
Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?
I read this blog-post by Connor McDonald the other day about the Advanced Network Compression and network transfer savings. It reminded me of a feature not many know of and comes without an extra cost option. In fact, it’s on by default.
Large joins may use full scans and hash joins. If your tables are large enough, this will fill up your process working memory and start spilling to your temp-tablespace. At that time a few important effects come into play:
Lately I’ve been working a lot with large bulk-loads of data between Oracle databases. The loading is done over db-links and we needed to speed up some of the loads since parallel DML is not supported in distributed transactions.
