Re: ORACLE Performance Tuning - Your experiences

This is Steven Karniotis. Primarily, I use several pieces. First, I watch the Buffer Cache Hit Ratio of the Database. I try to ensure that performance of the buffer cache is 90% or higher. Now this can be accomplished by properly setting DB_BLOCK_BUFFERS or setting it very high and not worrying about the ramifications. I have several SQL scripts that are used to help me in setting the
optimal value.

Next, I examine file i/o distribution and file i/o statistics to determine if I/O is being performed on any one or two files that may be split up into multiple files. I also look at sorting and table scans.

Besides statistics, look at rollback segments. If you see that rollback segments are growing and never shrinking, think of implementing OPTIMAL sizing. Also, review the size of your transactions. They may also be to big.

Watch the I/O to your temporary tablespace. Large amounts of I/O indicate sorting via disk. May need to examine SORT_AREA_SIZE value.

There are multitudes of places Oracle can be tuned. I suggest you purchase "Tuning Oracle" by Corey & Abbey. I would also suggest attending IOUW this year as there are may papers on performance tuning. Received on Fri Aug 23 1996 - 00:00:00 CEST