Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

OPTIMAL PASS :-

In Oracle if sort,hash join or group by suddenly gain memory in pga auto target it is said optimal pass.

 

ONE PASS:-

If the memory acquisition requires a single pass through pga_aggregate_target, then memory allocation is marked

as one pass.

 

MULTI PASS:-

 If all memory is in use, Oracle may have to make multiple passes through pga_aggregate_target

to acquire the memory. Multipass executions indicate a memory

shortage, and you should always allocate enough PGA memory to ensure that at least 90-95 percent of

connected tasks can acquire their memory optimally.

 

WHEN TO INCREASE  PGA_AGGREGATE_TARGET:-

Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass

exceeds pga_aggregate_target, then you'll want to increase pga_aggregate_target.

Whenever the value of the v$sysstat statistic workarea executions-multipass is

 greater than 1 percent, the database may benefit from additional PGA memory.

 

To evaluate  this:-

 consider the following query:

 

select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3

from

(

select name,value count,(sum(value) over ()) total

from

v$sysstat

where

name like 'workarea ex%'

);