Link huge PGA or TEMP consumption to sql_id over a period of time

Imagine you discovered that during a particular period of time a huge amount of PGA or TEMP space has been consumed by your database.

Then you want to know, if you could link this behavior to one or more sql_id.

Ok, I am a little bit late :-) but you should have noticed that since 11.2.0.1  two useful columns have been added to the v$active_session_history and dba_hist_active_sess_history views:

  • PGA_ALLOCATED:  Amount of PGA memory (in bytes) consumed by this session at the time this sample was taken
  • TEMP_SPACE_ALLOCATED: Amount of TEMP memory (in bytes) consumed by this session at the time this sample was taken

Interesting, but is it helpful to answer:

  • What are the top sql_id linked to pga consumption during a particular period of time ?
  • What are the top sql_id linked to temp space consumption during a particular period of time ?

Coskan Gundogar gave a nice example related to a temp space issue, for a particular session into this blog post.

With this blog post, I just want to provide a way to generalize the computation at the instance level instead of the session one.

So, to find the top sql_id(s) responsible of  PGA or TEMP space consumption during a particular period of time, I use:

For the PGA consumption:

SQL> !cat ash_sql_id_pga.sql
col percent head '%' for 99990.99
col star for A10 head ''

accept seconds prompt "Last Seconds [60] : " default 60;
accept top prompt "Top  Rows    [10] : " default 10;

select SQL_ID,round(PGA_MB,1) PGA_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_PGA_MB) PGA_MB ,(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,rank() over(order by sum(DELTA_PGA_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,PGA_ALLOCATED,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
from
v$active_session_history
where
IS_SQLID_CURRENT='Y'
and sample_time > sysdate-&seconds/86400
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < (&top+1)
order by rank
/

The output is like:

SQL> @ash_sql_id_pga.sql
Last Seconds [60] : 3600
Top  Rows    [10] : 10
old  13: and sample_time > sysdate-&seconds/86400
new  13: and sample_time > sysdate-3600/86400
old  20: where rank < (&top+1)
new  20: where rank < (10+1)

SQL_ID            PGA_MB         %
------------- ---------- --------- ----------
4nyd6q26dzvb2     2211.6     55.75 *****
3s5d6gj84kban      309.8      7.81
8pfzqzrsvjj38        171      4.31
2bjfxk6vqc0ft      134.1      3.38
fxr8wdgq9bmsv      115.4      2.91
4r23u15d4c9rh      108.4      2.73
                    94.3      2.38
g02u5ztkuv2sz       43.5      1.10
ddr8uck5s5kp3       23.8      0.60
10pty85f37hrb       20.5      0.52

10 rows selected.

For the TEMP consumption:

SQL> !cat ash_sql_id_temp.sql
col percent head '%' for 99990.99
col star for A10 head ''

accept seconds prompt "Last Seconds [60] : " default 60;
accept top prompt "Top  Rows    [10] : " default 10;

select SQL_ID,TEMP_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_TEMP_MB) TEMP_MB ,(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,temp_space_allocated,
greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from
v$active_session_history
where
IS_SQLID_CURRENT='Y'
and sample_time > sysdate-&seconds/86400
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < (&top+1)
order by rank
/

The output is like:

SQL> @ash_sql_id_temp.sql
Last Seconds [60] : 3600
Top  Rows    [10] : 
old  13: and sample_time > sysdate-&seconds/86400
new  13: and sample_time > sysdate-3600/86400
old  19: where rank < (&top+1)
new  19: where rank < (10+1)

SQL_ID           TEMP_MB         %
------------- ---------- --------- ----------
by714720ajxwk          2     50.00 *****
c2jdkwzndq685          1     25.00 **
5h7w8ykwtb2xt          1     25.00 **

Basically:

  • The SQL computes, for each session, the PGA or TEMP space allocated between two active session history samples  thanks to “over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding)“.
  • Those computed values are linked to the “active” sql_id observed during the sampling.
  • Then, it sums per sql_id those computed values and display the top sql_id(s).

So, we are now able to know which sql_id are responsible of huge PGA or TEMP consumption during a certain period of time.

Important remarks:

  1. You can also query the dba_hist_active_sess_history view but bear in mind that it is no so accurate as only a subset of the rows coming from v$active_session_history are flushed into the dba_hist_active_sess_history view.
  2. Those SQL work as of 11.2.0.1.
  3. You need to purchase the Diagnostic Pack in order to be allowed to query the “v$active_session_history” view.
  4. Those queries are useful to diagnose “huge” PGA or TEMP consumption, they are not so helpful to find out which sql_id used exactly how much PGA or TEMP  (As it may used already pre-allocated PGA or TEMP space and did not need over allocation: See the columns definition in the beginning of the post)

UPDATE: You can drill down in details per sql_id execution into this blog post

About these ads
This entry was posted in Perl Scripts. Bookmark the permalink.

One Response to Link huge PGA or TEMP consumption to sql_id over a period of time

  1. collierjw says:

    These are beautiful queries!

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