Hash and sort operations are perfomed in PGA.
Quoting after the documentation:
A sort operator uses a work area (the sort area) to perform the in-memory sorting of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.
If the work area is to small to perform a sort or a hash operation, the process will use the temporary tablespace. For example, let’s examine the following query:
with v_sales as ( select CUST_CITY, sum(amount_sold) from sales s, customers c where s.cust_id=c.cust_id group by cust_city ) select count(1) from v_sales
As we can see in V$SQL_WORKAREA, that hash operations does not fit into the PGA and temporary segments are being created.
The size of the maximum work area in the PGA is controlled by two hidden parameters: _smm_max_size and _pga_max_size.
At the beginning, the maximum area for hash and sort operations (for one process) is 100M. Those parameters are dynamic, so they will by adjusted to the size of the PGA_AGGREGATE_TARGET parameter.
The size of the hidden parameters has increased and there are no more temporary segments created. And the execution time decreased from 00:11:33.84 to 00:06:05.81
Unfortunately there is a little problem with the sort operations. Let’s check the following query:
with v_sales as
(
select CUST_CITY, CUST_FIRST_NAME, CUST_LAST_NAME, rank() over (partition by cust_city order by amount_sold desc) as rnk
from sales s, customers c, countries ct
where s.cust_id=c.cust_id
and c.country_id=ct.country_id
and ct.country_name in ('United Kingdom','Germany','Poland')
)
select count(1)
from v_sales
where rnk=5
After examining the work area, we can see the following result:
So apparently, although the maximum work area size is 1G, the sort operation used only 97.6 MB in PGA and has created almost 300M segment in the temporary tablespace
But let’s try a little trick – we will set the hidden parameters to the exact same values,that they have now… and restart the instance.
The restart is important – otherwise it doesn’t work 😛
And now – voilà!
So it looks like the automatic adjustment of the "_smm_max_size" and the "_pga_max_size" parameters works only for HASH operations. Sort area requires the manual setting of those parameters.