Oracle SGA

1. SGA components
- db buffer = DB_CACHE_SIZE
- shared pool = SHARED_POOL_SIZE
- redo log buffer = LOG_BUFFER
- large pool = LARGE_POOL_SIZE
- jva_poo – JAVA_POOL_SIZE

2. New view in new ST04 under Peformance > Stats Info > SGA

3. Dynamic SGA
- allows resezing SGA components ( buffer, shared pool and large pool)  without restarting at runtime. The others SGA components need to be restarted.
- sga_max_size = maximum size for total SGA. If not defined, the value is the total sga size.
- large sga will cause swapping.

4. SGA info
- V$SGA_CURRENT_RESIZE_OPS  = Current SGA size change operation(s)
- V$SGA_RESIZE_OPS = List of the last 100 SGA size change operations carried out
- V$SGA_DYNAMIC_COMPONENTS = Display detail info and history
- V$SGA_DYNAMIC_FREE_MEMORY = Display of the memory available for future SGA enhancements

5. Tuning DB buffer
- Cache size advice based on data buffer quality/hit ration and V$DB_CACHE_ADVISE (parameter DB_CACHE_ADVICE = ON or STATISTIC_LEVEL = typical ).
- SQL>ALTER SYSTEM SET STATISTICS_LEVEL=’TYPICAL’;
- high hit ration doesnt mean good sizing, as it could be due to expensive SQL. if ration buffer get : user call  > 15, hit ratio is   not a good indicator. Check for log read/user call value.ST04 and ST04OLD use diff method to collection of hit ratio.
- use ST04, cache advisory tab, the check the values of physical read for a range of cache size. Objective is to have less phy     read or until the phy read is constant after adding more cache size.

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/memory.htm

6. Tuning Shared pool
- If the shared pool is too small, then extra resources are used to manage the limited amount of available space.  This consumes CPU and latching resources, and causes contention and possibly ORA-4031
- Use v$shared_pool_advice
- Check the freepool in v$sgastat to see free memory

Advertisement

~ by onnry on September 12, 2008.