Chapter 14 Performance Management

Use Automatic Memory Management
Automatic PGA Memory Management
WORKAREA_SIZE_POLICY=AUTO
PGA_AGGREGATE_SIZE=20% of SGA Size (dynamic, automatic)

Automatic Shared Memory Management (ASMM)

SHARED_POOL_SIZE (dynamic, automatic)
LOG_BUFFER (static,fixed)
DB_CACHE_SIZE (dynamic, automatic)
LARGE_POOL_SIZE (dynamic, automatic)
STREAMS_POOL_SIZE (dynamic, automatic)
JAVA_POOL_SIZE (dynamic, automatic)

SGA_TARGET (dynamic, automatic)

Automatic Memory Management
MEMORY_TARGET (dynamic, automatic) = PGA + SGA
MEMORY_MAX_TARGET (static)

Use Memory Advisors
V$PGA_TARGET_ADVICE
V$SGA_TARGET_ADVICE
V$SHARED_POOL_ADVICE
V$MEMORY_TARGET_ADVICE

Troubleshoot Invalid and Unusable Objects
PL/SQL objects and Views can become INVALID due to table definition change.
SELECT object_type, object_name, status FROM DBA_OBJECT WHERE status='INVALID';
ALTER object_type object_name COMPILE; (Manually Compile)
EXECUTE the produre (Automatic Compile when accessed)
SELECT the view (Automatic Compile when accessed)

Indexes can become UNUSABLE due to ALTER TABLE table_name MOVE command.
SELECT index_type, index_name, status from DBA_INDEXES WHERE status='UNUSABLE';
ALTER INDEX index_name REBUILD [ONLINE] [NOLOGGING]; (Only Manual Rebuild option is available)

SKIP_UNUSABLE_INDEXES=TRUE (default) always success exception if it is to enforce constraint, table will be locked for DML.