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:

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.

Those SQL work as of 11.2.0.1.

You need to purchase the Diagnostic Pack in order to be allowed to query the “v$active_session_history” view.

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