Oracle Interview Preparation Notes Part 6

Shared Pool Tuning: Library Cache, Dictionary Cache, User Global Area (MTS)

Tuning the Library Cache: V$LIBRARYCACHE

A cache miss on the library or dictionary cache is MORE expensive than a DB buffer miss.
Concentrate on tuning library cache since Oracle holds dictionary data in memory longer.
If too small, resources must be dedicated which consumes CPU and causes contention.
Free memory in shared pool is considered wasted space and is often a sign of fragmentation.

1) Reduce Misses:
Allocate enough space to prevent statements from being aged out.
Use generic code and bind variables.
Avoid invalidations (changing schema objects referenced by the SQL).

2) Avoid fragmentation:
Reserve space for large memory requirements (using the init.ora SHARED_POOL_RESERVED_SIZE parameter [recommended 10% total size]).
Pin frequently used large objects (using DBMS_SHARED_POOL.KEEP(x)).
Avoid using large anonymous PL/SQL blocks – use small packaged functions.
GETS = The number of lookups for objects of the namespace.
PINS = Number of reads of the objects of the namespace.
RELOADS = Number of cache misses on execution causing implicit reparsing of statement.
Library cache hit ratios should be in the high 90s (select gethitratio from v$librarycache).
Reloads-to-pin ratio should be less than 1% (#of reloads < 1% total number of pins).
CURSOR_SPACE_FOR_TIME – parsed plans are not released until cursors accessing them are.
Tuning the Dictionary Cache: V$ROWCACHE
GETS = Number of requests
GETMISSES = Number of data requests resulting in cache misses.
The ratio of the sum of getmisses to the sum of all gets should be less than 15%.
Ratio can only be indirectly controlled by increasing overall size of shared pool.
Using the LARGE POOL
Not allocated, but configured - Memory comes directly from the SGA, not the shared pool.
Can be used by server processes, RMAN (backup and recovery) or MTS (UGA).
Tuning the Redo Log Buffer:

LOG_SMALL_ENTRY_MAX_SIZE: There is only 1 Redo Allocation Latch per instance. Limit amount of space per user by setting. If a user exceeds the max, then Oracle uses the REDO COPY LATCH to finish the request.

LOG_ENTRY_PREBUILD_THRESHOLD: Set to same as max size to force a prebuild before redo allocation latch is aquired.

Increase log buffer if:
• Log Buffer space event in v$session_wait is >0 and increasing.
• Redo buffer allocation retries > 1% of the redo log space requests in v$sysstat.

Increase the size and/or number of groups of the redo logs if:
• There are wait events for the “log file switch completion” (query v$system_event)
• “Checkpoint not complete” appears in the alert.log.

Increase the number of redo log groups if:
• The value of the “log file switch (arch” event in v$system_event > 0).

Reduce redo operations by:
• Direct path loading without archiving enabled.
• Direct path loading using the NOLOGGING option.
• Direct load insert in NOLOGGING mode.
• Using NOLOGGING in SQL statements (Create table as select, Create or alter index rebuild).

Database Configuration and I/O Issues

Use v$filestat or report.txt (utlbstat/utlestat) to determine I/O distribution.
Use Locally Managed Tablespaces:

•  Avoids data dictionary intervention.
• Fewer concurrency problems.
•  No performance implications for multiple extents – no reorgs or coalescing needed.
  

Tuning table scans:

• Query v$sysstat where name like ‘%table scans%’;
• Use DB_FILE_MULTIBLOCK_READ_COUNT to tune – how many blocks are read at a time during a full table scan.

Checkpoint Tuning (target usually every 30 minutes):

•  Frequent checkpoints decrease time need for recovery, but decrease performance as well.

DBWR tuning:

• DB_WRITER_PROCESSES = n. 
• DBWR_IO_SLAVES,  DB_BLOCK_MAX_DIRTY_TARGET