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?
Solution: Multi pass executions
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.