xoxomos
asked on
Multi pass executions
I'm looking at PGA Memory Usage Details in OEM. It shows under executions up to 1mb almost all Optimal executions. By the time it gets to the 32mb - 64 mb, most are multipass executions with some one pass executions. I've got a PGA aggregate target of 1536. Maximum PGA Allocated has been 731. Should the multipass executions be of concern and if so, what should be done?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was looking at the chart all wrong.
I've got the pga target set to 1.5G, but looking as the
'Show memory usage details for PGA target 192 mb' and seeing the multipass executions.
When i changed it to show memory usage details for PGA target 384 the chart showed all optimal.
I've got the pga target set to 1.5G, but looking as the
'Show memory usage details for PGA target 192 mb' and seeing the multipass executions.
When i changed it to show memory usage details for PGA target 384 the chart showed all optimal.
provides two PGA advice performance views:
n V$PGA_TARGET_ADVICE
n V$PGA_TARGET_ADVICE_HISTOG
By examining these two views, you no longer need to use an empirical approach to
tune the value of PGA_AGGREGATE_TARGET. Instead, you can use the content of
these views to determine how key PGA statistics will be impacted if you change the
value of PGA_AGGREGATE_TARGET.
In both views, values of PGA_AGGREGATE_TARGET used for the prediction are
derived from fractions and multiples of the current value of that parameter, to
assess possible higher and lower values. Values used for the prediction range from
10 MB to a maximum of 256 GB.
Oracle generates PGA advice performance views by recording the workload history
and then simulating this history for different values of PGA_AGGREGATE_TARGET.
The simulation process happens in the background and continuously updates the
workload history to produce the simulation result. You can view the result at any
time by querying V$PGA_TARGET_ADVICE or V$PGA_TARGET_ADVICE_
HISTOGRAM.
To enable automatic generation of PGA advice performance views, make sure the
following parameters are set:
n PGA_AGGREGATE_TARGET, to enable automatic PGA memory management. Set
the initial value as described in "Setting PGA_AGGREGATE_TARGET Initially"
on page 14-50.
n 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 aV$PGA_TARGET_ADVICE This view predicts how the statistics cache hit
percentage and over allocation count in V$PGASTAT will be impacted if
you change the value of the initialization parameter PGA_AGGREGATE_TARGET.ltere