Oracle 11g & 12c: tunning the sort and hash operations

by Kamil Stawiarski

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.

Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
+48 508 943 051
+48 661 966 009

Newsletter Sign up to be updated