When it comes to database tuning, we usually don't provide any such recommendations due to the fact that the configuration would vary from system to system.
There are no generic recommendations from Clarity on tuning. However, based on 'rule of thumb' type principle, we can suggest a couple of configuration options which respond well generally.
- Tune the shared pool size.
You can tune the shared pool by using the following query:
- SELECT sum(pins) "Executions",
sum(reloads) "Cache Misses whilexecuting",
sum(reloads)/(sum(pins)+sum(reloads))*100 "Ratio of Misses"
- The ratio of misses should be less than one percent.
If it is above one percent, the init.ora value of SHARED_POOL_SIZE should be increased incrementally until the value of executions approaches zero.
- Tune the buffer cache.
Changes to the buffer cache are made by altering the DB_BLOCK_BUFFERS.
One way to determine if you need more memory allocated is to run the following query as the sys user:
- SELECT name, value
WHERE name in ('db block gets', 'consistent gets', 'physical reads');
- Monitor these statistics over a period of time while Clarity is running.
Calculate the hit ratio for the buffer cache with this formula:
- Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))
The closer the hit ratio approaches 1.00, the better your system will perform
- If you still have free memory and the hit ratio is below .95, try increasing the value of DB_BLOCK_BUFFERS.
- Make sure you have at least 5% free memory.
Keywords: CLARITYKB, Oracle Performance, Buffer Pool.