Oracle Scratchpad

December 28, 2006

Parallel Execution

Filed under: Infrastructure,Parallel Execution,Partitioning,Performance — Jonathan Lewis @ 7:21 pm BST Dec 28,2006

Looking back at some of my previous posts I was reminded how easy it is to overlook one important feature when trying to comment on another.  In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment from David Aldridge jogged my memory. So here’s an important thought about parallel execution.

Partitioning.

Whatever you thought you knew about parallel execution, you need to review that knowledge in the light of partitioning. For example:

Parallel operations always do tablescans – No. Not if you have partitioned indexes. A parallel operation can run with each PX (parallel execution) session doing an index range scan of a single partition of the index.

Parallel operations do direct path reads – No. Consider the previous comment. A parallel operation can do index range scans – these are not done with direct path reads. (In fact a parallel nested loop join with an index on the second table will do cached reads on the index and second table – without a shred of partitioning in sight. The direct path reads will be restricted to the tablescan of the first table).

Finally, tablescans are not the only thing that do direct path reads. You can get them from parallel index fast full scans as well.

By the way, if you’re thinking that paralel queries “obviously” ought to do tablescans and hash joins, think again. Sometimes the performance problem with parallel execution comes from the huge volume of messaging that can take place – typified by wait event “PX Deq Credit: Send Blkd” on a parallel_to_parallel step; you may find that forcing a nested loop join makes a massive difference to the messaging, and makes the query run faster.

31 Comments »

  1. Jonathan,
    Is there any way to reliably determine whether the PX events for a process are normative or the cause of problems? Most Oracle documentation just says that the PX events are idle events and can be ignored; even “Oracle Wait Interface” (Shee, Deshpande, Gopalakrishnan) doesn’t go into it. However, I’m seeing massive quantities of PX events (and wait times) on 8, 16, and 64 CPU boxes. Reducing parallelism, controlling the parallel_execution_message_size, and using alternate access paths (e.g. the nested loop join, above) have helped somewhat, but I’d like to learn more about the subject in hopes of designing solutions properly. I realize I can’t avoid the “test and see” approach altogether, but can you shed some light on PX, or point me to references I may have missed?

    Great blog, btw.

    David

    Comment by David Scott — January 4, 2007 @ 6:53 pm BST Jan 4,2007 | Reply

  2. The commonest error in the documentation is that “PX Deq credit: Send Blkd” is an idle wait. When processes are running parallel to parallel, I am convinced that this is not the case, when processes are running parallel to serial (which ought only to be in the last step to the query co-ordinator) they are.

    At the top level (Parallel_to_Serial), a slave will have a 2 second time-out. At the lower levels (parallel_to_parallel) the time-out is (probably) 1/100 of a second. The trouble is that you can rarely draw any safe conclusions about durations when you look at the summary stats over even a fairly short period of time – and, of course, the parallel detail for any one query disappears as soon as the query completes.

    The only thought I have for minimising contention is to increase the parallel_execution_message_size as you increase the degree of parallelism, as this may result in a smaller number of larger messages – but I haven’t tested that idea in a realistic system yet. (Don’t forget to allow for the extra memory in your large pool if you decide to try experimenting with this).

    You probably know about Doug Burns’ papers on parallel query already – apart from that I can’t think of anything else at the moment; possibly someone else can chip in with other suggestions.

    Comment by Jonathan Lewis — January 4, 2007 @ 9:06 pm BST Jan 4,2007 | Reply

  3. Hi

    We recently applied the patchset 9.2.0.7 and after that some of our queries have suddenly stopped working and the wait events point to PX Deq: Table Q Normal

    Hmm just wundering if we should take that wait event (PX Deq: Table Q Normal )as idle wait event Jonathan ?

    Nothing else has changed except that patch.
    And if i rewrite the query the performance is again back.

    regards
    Hrishy

    Comment by Hrishy — January 5, 2007 @ 10:16 am BST Jan 5,2007 | Reply

  4. Hrishy, queries rarely “stop working”. End-users are allowed to say this, but DBAs and developers should not. Your query response time increased (significantly) either because (a) you used far more CPU, (b) you spent a lot more time waiting or (c) both.

    My best guess would be that when you applied patchset 9.2.0.7 (and you really ought to say what you applied it to – could be anything from 9.2.0.1 or 9.2.0.6) some execution paths changed. When this happens, the first thing you need to do is check the execution paths before and after the patch – and check the patch notes for possible threatening fixes.

    One such example is the “tie-break on index costing” – historically this would be by alphabetical order of name, but in 9.2.0.7 the number of distinct keys is used as a determinant. Possibly your change in run-time is affected by a change in Oracle’s choice of some critical indexes. This is a particular risk if you had previously set parameter optimizer_index_cost_adjust to something low (which means less than about 20 in a typical OLTP system).

    I believe, but don’t know for sure, that the “Table Q Normal” wait is emitted by a lower level slave when it needs to pass data to a higher level slave which is still busy with a previous packet of data – although if this is right I don’t know how Oracle distinguishes between Table Q Normal and Send Blkd. If I am right, it may simply mean that you have some slaves working much harder (because of changes in execution path). One possiblity is hash joins changing from (broadcast,none) to (hash, hash) as their distribution method for hash joins.

    Comment by Jonathan Lewis — January 5, 2007 @ 12:08 pm BST Jan 5,2007 | Reply

  5. Hi Jonathan

    I like that statement “Queries rarely stop working ” sounded kinda of funny and true too and the statement following that was nice too end users shoud decide that :-).

    I have posted everything here
    http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=27102&SkipA=25

    I will read on how the parallel query works esp the broadcast none and broadcast hash to hash

    regards
    Hrishy

    P.S
    I can always do the donkey work of rewriting the query and after a rewrite it screams and completes in 30 minutes.But then i am little selfish when it comes to acquiring knowledge and want to nail it down and understand whats going on.

    Comment by hrishy — January 6, 2007 @ 2:42 am BST Jan 6,2007 | Reply

  6. Hrishy, an odd thing about your post – the explain plan doesn’t show any parallelism. From dbms_xplan you should see column heading TQ, IN-OUT, and PQ Distrib.

    Comment by Jonathan Lewis — January 6, 2007 @ 7:52 am BST Jan 6,2007 | Reply

  7. Hi

    You have a eye for details :-).

    Yes on that website i had showed a serial plan because of the space limitation on the forum.

    But I can mail you everything the trace files the parallel execution plans etc..if desired by you.Can zip it and send it across so i wont flood ur mailbox.

    I think most of those idle events need not be idle events in many situations.As in this specific case

    Comment by hrishy — January 7, 2007 @ 2:14 am BST Jan 7,2007 | Reply

  8. Hrishy, thank you. Attention to detail is very important; it can be very tedious work at times, but it’s usually the quickest route to solving a problem.

    Don’t bother sending the material – you’ve fixed the problem, and I don’t really have time to spend on things that look like “ordinary” problems.

    Comment by Jonathan Lewis — January 7, 2007 @ 10:00 am BST Jan 7,2007 | Reply

  9. Hi Jonathan

    So what do you have to say about the so called idle event “PX Deq:Table Q Normal” :-D

    regards
    Hrishy

    Comment by Hrishy — January 8, 2007 @ 7:19 am BST Jan 8,2007 | Reply

  10. Hrishy, as far as I can tell it’s the result of an “upper-layer” slave (but not the query coordinator) broadcasting a message that it is ready to receive more data (though I don’t know who acknowledges it, or how). It is pretty much an “idle” event – but if you see a very large number, and it’s the most significant recorder of time, it gives you a hint that your volume of slave to slave messaging is the root source of performance issues. You probably need to review execution paths (to reduce parallel_to_parallel activity), although you might get some benefit from increasing the parallel_execution_message_size.

    Comment by Jonathan Lewis — January 8, 2007 @ 9:24 am BST Jan 8,2007 | Reply

  11. Jonathan,

    I’m late for another party, although I read this when you first posted it. Even though I was aware of parallel index range scans before I wrote the paper, it made me re-examine my language to make sure it was accurate enough. Sure enough, this sentence could be misleading.

    “First, it’s worth knowing that PX slaves perform direct path reads rather than db file scattered reads.”

    To avoid the possible perception that slaves *always* use direct path reads, I’ve changed it to :-

    “First, it’s worth knowing that PX slaves use direct path reads for full table scans and index fast full scans, rather than db file scattered reads.”

    This blog has also given me some very useful ideas for my next paper, so thanks to you and David for that.

    Comment by Doug Burns — January 8, 2007 @ 10:39 pm BST Jan 8,2007 | Reply

  12. Jonathan,

    Let’s consider the following case. There is a table with the PARALLEL attribute, called ‘event’. Now, the simple statement

    insert /*+ append */ into event select * from event;

    will have an execution plan similar to this:

    INSERT STATEMENT
      LOAD AS SELECT EVENT
        PX COORDINATOR
          PX SEND QC (RANDOM)
            PX BLOCK ITERATOR
              TABLE ACCESS FULL EVENT

    The plan implies that only the select part is parallelized. Is it possible to achieve parallelism in the insert part ?

    Thanks.

    Val

    Comment by Val Carey — January 3, 2008 @ 6:13 pm BST Jan 3,2008 | Reply

  13. Val,

    Parallel DML is disabled by default (although parallel query and parallel DDL are enabled by default), so before you do your append, try issuing:
    alter session enable parallel dml;

    Comment by Jonathan Lewis — January 3, 2008 @ 6:33 pm BST Jan 3,2008 | Reply

  14. Jonathan,

    I do see parallel execution with slaves and all. My simplified question is motivated by trying to improve parallel load performance when using an external table.

    The configuration:

    4 CPUs;
    two separate disk arrays, one containing the flat file/external table, the other contains the table being loaded.

    When loading ‘serially’, both arrays are about 10% busy. The single Oracle process doing the load uses about 95% CPU with about 15MB/s flat file read rate(the disk array is 7-10% busy).

    The natural answer to this is to use parallel load since we have plenty of CPU power (3 extra CPUs).

    When loading ‘in parallel’, there are 8 slaves and one coordinator. The flat file read rate increases to about 20-25 MB/s (25-27% busy). The coordinator consumes close to 100% CPU. The slaves consume about 12-15% CPU each. So the system is running at about 52% CPU total and the disk array is 25% busy. Clearly, the bottleneck is the coordinator which is apparently doing the insert part of the statement.

    When running something like select count(*) from the_external_table in the parallel mode, the disk array is about 96% busy with a throughput of about 110MB/s.

    The external table ‘insert .. select’ execution plan is identical to the one I gave earlier, and seemingly implies that the insert part is constrained by the coordinator.

    Comment by Val Carey — January 3, 2008 @ 7:42 pm BST Jan 3,2008 | Reply

  15. Val,
    Your plan is a 10g plan, so here’s a 10g demo (10.2.0.1 specifically) that shows the sort of thing you want to see.
    The two outputs follow the sample code. Note the change when you enable parallel DML.


    drop table t2;
    drop table t1;

    create table t1 as select * from all_objects;
    create table t2 as select * from t1 where rownum <= 1;

    alter table t1 parallel (degree 2);
    alter table t2 parallel (degree 2);

    explain plan for
    insert /*+ append */ into t2 select * from t1;

    select * from table(dbms_xplan.display(null,null,'-note -cost -rows -bytes'));

    commit;

    alter session enable parallel dml;

    explain plan for
    insert /*+ append */ into t2 select * from t1;

    select * from table(dbms_xplan.display(null,null,'-note -cost -rows -bytes'));

    And the two execution plans - the second one showing parallel inserts.
    Rremember that parallel insert has an important impact on the way space is allocated - every slave assumes it is the only slave, and allocates space as if starting an entire new segment.


    ----------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Time     |    TQ  |IN-OUT| PQ Distrib |
    ----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT      |          | 00:00:02 |        |      |            |
    |   1 |  LOAD AS SELECT       | T2       |          |        |      |            |
    |   2 |   PX COORDINATOR      |          |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)| :TQ10000 | 00:00:02 |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     PX BLOCK ITERATOR |          | 00:00:02 |  Q1,00 | PCWC |            |
    |   5 |      TABLE ACCESS FULL| T1       | 00:00:02 |  Q1,00 | PCWP |            |
    ----------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------
    | Id  | Operation               | Name     | Time     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT        |          | 00:00:02 |        |      |            |
    |   1 |  PX COORDINATOR         |          |          |        |      |            |
    |   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 00:00:02 |  Q1,01 | P->S | QC (RAND)  |
    |   3 |    LOAD AS SELECT       | T2       |          |  Q1,01 | PCWP |            |
    |   4 |     PX RECEIVE          |          | 00:00:02 |  Q1,01 | PCWP |            |
    |   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 00:00:02 |  Q1,00 | P->P | RND-ROBIN  |
    |   6 |       PX BLOCK ITERATOR |          | 00:00:02 |  Q1,00 | PCWC |            |
    |   7 |        TABLE ACCESS FULL| T1       | 00:00:02 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------

    Comment by Jonathan Lewis — January 3, 2008 @ 8:11 pm BST Jan 3,2008 | Reply

  16. Jonathan,

    I apologize for missing your answer the first time (‘alter session enable parallel dml;’). I do not use parallel query/DDL/DML a lot and have forgot that the DML option needs to be enabled explicitely. So, when I saw all the slaves running merrily along, I assumed that was the best Oracle could do.

    With parallel DML enabled, Oracle is CPU bound (89%) and the flat file read throughput is 2.5-3 times higher (38-45 MB/s) than without PDML.

    On the other hand, queries of the kind ‘select /*+ parallel(event) */ count(*) from event’, in the absence of any other activity, are two times slower than than their serial counterparts , but that was expected …

    Thanks.

    Comment by Val Carey — January 3, 2008 @ 11:51 pm BST Jan 3,2008 | Reply

  17. Jonathan, do I infer correctly that you believe that increasing px message buffer size can lower the non-idle type of “PX Deq Credit: send blkd”? I have a SQL load process that shows an (admittedly aggregate) 200ms average wait time on 720,000 “PX Deq Credit: send blkd” events, accounting for around 45% of the non-idle timed waits during the load process according to AWR (10.2.0.4.0).

    Well, I’m going to try to get test executions with the current message size of 32kb doubled and halved to see what effect it has, if any.

    Comment by David Aldridge — May 29, 2008 @ 12:03 am BST May 29,2008 | Reply

  18. David,

    I don’t know enough about the meaning of this wait (and the possibly associated “need buffer” ) to be certain. But I believe it could be one of those setting that gives you an “inverted U” performance graph. (i.e. things get better to start with, hit a maximum, then get worse).

    Since your value (32KB) is already much bigger than the default (~2K, or ~4K depending on other parameters), I have no idea what might happen in your tests.

    Another thought that I want to look at some time is the effect of memory starvation on allocation and acquisition speeds. Is it possible that some waits are due to a shortage of memory in the pool (large, or shared, depending on configuration).

    In passing – I’ve just been to a site that found that their managed recovery (using RAC with standby RAC and parallel recovery) operates much faster if they increase the parallel message size to 16K.

    Comment by Jonathan Lewis — May 30, 2008 @ 9:53 am BST May 30,2008 | Reply

  19. Jonathan,

    I happened upon your weblog while searching the Internet for information in regard to a question about parallel execution messages. I’m wondering about any possible effect of a decision taken long ago in regard to the VARCHAR2 columns of some tables that participate in the subquery of a parallelized INSERT / SELECT statement. The tables’ VARCHAR2 columns were defined as VARCHAR2(100) as a matter of course, without regard for the actual, maximal length of the values in each of the respective columns. The developer had a couple of reasons for setting such high, one-size-fits-all maxima, and the choice seemed harmless at the time. However, as I’ve worked on improving the performance of an INSERT / SELECT statement that reads these tables in parallel, I’ve wondered if leaving superfluous “headroom” in a VARCHAR2 column definition affects the length of parallel execution messages having that column’s values as part of its contents.

    In effect, what I’m asking is whether the length of PX messages varies with the actual length of the values passed by the individual messages or the messages are given a fixed-length format determined by the defined, maximal length of the columns they will be passing. In the end, the practical question is whether any significant amount of memory may be wasted in the later scenario.

    Only in regard to permanent storage for tables and indexes do I have any assurance based on previous reading; there, of course, the storage forms of variable-length column values are variable-length. But besides wondering about the allocation of memory for VARCHAR2 in PX messages, I wonder about any adverse effect of a high defined maximum for a VARCHAR2 column’s length, on the allocation of sort memory, hash memory, and the temporary disk storage for each of these.

    And I wonder whether I’ll have to dig in and find out some of this for myself or whether you already know the answers. Either way, thanks for reading and, if time permits, replying.

    Comment by Doug — July 23, 2008 @ 1:27 am BST Jul 23,2008 | Reply

  20. Doug,

    Thanks for raising this point – it’s an example of the type of question that often gets overlooked in the early stages of adopting a piece of technology.

    Some years ago I noted that numeric columns were always passed in parallel messages with the maximum size of 22 bytes – the effect was causing significant performance problems. I don’t recall noticing varchar2 following the same pattern – but this may be because I didn’t look at the time.

    As a first check I would create a table with a varchar2(2) column and a load of data and run a parallel query against it; then recreate the table with the same column set to varchar2(1000) and repeat. Check v$pq_tqstat each time to see what the messages counts and bytes look like.

    Comment by Jonathan Lewis — July 23, 2008 @ 7:12 am BST Jul 23,2008 | Reply

  21. Hi Jonathan
    I have been visiting your blog since some time back; I haven’t put a comment or anything as of now though. But I have been caught with an issue related with what you’ve mentioned here on this page. ‘PX Deq Credit: send blkd’ tops all others in my AWR report with 15,783,670 waits. I don’t have parallel DML enabled here. I can’t avoid FTS here and can’t even use index as I’ve a ‘left join’ here to a table with more than 50M records. I have set pga_aggregate_target to 15728640000. i guess hash_area_size doesn’t play any role here when I’ve pga_aggregate_target set. I have parallel_execution_message_size set to 2152. I wonder what could be the possible reason. The execution time has gone 10 hrs to 20 hrs.


    Event                                Waits  Time(s)  Avg (ms)  % Total     Wait Class
    PX Deq Credit: send blkd           783,670   21,307         1     39.5          Other
    log file switch (chkpnt incomplete)  7,402    6,998       945     13.0  Configuration
    db file scattered read              51,904    5,831       112     10.8       User I/O
    rdbms ipc reply                      2,952    4,069     1,378      7.5          Other
    CPU time                                      1,682                3.1

    Comment by Rizu — July 23, 2008 @ 8:05 am BST Jul 23,2008 | Reply

  22. Rizu,
    I’ve deleted the comment where you posted your “Top 5 Timed Events”, and copied the data (with a little formatting to try to make it more readable) into the previous post.

    The first thing I would do in your case is to look at the SECOND event in the list. “Log file switch (checkpoint incomplete)” is a strong indicator that your log files are probably too small (or perhaps you don’t have enough of them).

    If you increase the size of your log files (see http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/ and the post it links to) you can probably get rid of all that wait time.

    If the log file switch wait time comes from one layer of PX slaves, it is possible that a lot of the “send blocked” wait are coming from the PX slaves that are trying to feed them – so eliminating the log file switch may eliminate a large fraction (perhaps most, and given the layering of PX slaves) of the “send blocked” wait time.

    Comment by Jonathan Lewis — July 23, 2008 @ 9:01 am BST Jul 23,2008 | Reply

  23. Thanks Jonathan,
    I’ve found the culprit, which happens to be the log switch; log switch every minute, isn’t it real high rate? I’ve added 4 more log groups to it. Will also increase logfile size. Thanks again, for your detailed description.

    Comment by Rizu — July 23, 2008 @ 1:04 pm BST Jul 23,2008 | Reply

  24. scott@IRADSDB> select /*+ parallel(e,4) parallel(d,4) */ dname,ename,sal
      2  from emp e,dept d
      3  where e.deptno = d.deptno;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1036174697
    
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |    14 |   364 |     2   (0)| 00:00:01 |        |      |            |
    |   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |
    |   2 |   PX SEND QC (RANDOM)          | :TQ10000 |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   3 |    NESTED LOOPS                |          |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    |   4 |     PX BLOCK ITERATOR          |          |       |       |            |          |  Q1,00 | PCWC |            |
    |   5 |      TABLE ACCESS FULL         | EMP      |    14 |   182 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    |   6 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    |*  7 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       7 - access("E"."DEPTNO"="D"."DEPTNO")
    

    1) Can you please help me how to read this Parallel Execution plan?
    (a) looking at the step 5 in plan TABLE ACCESS FULL is done by Paralle combined with Parent (PCWP) is that parent for this operation is Step 3 in plan?
    (b) Looking at TQ column in plan that only one Slave process (Q1,00) is allocated for this Execution?

    Please correct me if i am wrong.

    Comment by Rajeshwaran, Jeyabal — January 27, 2010 @ 2:40 pm BST Jan 27,2010 | Reply

    • First a correction to vocabulary: the TQ represents a “table queue” (or virtual table) used to pass data from one layer of processes to the next; it’s not representative of a set of Slave processes. It is true, though, that in your example you do have only one set of parallel execution slaves.

      In effect, the plan breaks a single nested loop join into a number of nested loop joins using non-overlapping chunks (block ranges) of the EMP table. Each PX slave takes one block range at a time. There is only one set of PX slaves, and as they produce data (at line 3) they pass it up to the query co-ordinator by writing into the virtual table TQ10000, and the query co-ordinator reads the virtual table and passed the data to the end-user.

      In answer to your question (a) – id 4 is the parent of id 5. Having said that, I have never figured out exactly when Oracle decides to report a line as PCWC or PCWP – it’s not easy since lines are often combined with both parent and child, and this makes the choice seem arbitrary. In this case, I believe that the lines with id 3, 4 and 6 are combined with both parent and child, and line id 2 is both parallel to serial and parallel combined with child. (Lines 5 and 7 can only be parallel combined with parent since they have no child operations).

      Comment by Jonathan Lewis — January 27, 2010 @ 8:10 pm BST Jan 27,2010 | Reply

  25. So, having one set of parallel execution slaves is same as having the process executing in Serial process. Am i right? I think parallel execution benefit is having multiple parallel execution slaves executing together.
    Please correct me if i am wrong.

    Comment by Rajeshwaran, Jeyabal — January 28, 2010 @ 3:06 pm BST Jan 28,2010 | Reply

  26. The expression “one set of PX slaves” is not the same as “one slave”.

    This plan has “multiple parallel execution slaves executing together”.

    Comment by Jonathan Lewis — January 29, 2010 @ 6:28 pm BST Jan 29,2010 | Reply

    • Jonathan,

      I have silly question.
      1. How do i find the parent process for a parallel slave?
      2. My partition table have 3800 partitions. How can I restrict parallel for a single query
      like at max 10 slaves for a query. I am using oracle 10204.

      Thanks

      Comment by kumar — June 25, 2010 @ 7:14 am BST Jun 25,2010 | Reply

      • Kumar,

        Oracle has various pieces of code that try to restrain parallel execution count, it’s probably still a good idea for 10g to work on the basis of setting the degree/instances value for every table and index to 1 and then using parallle() and parallel_index() hints to set the degree of parallelism you want.

        It’s also sensible to limit the number of concurrent queries that are allowed to run in parallel – and that’s harder to control, of course, unless you are already using some type of scheduler program to run the big queries.

        Comment by Jonathan Lewis — June 30, 2010 @ 8:40 am BST Jun 30,2010 | Reply

  27. Hi Jonathan,

    Allocating parallelism for the select query will the Optimizer change explain/execution plan from hash join to nested loop or vice versa…. If so, would you please explain why?

    I understand the cost will come down because of the (parallel slave) multiple processes to go after the necessary data instead of just one process.

    Comment by ganesh — October 25, 2011 @ 12:59 am BST Oct 25,2011 | Reply

    • Ganesh,

      There is no single answer to your question about plans switching between hash joins and nested loop joins.

      The only parallel paths (as drivers) are the tablescan and the index fast full scan – and you will often find that when scans can run in parallel Oracle will switch from an index-based nested loop join to a hash join because the cost of the necessary tablescans or index fast full scans has dropped.

      HOWEVER – it is possible for an execution path to start with a parallel tablescan and then – for each row of the scan – do an indexed access into the second table, so parallel doesn’t preclude nested loops; in fact, it is possible (particularly with locally partitioned indexes in place) that for a serial hash join to turn into a parallel nested loop because each slave “assumes” it will be accessing one partition through the local index partition.

      Comment by Jonathan Lewis — November 13, 2011 @ 3:17 pm BST Nov 13,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers