home

all information is from:

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02

 

9ituning  

This mechanism cannot be used for shared server connections.

 

setting pga_aggregate_target_advice

PGA_AGGREGATE_TARGET, to enable automatic PGA memory management. Set the initial value as described in "Setting PGA_AGGREGATE_TARGET Initially".

STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting this parameter to BASIC turns off generation of PGA performance advice views.

The content of these PGA advice performance views is reset at instance start-up or when PGA_AGGREGATE_TARGET is altered.

 

Setting PGA_AGGREGATE_TARGET Initially

For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

total_mem is the total amount of physical memory available on the system

 

This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager
V$PROCESS
 This view has one row for each Oracle process connected to the instance.
V$SQL_WORKAREA_HISTOGRAM
 This view shows the number of work areas executed with optimal memory size, one-pass memory size, and multi-pass memory size since instance start-up
   
   

 

non empty buckets 
SELECT LOW_OPTIMAL_SIZE/1024 low_kb
,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb
,OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS
,MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

 

V$sql_workarea 
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;