Experts agree on adjusting
Oracle tuning parameters
Anjo Kolk, creator
of the oraperf tool, notes that changing important Oracle optimizer
parameters can have a profound impact on database performance:
So the decision
made to include the directio mount option again and at the same time
change two init.ora paramters: optimzer_index_cost_adj and
optimzer_index_caching. The database was bounced and a simple batch
job was run to validate the performance improvement. That batch job
ran before with 50-70 calculations per hour and after the changes it
ran with 2500+ calculations per hour. There was a factor of 35-50
broad-brush approaches have a downside too, as Kolk noted when some
SQL began to take-on sub-optimal plans:
So this was great news of course,
however the next day one of the other databases on the system
reported a severe performance problem. And in fact a quick check
revealed that it was waiting most of the time in I/O related events.
A further analysis showed a couple of things:
The Oracle buffer cache was
really small and so the File System buffer cache was heavily used to
compensate for that small Oracle buffer cache.
It also showed that a handful of
statements were responsible for 80+ percent of the I/O workload.
In fact it was discovered that
one statement did around 600K physical I/Os in just over 2 hours.
And there were a couple of other
statements just like that.
In my latest book
Tuning: the Definitive Reference”, I show a script that can be
used to compute the starting values for optimizer_index_cost_adj and
optimizer_index_caching, based on historical data. Here is the
listing from this script in the
Oracle Tuning Book:
Percent of Percent
Waits for Average Waits for I/O Waits I/O Waits cost
Read I/O Index Read I/O for Full Scans for Index Scans adj
------------------ ----------------- -------------- ---------------
1.473 .289 .02