Poor Discoverer Performance

During the very start of my career, I used to be very intimated by the oh-so-famous Discoverer… Though the reason for this phobia always slipped my mind! But then, there are instances like the one I would be discussing here, which might be passed as a substantial reason!

Scope:

Oracle Discoverer - Version: 10.1.2.2

Issue:

On one of our production systems, we upgraded Discoverer 4 to 10, which seemed like a good move till the users started mailing like crazy that the reports that took 45 mins, now were taking 6 hrs!!

Analysis:

On checking the SQL being used by the Discoverer, we noticed that it was appending the hint /*+ NOREWRITE */ to every query. Also, when we executed the queries without this hint, through a client like say TOAD, the performance improved.

So we came to the conclusion that, Discoverer quite conveniently was not using the CBO(though all our tables were analyzed).

Solution:

We made the following changes to pref.txt:

1. UseOptimizerHints = 1 – Which was initially 0
2. UseNoRewriteHint = 0 – Which was initially not included(by default it was picking 1)

After editing the above file, applypreferences.sh needs to be run for the changes to take effect. Bouncing the Discoverer server is not necessary. Also on adding the above, Discoverer started using the ALL ROWS hint and stopped using the NOREWRITE hint.

Though, Oracle strongly recommends quite contrary to the changes we made, it still did work really well for us!! The only catch here is that the concerned tables have to be regularly analyzed or the performance would degrade manifold. Hope that helps!