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.
