What is going on PGA /Work Area?


— This script shows how many times work area are executed in
— optimal pass, one pass and mutiple pass.
— Script Name: Work_Area_Execution_Mode

Column optimal_count format 9999999999
Column Optpass_pct format 999
Column Onepass_count format 9999999999
Column Onepass_pct format 999
Column multipass_count format 9999999999
Column Mltpass_pct format 999

Spool Work_Area_Execution_Mode.lst
Select        optimal_count, round(optimal_count * 100 / total,2) Optpass_pct, onepass_count , round(onepass_count * 100 / total,2) Onepass_pct,
multipass_count , round(multipass_count * 100 / total,2) Mltpass_pct
from        (
select        DECODE (SUM(total_executions), 0, 1, SUM(total_executions)) total, SUM(optimal_executions) optimal_count,
SUM(onepass_executions) nepass_count, SUM(multipasses_executions) multipass_count
from         v$sql_workarea_histogram
where        low_optimal_size <= 8*1024*1024
-- As if in my case the pga_aggregate target was 8 Gigs
);

spool Off

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s