This discussion is archived
7 Replies Latest reply: Jan 30, 2013 7:02 AM by garywicke RSS

Effects of PARALLEL hint in different parts of SQL script

garywicke Newbie
Currently Being Moderated
I have a fairly large data warehouse with most of the Child tables having more than 5 billion rows. They are partitioned by a DATE column.

I have several local B-tree and bitmap indexes on the appropriate columns.

The DEGREE on most of the large tables is currently set to 4.

AUTO_DOP is not currently being used.

The DW runs on a 64 processor server with 64GB memory and 16k block size.

Database is Oracle 11.2.0.2 EE on Solaris

I have several queries that extract data into separate reference tables that are totally refreshed on typically a monthly basis although a few are daily.

The queries use an INSERT /*+ APPEND */ and usually are a JOIN of 3-4 tables and/or in-line views with a GROUP BY on 1-2 columns.

I'm trying to find the best place to specify a PARALLEL hint or optionally force parallel DML in the session and set the degree via the 'ALTER SESSION ...' statement.

Here are the options I'm looking at: (others are greatly appreciated!)

1) use the /*+ PARALLEL x */ hint only on the topmost SELECT

2) specify the /*+ PARALLEL x */ hint on each separate SELECT in all the sub-queries and in-line views where it's deemed useful (i.e. not on very small tables).

3) use an 'ALTER SESSION FORCE PARALLEL DML PARALLEL x;'

Questions:

a) by using option (1) will the DOP in that topmost SELECT be used for all subsequent SELECT statements below it?

b) same question for using option (3)?

c) how can I monitor the activity and verify what DOP is used in the different query sections? I have tried to follow the script execution in TOAD but haven't had much luck.


Thanks very much for your help and please let me know if you need any more information.

-gary
  • 1. Re: Effects of PARALLEL hint in different parts of SQL script
    Martin Preiss Expert
    Currently Being Moderated
    Hi Gary,

    Randolf Geist has recently published two very interesting articles on parallel execution in the otn library:

    http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html
    http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-2-1872405.html

    Regards

    Martin
  • 2. Re: Effects of PARALLEL hint in different parts of SQL script
    garywicke Newbie
    Currently Being Moderated
    Hi Martin

    Thanks very much for the article references. I'm looking at them now.

    I appreciate the help!

    -gary
  • 3. Re: Effects of PARALLEL hint in different parts of SQL script
    garywicke Newbie
    Currently Being Moderated
    Well I know its been a while since I read the above articles but I'm not sure I have my answer yet.

    The articles are very good and quite detailed. Not sure I understood all of it, but very educational.

    My basic question has boiled down to this:

    If I have an outer INSERT with a 'parallel (8)' hint and several subqueries in the body to put the data together, each having a 'parallel (24)' hint, does the query run with a DOP of 8 or does each section run with the DOP I've hinted to it?

    From looking at TOAD, it appears the SQL is running with a DOP of 8 which is NOT what I want, obviously.

    I have done a bunch of reading and searching but haven't found the right article to tell me why and more importantly how I can make each section of the query use the DOP I'd like it to.

    Mr. Geist talks about a query having multiple DFOs (Data Flow Operations) and how each can have separate and different DOPs but wasn't clear, in my mind anyway, how to manage it. It can be tracked using the V$PX_SESSION view he mentions.

    I continue to test alternatives.

    Any further readings or suggestions are most welcome!!

    -gary
    Thanks very much.
  • 4. Re: Effects of PARALLEL hint in different parts of SQL script
    riedelme Expert
    Currently Being Moderated
    garywicke wrote:
    If I have an outer INSERT with a 'parallel (8)' hint and several subqueries in the body to put the data together, each having a 'parallel (24)' hint, does the query run with a DOP of 8 or does each section run with the DOP I've hinted to it?
    Each section of the SQL should have its own parallel hint. If the tables have a specified DOP queries should be executed in parallel automatically but only if the optimizer determines that parallelism is more efficient. If you want the INSERT to use parallelism you will have to turn parallel DML on which is a different setting. In general each subquery should have its own parallel hint if the parallelism is not occuring the way you want it to.

    Good work and keep testing!
  • 5. Re: Effects of PARALLEL hint in different parts of SQL script
    garywicke Newbie
    Currently Being Moderated
    Thanks for the feedback! Very useful!

    A couple of follow-ups.

    My SQL statement structure is basically like this:
    INSERT /*+ APPEND  PARALLEL (8) */  INTO <table 1> (COL1, COL2, COL3)
    SELECT /*+ PARALLEL (24) */ COL1, COL2, COL3
    FROM <table 2>
    WHERE ...
    GROUP BY ...
    After reading about 'ALTER SESSION ENABLE PARALLEL DML', I can see how to set it but I can't see how to verify it's current state (enabled or disabled). I looked at several system parameters but it wasn't obvious to me. I did see some PARALLEL_MAX_SERVERS (100) and PARALLEL_MIN_SERVERS (0) but didn't know if that applied to any and all SQL statements, including DML which is what I'm doing.

    From my testing above, without explicitly enabling parallel DML, it appeared the entire statement was using only 8 parallel sessions.

    Is the SQL set up properly to use the different degrees of parallelism for the different sections of the SQL statement?

    The documentation says:
    For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement.
    This is referring to /*+ parallel (integer) */

    But it doesn't say anything about additional parallel hints in the statement. Should the next parallel hint override the previous one and change how the data is being gathered? In my case would it or should it be using 24 sessions to organize the data and then 8 sessions to do the INSERT?

    I'm still reading through the chapter on parallel processing in the VLDB Guide.

    Thanks again for all your help and suggestions!!

    -gary
  • 6. Re: Effects of PARALLEL hint in different parts of SQL script
    riedelme Expert
    Currently Being Moderated
    garywicke wrote:
    Thanks for the feedback! Very useful!

    A couple of follow-ups.

    My SQL statement structure is basically like this:
    INSERT /*+ APPEND  PARALLEL (8) */  INTO <table 1> (COL1, COL2, COL3)
    SELECT /*+ PARALLEL (24) */ COL1, COL2, COL3
    FROM <table 2>
    WHERE ...
    GROUP BY ...
    I just checked the docs. The syntax for the parallel hint listed for 10gR1 (could have changed for 11gR2 - go ahead and see for yourself)
    parallel(table_spec[degree]
    expecting a table designation and an optional degree. If your syntax is unrecognized the hint will be ignored

    It is possible the syntax you are using is valid, being listed elsewhere than the hint definitions. Some hints aren't listed where hints are defined but in the data warehousing guide and so are semi-documented. I have not seen that usage myself.

    >
    After reading about 'ALTER SESSION ENABLE PARALLEL DML', I can see how to set it but I can't see how to verify it's current state (enabled or disabled). I looked at several system parameters but it wasn't obvious to me. I did see some PARALLEL_MAX_SERVERS (100) and PARALLEL_MIN_SERVERS (0) but didn't know if that applied to any and all SQL statements, including DML which is what I'm doing.
    I don't know how to tell if parallel DML is enabled with ALTER SESSION ENABLE PARALLEL DML except by turning it on. using SHOW PARAMETERS PARALLEL in SQL*PLUS did not tell me anything when I just tried it. You should be able to turn it on and get an indication from an execution plan that it is being used.
  • 7. Re: Effects of PARALLEL hint in different parts of SQL script
    garywicke Newbie
    Currently Being Moderated
    riedelme

    Thanks very much for your feedback.

    The syntax I'm using does invoke parallel sessions according to V$PX_SESSION and I believe the docs say that syntax will use that degree of parallelism for all subsequent statements. I guess if there's another hint later with a different degree it will change it to the new specification.

    I agree with you about enabling parallel DML so I will do as you suggest and just set it explicitly in the script.

    Thanks again for your help!!

    -gary

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points