Performance Tuning Workshop
Strategic Summary
The order that database components should be tuned in:
1. Design
2. Applications
3. Memory
4. I/O
5. Contention
6. Operating System
For this tuning workshop, cannot tune the design or modify directly SQL code, or the operating system.
For those components we will be tuning, here are some guidelines:
Memory Tuning:
Shared Pool:
1. Is it big enough? Check the ratio of reloads to pins. This should be less than 1%.
2. Am I reusing SQL? Check the gethitratio. Should be > 90%.
3. How do I keep my shared pool from being fragmented? Identify large objects and pin them. Set up a reserved list!
Database Buffer Cache:
1. Is it big enough? Check the ratio of logical reads to physical reads. It should be >= 90%.
2. Am I getting Buffer Busy Waits? Check V$SYSTEM_EVENT. If so, why? Check V$WAITSTAT.
3. Am I making appropriate use of full table scans? Tune db_file_multiblock_read_count if appropriate.
Redo Log Buffer:
1. Is my redo log buffer (cache) big enough? Check V$SYSTAT to ensure that redo buffer allocation retries are less than 1% of redo entries.
General:
1. Not really something we can tune much of in this lab since our “disk farm” consists of a directory structure, but in real life you should start at the OS monitoring utilities. If they are pointing out a problem, then go to V$FILESTAT and try to figure out which Oracle files need working with.
2. Ensure that redo log files and archived logs are placed properly (again, in class we are just working with directories, but go ahead and follow the proper procedures just to get the feel for it).
3. Remember that you tune DBW0 (for VERY limited performance gains) with the parameter db_block_checkpoint_batch.
4. Check how often checkpoints are occurring. You can use the parameter log_checkpoints_to_alert or the report.txt for this. Don’t forget log_checkpoint_interval and log_checkpoint_timeout parameters.
5. Know your system. The use of multiple DBW0 processes or I/O slaves can help (again, not much in this lab).
Optimizing Sort Operations:
1. Are 90% or greater of sorts happening in memory? If I’m not administering a DSS or data warehouse, they should be. Check V$SYSSTAT for the ratio of sorts(memory) to sorts(disk).
2. Remember that you can use sort_area_retained_size as well as sort_area_size to help control memory usage.
3. Don’t forget that in production, you probably want more than one temporary tablespace. Do make sure that they are temporary!
Redo Logs
1. Are
my log groups big enough? How often am
I switching logs? In production, it
should be 15-60 minutes. In this
workshop, it should take at least 3-5 minutes! Check V$LOG_HISTORY.
2. Do
I have enough log groups? Am I
completing checkpoints without having to wait for the next available redo log
group? Check V$SYSTEM_EVENT to see if log file switch (checkpoint
incomplete) is happening.
Rollback Segments:
1. Do I have enough? Check V$ROLLSTAT to ensure that waits are less than 5% of gets.
2. Are they big enough? Check V$ROLLSTAT. If there are extends then you either need to:
a. Increase the size of the rollback segments, or
b. Find out who is blocking the rollback segments
Note: Remember that in report.txt extends doesn’t show up so you will have to look at shrinks instead.
3. Are they sized for special cases? Do I have large rollback segments for large transactions?
1. Am I experiencing contention for the transaction table in my rollback segments? Check V$SYSSTAT and get the number of consistent gets. Check V$WAITSTAT and look for excessive waits in the class undo block. Excessive could be defined as undo blocks more than 1% of consistent gets.
2. Am I seeing contention for my lru latches? Check V$LATCH and look for the ratio of sleeps to gets. It shouldn’t be much higher than 1%. Remember that you are limited to 2 x #CPUs x #buffer pools for db_block_lru_latches. How many CPUs does your server for this lab have?
3. Am I seeing contention for the free lists? Check V$WAITSTAT and look at class segment header. There is not a magic number here, but uf you see the time start to become significant, this can be and indication of freelist contention. Remember that your only option here is to reorganize the data since freelists are not dynamically configurable.
Look at Tablespace Organization (Memory, I/O, Contention potentially all issues here):
1. Are data, index, rollback, temp and system being kept separate from each other?
2. Are all of your tablespaces (except SYSTEM) being locally managed? This is much more efficient (faster) than dictionary managed tablespaces.
3. Are any of your objects extending too frequently? This takes extra time.
4. Monitor your indexes. Keep an eye out for excessive percentage of delete leafs. This keeps the B-tree artificially tall and makes retrievals slower. Rebuild as necessary.
5. Watch for row migration. If it becomes more than about 10% of the data in the table, you need to consider changing pctfree and/or reorganizing the table.