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 18.104.22.1680 PM 43.2587891 99m08n4dv67h9 21-SEP-12 12.33.00.810 PM 88.3574219 99m08n4dv67h9 21-SEP-12 22.214.171.1240 PM 100.229492 99m08n4dv67h9 21-SEP-12 126.96.36.1990 PM 111.904297 99m08n4dv67h9 21-SEP-12 188.8.131.520 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.
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.