Automatic Undo Management
Prior to Oracle 9i, a DBA had to manage rollback tablespaces and rollback segments manually. Failure to allocate enough segments, or to allocate enough space for those segments, would invariably leads to "ORA-01555: snapshot too old" error during long transactions. Since the advent of 9i, that worry can, and should, largely be eliminated.
3 new initialization parameters were added: UNDO_MANAGEMENT, UNDO_RETENTION, and UNDO_TABLESPACE.
To activate automatic undo management at least one undo tablespace exists.
set the UNDO_MANAGEMENT = AUTO
set the UNDO_RETENTION = 0 (zero),
Oracle will automatically tune for maximum retention of undo information based on the space available in the target undo tablespace, with the caveat that this automatic tuning mechanism will never tune for less than 15 minutes of retention.
Automatic Memory Tuning
Two new parameters, WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET
WORKAREA_SIZE_POLICY = TRUE
PGA_AGGREGATE_TARGET > 0 (zero)
In previous releases, or when not using the new automatic PGA tuning ability, a DBA had to carefully tune the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE parameters to achieve optimal sort and join performance.
With automatic PGA tuning enabled, a process's needs shrink and grow, so does its PGA.
The recommended starting point for PGA_AGGREGATE_TARGET on an online transaction processing (OLTP) system is 16% of physical memory, and for DSS systems, it is 40% of physical memory.
Metalink Note 223730.1 suggests querying the V$SQL_WORKAREA_ACTIVE view to determine if any PGA work areas are undersized, resulting in writes to temporary segments.
SELECT
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM
V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
The goal is to have a cache hit ratio as close to 100% as possible, and to have zero processes overallocating their PGA.
Optimizer (CBO)
Some of the parameter values affect the decision of CBO. They should be change if necessary.
Default values during installation
- optimizer_index_caching=0
- optimizer_index_cost_adj=100
optimizer_index_caching=0 means “you don’t normally have any index blocks cached in RAM”(percent-value) . It should be around : 80-90
optimizer_index_cost_adj=100 means “index-access is just as expensive as full table scans” It should be about: 20-30 (i.e. cost is 1/5 or so)
Use GATHER_SCHEMA_STATS instead of Analyze Table
GATHER_SCHEMA_STATS( ownname=>’GEO’, cascade=>TRUE, method_opt=>’FOR ALL INDEXED COLUMNS SIZE AUTO’);
- cascade : analyzes indexes,
- method_opt : controls histogram generation,
