Sum of temp used from ASH view

I had a situation where I wanted to compare the temp space used by two similar insert statements on a test system.  One was filling up the temporary tablespace and the other wasn’t.

select sql_id,sample_time,
sum(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
and sql_id in ('99m08n4dv67h9','8av0z21tm15as')
group by sql_id,sample_time
order by sql_id,sample_time;

SQL_ID        SAMPLE_TIME                      GIG
------------- ------------------------- ----------
8av0z21tm15as 21-SEP-12 10.20.49.279 AM 35.8271484
8av0z21tm15as 21-SEP-12 10.20.59.309 AM 44.6728516
8av0z21tm15as 21-SEP-12 10.21.09.329 AM 142.089844
8av0z21tm15as 21-SEP-12 10.21.19.379 AM 133.241211
8av0z21tm15as 21-SEP-12 10.21.29.389 AM 142.089844
8av0z21tm15as 21-SEP-12 10.21.39.399 AM 142.089844
99m08n4dv67h9 21-SEP-12 12.32.50.790 PM 43.2587891
99m08n4dv67h9 21-SEP-12 12.33.00.810 PM 88.3574219
99m08n4dv67h9 21-SEP-12 12.33.10.820 PM 100.229492
99m08n4dv67h9 21-SEP-12 12.33.20.840 PM 111.904297
99m08n4dv67h9 21-SEP-12 12.33.30.860 PM 147.005859

I used a query that I had posted about earlier where you can see how much temp space a query is using by querying the ASH.  But, in this case the two queries were parallel inserts so I summed the temp space used across all the sessions running the given sql_id.  In this case I knew there was only one running at a time.  If multiple were running I would have summed the temp space used by all the inserts with the same sql_id.  The dev team reported that the first sql_id wasn’t using any temp and the second was filling it up, but this query showed that they both were using more than 140 gig of temp at their peak.

- Bobby

P.S.  In retrospect I should have left off the TEMP_SPACE_ALLOCATED condition but at the peak all the parallel query servers probably had more than 1 gig of temp.  This was built from the previous query that only showed sessions with temp usage over 1 gig.  In this example I really wanted all the sessions for the given sql_ids even if they had less than 1 gig temp.  But, I’m leaving the query as it is because it was what I really ran and it was helpful.

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply