I was about to optimise some code from using FORALL to work set based, but a small test changed my mind.
Read More »
Tag: Optimization
Function result cache, yes or no?
Oracles function result cache (FRC) works, in simplified terms, by caching the result of a PL/SQL function. This can greatly improve performance, but it can also lead to performance problems.
Read More »Which tables could need updated statistics?
Does your tables need updated statistics for the optimizer? Here’s a couple of views that could be useful in the gathering of information.
Read More »Statistics gathering parameters for partitioned tables
When partitioned tables get a bit large, these parameters may be suitable to set.
Read More »
Reviewing statements with hints, using PL/Scope
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.
Differentiating between duplicated statements
Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?
Find bad performing queries or code with PL/Scope
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.
Function result cache and application context – don’t get burned
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?
Compression of Oracle database network traffic
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.
- nice theory... 