Twelve Silver Bullets For Oracle Performance Tuning
February 27th, 2004 by Mark Rittman
"Contrary to the pontifications of theoreticians and ivory-tower academics, there are many silver bullets for Oracle performance tuning. By silver bullet, I mean a small set of commands that quickly relieves an acute performance bottleneck. Some of these techniques of just-in-time tuning have been codified in Oracle10g with the Automatic Memory Management (AMM) facility, in which the SGA regions are changed dynamically to meet changing demands in processing."
writes Don Burleson for
dbazine.com. So what are they? According to Don;
- Fix missing CBO statistics
- Replace an obsolete statistics gathering method
- Initialize missing Oracle instance parameters
- Add missing indexes
- Implement cursor_sharing=force
- Implement the KEEP pool for small-table full-scanned tables
- Change the CBO optimizer parameters
- Add additional SGA RAM
- Employ materialized views
- Create bitmap indexes
- Add freelists
- Windows Oracle issues
Check out the article
for full details.
March 5th, 2004 at 3:08 pm
By and large I agree with the points here, but _oh dear_ you can really create a ton of unnecessary soft parsing with cursor_sharing=force. If you have vendor software that doesn’t use bind variables appropriately then this is a swell solution, but you really want to implement this via a logon trigger that is specific to that user.
We have software from two such vendors, and we have gotten great results from altering their sessions to cursor_sharing=force via such a SYSTEM-owned logon trigger.
Scott