[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1413
  • Last Modified:

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?
0
xoxomos
Asked:
xoxomos
  • 2
1 Solution
 
ysdCommented:
Increase of multipasses can really affect the db's performance. To help you tune the initialization parameter PGA_AGGREGATE_TARGET, Oracle
provides two PGA advice performance views:
n V$PGA_TARGET_ADVICE
n V$PGA_TARGET_ADVICE_HISTOGRAM
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.ltered.
0
 
ysdCommented:
Increase of multipasses can really affect the db's performance. To help you tune the initialization parameter PGA_AGGREGATE_TARGET, Oracle
provides two PGA advice performance views:
n V$PGA_TARGET_ADVICE
n V$PGA_TARGET_ADVICE_HISTOGRAM
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.ltered.
0
 
xoxomosAuthor Commented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now