Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


Being Too Clever For Your Own Good - Part 2
September 23
, 2005
Mark Rittman

The server we were using had 12 SPARC processors, and all the tables and indexes were defined using PARALLEL = DEFAULT, so they should pick up the default degree of parallelism for the instance, which should be 12 (processors) x 2 (parallel threads per CPU) = 24. So what was happening when we ran our query? I ran it again, but in a separate session queried V$PX_PROCESS to see how many slaves were running:
SQL> SELECT * FROM v$px_process;

SERV STATUS           PID SPID                SID    SERIAL#
---- --------- ---------- ------------ ---------- ----------
P023 IN USE            52 11852               318        441
P012 IN USE            38 11830               327       7723
P000 IN USE            24 10078               348       2064
P004 IN USE            29 10086               357       1433
P006 IN USE            31 10090               366       2312
P007 IN USE            32 10092               367        431
P018 IN USE            44 11842               371       1366
P003 IN USE            28 10084               372        292
P008 IN USE            33 10095               373        453
P014 IN USE            40 11834               374        500
P001 IN USE            26 10080               381        348
P011 IN USE            36 10111               401       1170
P017 IN USE            43 11840               414       1002
P020 IN USE            46 11846               416      21671
P002 IN USE            27 10082               445       7451
P019 IN USE            45 11844               465        344
P016 IN USE            42 11838               474        626
P009 IN USE            34 10097               489        723
P010 IN USE            35 10106               503        254
P013 IN USE            39 11832               504        265
P015 IN USE            41 11836               505       1848
P021 IN USE            47 11848               512       6012
P005 IN USE            30 10088               519        170
P022 IN USE            49 11850               546       1334

24 rows selected.

which was what I was expecting given the above settings; however running the query at this degree of parallelism was causing the statement to fail, running out of temp space. So what if we reduced the DOP, would that have any effect?

SQL> drop table lookup_table;

Table dropped.

Elapsed: 00:00:00.04
SQL> CREATE TABLE lookup_table parallel (degree 24)
  2  AS
  3  SELECT
  4      destn,
  5   studref,
  6      min(pi) pi
  7  FROM (
  8  SELECT
  9       pct1.DES       as container_type
 10      ,pc1.ID                 as pi
 11      ,pc1.SPN as pn
 12      ,pct2.DES       as Desc2
 13      ,pc2.DES        as studref
 14      ,pct3.des       as desc3
 15      ,pc3.dest_code        as destn
 16  FROM
 17       container pc1
 18      ,element  e1
 19      ,container_type pct1
 20      ,element  e2
 21      ,container pc2
 22      ,container_type pct2
 23      ,element  e3
 24      ,container pc3
 25      ,container_type pct3
 26  WHERE
 27      pc1.CONTAINER_TYPE_ID = 3
 28  AND e1.CONTAINER_ID       = pc1.id
 29  AND pct1.ID                       = pc1.CONTAINER_TYPE_ID
 30  AND e2.id                         = e1.PARENT_ID
 31  AND pc2.ID                        = e2.CONTAINER_ID
 32  AND pct2.id                       = pc2.CONTAINER_TYPE_ID
 33  AND e3.id                         = e2.PARENT_ID
 34  AND pc3.ID                        = e3.CONTAINER_ID
 35  AND pct3.id                       = pc3.CONTAINER_TYPE_ID
 36  ) pages
 37  GROUP BY destn, studref
 38  ;
CREATE TABLE lookup_table parallel (degree 24)
*
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

What was interesting here was that when I queried V$PX_PROCESS in a different session whilst this statement was running, 48 slaves were running. Doug Burns explains why this is in his Parallel Query paper - basically Oracle will actually kick off sets of PX slaves for a given action, one as a producer of rows, and one as a consumer, which led to 48 PX slaves being listed in V$PX_PROCESS, and the query still ran out of temp space. So what if we ran it again, with a DOP of 12?

SQL> drop table lookup_table;

SQL> CREATE TABLE lookup_table parallel (degree 12)
  2  AS
  3  SELECT
  4      destn,
  5   studref,
  6      min(pi) pi
  7  FROM (
  8  SELECT
  9       pct1.DES       as container_type
 10      ,pc1.ID                 as pi
 11      ,pc1.SPN as pn
 12      ,pct2.DES       as Desc2
 13      ,pc2.DES        as studref
 14      ,pct3.des       as desc3
 15      ,pc3.dest_code        as destn
 16  FROM
 17       container pc1
 18      ,element  e1
 19      ,container_type pct1
 20      ,element  e2
 21      ,container pc2
 22      ,container_type pct2
 23      ,element  e3
 24      ,container pc3
 25      ,container_type pct3
 26  WHERE
 27      pc1.CONTAINER_TYPE_ID = 3
 28  AND e1.CONTAINER_ID       = pc1.id
 29  AND pct1.ID                       = pc1.CONTAINER_TYPE_ID
 30  AND e2.id                         = e1.PARENT_ID
 31  AND pc2.ID                        = e2.CONTAINER_ID
 32  AND pct2.id                       = pc2.CONTAINER_TYPE_ID
 33  AND e3.id                         = e2.PARENT_ID
 34  AND pc3.ID                        = e3.CONTAINER_ID
 35  AND pct3.id                       = pc3.CONTAINER_TYPE_ID
 36  ) pages
 37  GROUP BY destn, studref
 38  ;

Table created.

Elapsed: 00:05:51.07

and this time it is created without any problem. So now that we know that a DOP of 12, and consequently 24 PX slaves, is OK, how about taking the DEGREE clause of of the CREATE TABLE statement and instead limiting down the PARALLEL_MAX_SERVERS parameter to "throttle" it to a maximum of 24. Would that work?

SQL> conn sys/password@orcl as sysdba
Connected.
SQL> alter system set parallel_max_servers = 24 scope=memory;

System altered.

Elapsed: 00:00:00.00
SQL> conn STAGING/STAGING@orcl
Connected.

SQL> CREATE TABLE lookup_table
  2  AS
  3  SELECT
  4      destn,
  5   studref,
  6      min(pi) pi
  7  fROM (
  8  SELECT
  9       pct1.DES       as container_type
 10      ,pc1.ID                 as pi
 11      ,pc1.SPN as pn
 12      ,pct2.DES       as Desc2
 13      ,pc2.DES        as studref
 14      ,pct3.des       as desc3
 15      ,pc3.dest_code        as destn
 16  FROM
 17       container pc1
 18      ,element  e1
 19      ,container_type pct1
 20      ,element  e2
 21      ,container pc2
 22      ,container_type pct2
 23      ,element  e3
 24      ,container pc3
 25      ,container_type pct3
 26  WHERE
 27      pc1.CONTAINER_TYPE_ID = 3
 28  AND e1.CONTAINER_ID       = pc1.id
 29  AND pct1.ID                       = pc1.CONTAINER_TYPE_ID
 30  AND e2.id                         = e1.PARENT_ID
 31  AND pc2.ID                        = e2.CONTAINER_ID
 32  AND pct2.id                       = pc2.CONTAINER_TYPE_ID
 33  AND e3.id                         = e2.PARENT_ID
 34  AND pc3.ID                        = e3.CONTAINER_ID
 35  AND pct3.id                       = pc3.CONTAINER_TYPE_ID
 36  ) pages
 37  GROUP BY destn, studref
 38  ;

Table created.

Elapsed: 00:08:34.07

OK, that's good, although the query time has gone up - this could just be down to load on the server though. The principle is fine, by setting PARALLEL_MAX_SERVERS to a lower amount, we can limit the amount of parallelism taking place. How about going the whole hog and turning of PQ completely? How would that affect it?

SQL> conn sys/password@orcl as sysdba
Connected.
SQL> alter system set parallel_max_servers = 0 scope=memory;

System altered.

Elapsed: 00:00:00.00
SQL> conn STAGING/STAGING@orcl
Connected.
SQL> drop table lookup_table;

Table dropped.

Elapsed: 00:00:00.01
SQL> CREATE TABLE lookup_table
  2  AS
  3  SELECT
  4      destn,
  5   studref,
  6      min(pi) pi
  7  fROM (
  8  SELECT
  9       pct1.DES       as container_type
 10      ,pc1.ID                 as pi
 11      ,pc1.SPN as pn
 12      ,pct2.DES       as Desc2
 13      ,pc2.DES        as studref
 14      ,pct3.des       as desc3
 15      ,pc3.dest_code        as destn
 16  FROM
 17       container pc1
 18      ,element  e1
 19      ,container_type pct1
 20      ,element  e2
 21      ,container pc2
 22      ,container_type pct2
 23      ,element  e3
 24      ,container pc3
 25      ,container_type pct3
 26  WHERE
 27      pc1.CONTAINER_TYPE_ID = 3
 28  AND e1.CONTAINER_ID       = pc1.id
 29  AND pct1.ID                       = pc1.CONTAINER_TYPE_ID
 30  AND e2.id                         = e1.PARENT_ID
 31  AND pc2.ID                        = e2.CONTAINER_ID
 32  AND pct2.id                       = pc2.CONTAINER_TYPE_ID
 33  AND e3.id                         = e2.PARENT_ID
 34  AND pc3.ID                        = e3.CONTAINER_ID
 35  AND pct3.id                       = pc3.CONTAINER_TYPE_ID
 36  ) pages
 37  GROUP BY destn, studref
 38  ;

Table created.

Elapsed: 00:04:31.08

So Parallel Query's the culprit then, eh? If you turn it down a notch, the query now runs within the TEMP space, and if you turn it off completely, it actually runs faster than if it's enabled. Er... well no actually, there's one more twist to it.

Whilst we were performing heroics with PARALLEL_MAX_SERVERS and explain plans, one of the other DBAs noticed that the CONTAINER_TYPE table was redundant in the SQL statement - it was included in the join, but the columns it provided could be sourced entirely from the other two tables. CONTAINER_TYPE could therefore be removed from the join, potentially reducing the amount of memory and temp space taken up by the hash join. The SQL was rewritten to remove this table:

CREATE TABLE lookup_table
AS
SELECT
     pc3.dest_code        as destn
    ,pc2.DES        as studref
    ,MIN(pc1.ID)            as pi
FROM
     container pc1
    ,element  e1
    ,element  e2
    ,container pc2
    ,element  e3
    ,container pc3
WHERE
    pc1.CONTAINER_TYPE_ID = 3
AND e1.CONTAINER_ID       = pc1.id
AND e2.id                         = e1.PARENT_ID
AND pc2.ID                        = e2.CONTAINER_ID
AND e3.id                         = e2.PARENT_ID
AND pc3.ID                        = e3.CONTAINER_ID
GROUP BY pc3.dest_code, pc2.des
;

and then run again - with an successful execution and a run time of just 1 min 17 seconds. Which was a bit of improvement. This of course was with PARALLEL_MAX_SERVERS set to 0, so what would happen if it was increased back to the original value, 240?

SQL> conn sys/password@orcl as sysdba
Connected.
SQL> alter system set parallel_max_servers = 240 scope=memory;

System altered.

Elapsed: 00:00:00.02
SQL> conn STAGING/STAGING@orcl
Connected.
SQL> CREATE TABLE lookup_table
  2  AS
  3  SELECT
  4       pc3.dest_code        as destn
  5      ,pc2.DES        as studref
  6      ,MIN(pc1.ID)            as pi
  7  FROM
  8       container pc1
  9      ,element  e1
 10      ,element  e2
 11      ,container pc2
 12      ,element  e3
 13      ,container pc3
 14  WHERE
 15      pc1.CONTAINER_TYPE_ID = 3
 16  AND e1.CONTAINER_ID       = pc1.id
 17  AND e2.id                         = e1.PARENT_ID
 18  AND pc2.ID                        = e2.CONTAINER_ID
 19  AND e3.id                         = e2.PARENT_ID
 20  AND pc3.ID                        = e3.CONTAINER_ID
 21  GROUP BY pc3.dest_code, pc2.des
 22  ;

Table created.

Elapsed: 00:01:12.04
SQL>

And there you go - it runs within the temp space constraints, and runs (just about) faster than with PARALLEL_MAX_SERVERS set to 0.

So, the moral of the story? Well, I guess if you're having issues with temp space, and you're hash joining several (big) tables with parallel query enabled, and you've got lots of processors, consider limiting down the degree of parallelism as a quick fix way to limit the amount of temp space being used. Alternatively, consider increasing HASH_AREA_SIZE if you've got memory spare. However, and I guess this is the true moral of the story - before you get all fancy and start playing around with initialization parameters, DOPs and so forth, don't ignore the little guy over in the corner, waving his hand and saying "perhaps this statement could be written better", as probably the biggest improvement you'll ever get is taking the time out to write the query more efficiently in the first place, rather than rely on clever quick fixes to paper over the problem.

Any comments, aspects I've missed or misinterpreted etc, let me know and I'll update as necessary.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational