The best approach to tuning is to examine and tune individual operations rather than attempting to improve database performance as a whole. Find out from users if there are operations whose slow response time is causing negative effects on the business. Then, for each operation (in order of the severity of impact), set up a test and enable Oracle extended tracing (in version 9i and above, use the supplied package dbms_support; you will first need to install it by running the script $ORACLE_HOME/rdbms/admin/dbmssupp.sql as SYS). Run tkprof on the result tracefile, being sure to specify waits=yes on the command line. The output file will show you not only how your process consumed CPU, but how much of the operation's elapsed time consisted of waits for events such as disk I/O, latch gets, and enqueues. I highly recommend Cary Millsap's book Optimizing Oracle performance for a full explanation of this approach.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.