10/12/2016

[Oracle] Initialise DB statistics to improve performance

A couple days ago we were analysing a customer issue concerning DB performance on simple and already optimised queries (indexes + hints).

Somehow, the customer had bad performance even after calculating statistics for tables and indexes. Turns out that everything was good except for the fact that Oracle can't imagine future statistics without some help; if a table is only filled after some time that the applications run, it is important to gather statistics again after the first initialisation.

It is also possible however to provide fake statistics immediately after the objects are created or modified so that the DB already has an idea of how the objects will look like and choose different query execution plans than the ones it would use for freshly created objects.

That's what the DBMS_STATS package and its EXPORT__STATS and IMPORT__STATS procedures are for.

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.