I was with a client the other week
and was asked to look at a particular SQL statement that kept
failing due to lack of TEMP space. It looked something like this
(names changed to protect the innocent, etc.)
CREATE TABLE lookup_table
AS
SELECT
destn,
studref,
min(pi) pi
fROM (
SELECT
pct1.DES as container_type
,pc1.ID as pi
,pc1.SPN as pn
,pct2.DES as Desc2
,pc2.DES as studref
,pct3.des as desc3
,pc3.dest_code as destn
FROM
container pc1
,element e1
,container_type pct1
,element e2
,container pc2
,container_type pct2
,element e3
,container pc3
,container_type pct3
WHERE
pc1.CONTAINER_TYPE_ID = 3
AND e1.CONTAINER_ID = pc1.id
AND pct1.ID = pc1.CONTAINER_TYPE_ID
AND e2.id = e1.PARENT_ID
AND pc2.ID = e2.CONTAINER_ID
AND pct2.id = pc2.CONTAINER_TYPE_ID
AND e3.id = e2.PARENT_ID
AND pc3.ID = e3.CONTAINER_ID
AND pct3.id = pc3.CONTAINER_TYPE_ID
) pages
GROUP BY destn, studref
;
which when executed gave the following error:
CREATE TABLE lookup_table
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P030
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Now of course my first reaction was "get the TEMP tablespace
extended" but it had already just been extended to 8GB (made up
of 4 tempfiles). So what could be causing the issue?
The first thing I did was to run an explain plan on the
query, as my suspicion was that the joins were perhaps going to
be hash joins, which can make heavy use of the TEMP tablespace
if the hash can't be built in memory (defined by the
HASH_AREA_SIZE parameter). The explain plan looked like this:
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 120 | 406 | | | |
| 1 | LOAD AS SELECT | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 120 | 406 | Q1,04 | P->S | QC (RAND)
| 4 | SORT GROUP BY | | 1 | 120 | 406 | Q1,04 | PCWP | |
| 5 | PX RECEIVE | | 1 | 120 | 405 | Q1,04 | PCWP | |
| 6 | PX SEND HASH | :TQ10003 | 1 | 120 | 405 | Q1,03 | P->P | HASH |
| 7 | NESTED LOOPS | | 1 | 120 | 405 | Q1,03 | PCWP | |
| 8 | HASH JOIN | | 1 | 112 | 405 | Q1,03 | PCWP | |
| 9 | PX RECEIVE | | 1 | 100 | 305 | Q1,03 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10002 | 1 | 100 | 305 | Q1,02 | P->P | BROADCAST |
| 11 | NESTED LOOPS | | 1 | 100 | 305 | Q1,02 | PCWP | |
| 12 | NESTED LOOPS | | 1 | 87 | 305 | Q1,02 | PCWP | |
| 13 | NESTED LOOPS | | 1 | 77 | 305 | Q1,02 | PCWP | |
| 14 | HASH JOIN | | 1 | 65 | 305 | Q1,02 | PCWP | |
| 15 | PX RECEIVE | | 1 | 47 | 206 | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10001 | 1 | 47 | 206 | Q1,01 | P->P | BROADCAST |
| 17 | HASH JOIN | | 1 | 47 | 206 | Q1,01 | PCWP | |
| 18 | BUFFER SORT | | | | | Q1,01 | PCWC | |
| 19 | PX RECEIVE | | 1 | 26 | 0 | Q1,01 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10000 | 1 | 26 | 0 | | S->P | BROADCAST |
| 21 | NESTED LOOPS | | 1 | 26 | 0 | | | |
| 22 | INDEX UNIQUE SCAN | PK_CONTAINER_TYPE | 1 | 13 | 0 | | |
| 23 | INDEX FULL SCAN | PK_CONTAINER_TYPE | 1 | 13 | | | |
| 24 | PX BLOCK ITERATOR | | 2708K| 54M| 202 | Q1,01 | PCWC | |
| 25 | TABLE ACCESS FULL | CONTAINER | 2708K| 54M| 202 | Q1,01 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 2340K| 40M| 96 | Q1,02 | PCWC | |
| 27 | TABLE ACCESS FULL | ELEMENT | 2340K| 40M| 96 | Q1,02 | PCWP | |
| 28 | TABLE ACCESS BY INDEX ROWID| ELEMENT | 1 | 12 | 2 | Q1,02 | PCW
| 29 | INDEX UNIQUE SCAN | PK_ELEMENT | 1 | | 1 | Q1,02 | PCWP |
| 30 | TABLE ACCESS BY INDEX ROWID | CONTAINER | 1 | 10 | 2 | Q1,02 | PC
| 31 | INDEX UNIQUE SCAN | PK_CONTAINER | 1 | | 1 | Q1,02 | PCWP |
| 32 | INDEX UNIQUE SCAN | PK_CONTAINER_TYPE | 1 | 13 | 0 | Q1,02 | PCWP |
| 33 | PX BLOCK ITERATOR | | 2340K| 26M| 96 | Q1,03 | PCWC | |
| 34 | TABLE ACCESS FULL | ELEMENT | 2340K| 26M| 96 | Q1,03 | PCWP | |
| 35 | TABLE ACCESS BY INDEX ROWID | CONTAINER | 1 | 8 | 2 | Q1,03 | PCWP |
| 36 | INDEX UNIQUE SCAN | PK_CONTAINER | 1 | | 1 | Q1,03 | PCWP | |
----------------------------------------------------------------------------------------------------
A couple of things jumped out at me: firstly, parallel query
is being used (spot the PX RECEIVE, PX SEND BROADCAST and PX
BLOCK ITERATOR operations), and secondly, there's lots of hash
joins going on (three to be precise, which matches the number of
tables in the query). So, a couple more bits of information to
find out : roughly how big are the tables that we're joining
(the smallest of which will determine the size of hash table
built) and how big is the HASH_AREA_SIZE?
SQL> show parameter hash_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size integer 131072
SQL> select bytes from dba_segments where owner='STAGING' and segment_name = 'CONTAINER';
BYTES
----------
318767104
SQL> select bytes from dba_segments where owner='STAGING' and segment_name = 'ELEMENT';
BYTES
----------
150994944
SQL> select bytes from dba_segments where owner='STAGING' and segment_name = 'CONTAINER_TYPE';
BYTES
----------
65536
So, from looking at these figures, the HASH_AREA_SIZE isn't
too big, but the smallest table (CONTAINER_TYPE) isn't that big
anyway, and according to this article, you only need about 1.6 x
the size of the smallest (driving) table available in the hash
area.
From speaking to a couple of people, the main finger of
suspicion was pointing towards parallel query. The consensus was
that parallel query can multiply the actual amount of hash joins
going on by the number of parallel slaves being used, and this
could well end up taking more memory than if a single hash table
was built. Looking at the initialization parameters, parallel
query was enabled and with quite a high PARALLEL_MAX_SERVERS:
SQL> show parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 240
SQL> show parameter parallel_threads_per_cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
and include the URL for the page.
