1 2 Previous Next 19 Replies Latest reply: Jul 19, 2012 2:49 AM by Karan RSS

    How to find parallel degree from execution plan

    user8779435
      Is it possible to see the degree of parallelism from the execution plan?

      Thanks
        • 1. Re: How to find parallel degree from execution plan
          Nikolay Savvinov
          Hi,

          no, I don't think so -- but you can find it in V$SQL: PX_SERVERS_EXECUTIONS.

          Best regards,
          Nikolay
          • 2. Re: How to find parallel degree from execution plan
            Karan
            We can ... When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).


            | 1 | PX COORDINATOR | | | | | | | |
            |

            | 2 | PX SEND QC (RANDOM)| :TQ10000 | 16 | 576 | 2 (0)| 00:00:06 | Q1,00 | P->S | QC
            (RAND) |

            | 3 | PX BLOCK ITERATOR | | 16 | 576 | 2 (0)| 00:00:06 | Q1,00 | PCWC |
            • 3. Re: How to find parallel degree from execution plan
              Nikolay Savvinov
              Hi Karan,

              DOP is a number, and I don't think you can find this number on the plan.

              Best regards,
              Nikolay
              • 4. Re: How to find parallel degree from execution plan
                Karan
                Hi Nikolay, I consider your statement that DOP is number and i am aware of that but do you mean the coordinator who is QC will not be shown ? Do you mean execution server processes the PX's are not shown ? what i have seen is as long as i see slave sets like suppose (Q1,00) (Q1,01) (Q1,02) i can know how many slave sets are working..Becaue majority of work is done by them as little is done by coordinator the QC... assuming a query requires more than 1 PX server sets...Because i have always seen that in TQ column how many number of slave sets are working... Any link if you can post which tells that i would appreciate that we cannot see them... DOP number is offcourse not shown

                Regards
                Karan
                • 5. Re: How to find parallel degree from execution plan
                  Nikolay Savvinov
                  Hi Karan,

                  consider this:
                  drop table t;
                  
                  create table t (x number) parallel (degree 4);
                  
                  insert into t select level from dual connect by level <= 1e5;
                  
                  explain plan for select * from t;
                  
                  alter session enable parallel query;
                  
                  select /*+ parallel (t1 4)*/* from t1;
                  
                  select * from table(dbms_xplan.display(null, null, 'parallel'));
                  
                  select * from table(dbms_xplan.display_cursor((select sql_id from v$sql where sql_text = 'select /*+ parallel (t1 4)*/* from t1'), null, 'parallel'));
                  
                  SQL_ID  509k784qyxub4, child number 0
                  -------------------------------------
                  select /*+ parallel (t1 4)*/* from t1
                   
                  Plan hash value: 2494645258
                   
                  --------------------------------------------------------------------------------------------------------------
                  | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                  --------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT     |          |       |       |   119 (100)|          |        |      |            |
                  |   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
                  |   2 |   PX SEND QC (RANDOM)| :TQ10000 |   100K|    10M|   119   (0)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |
                  |   3 |    PX BLOCK ITERATOR |          |   100K|    10M|   119   (0)| 00:00:02 |  Q1,00 | PCWC |            |
                  |*  4 |     TABLE ACCESS FULL| T1       |   100K|    10M|   119   (0)| 00:00:02 |  Q1,00 | PCWP |            |
                  --------------------------------------------------------------------------------------------------------------
                   
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                   
                     4 - access(:Z>=:Z AND :Z<=:Z)
                   
                  select s.px_servers_executions from v$sql s where sql_id = ((select sql_id from v$sql where sql_text = 'select /*+ parallel (t1 4)*/* from t1'));
                  
                  px_servers_executions
                  ---------
                  4
                  In this example, I create a table with DOP=4 and query it with the same DOP. From V$SQL, I can see that indeed, Oracle provided the requested DOP=4.
                  But how do I know that from the plan?

                  Best regards,
                  Nikolay
                  • 6. Re: How to find parallel degree from execution plan
                    Jonathan Lewis
                    user8779435 wrote:
                    Is it possible to see the degree of parallelism from the execution plan?
                    If you're running 11g then there are cases where the dbms_xplan will report the assumed DOP and reason for that DOP in the notes section of the output. This does not mean that enough slaves are available at run time for that DOP to appear, of course.

                    If you have licensed the performance pack you can then access v$sql_plan_monitor (possibly through OEM/Grid control) to report the real execution DOP for executions of the statement that are currently running, or completed in the recent past.

                    Regards
                    Jonathan Lewis
                    • 7. Re: How to find parallel degree from execution plan
                      Dom Brooks
                      In 11.1 onwards and if licensed for Diagnostics+Tuning Pack, see V$SQL_MONITOR and also DBMS_SQLTUNE.REPORT_SQL_MONITOR.

                      Edited by: Dom Brooks on Jul 11, 2012 11:09 AM
                      Too late / beaten to it
                      • 8. Re: How to find parallel degree from execution plan
                        Nikolay Savvinov
                        Hi Jonathan,

                        I can't get dbms_xplan to display the DoP in the notes section in my Oracle 11.2.0.1 -- am I missing something? I tried "all", "parallel", "note", "parallel +note", nothing seems to be helping.

                        Best regards,
                        Nikolay
                        • 9. Re: How to find parallel degree from execution plan
                          Dom Brooks
                          DBMS_XPLAN reports the top level tags
                          <info type="dop reason"></info>
                          <info type="dop"></info>
                          from V$SQL_PLAN.OTHER_XML

                          If these aren't present.... no extra info in the notes section.

                          Note the difference in statement parallel hint and object parallel hint.
                          • 10. Re: How to find parallel degree from execution plan
                            Nikolay Savvinov
                            Dom Brooks wrote:
                            DBMS_XPLAN reports the top level tags
                            <info type="dop reason"></info>
                            <info type="dop"></info>
                            from V$SQL_PLAN.OTHER_XML

                            If these aren't present.... no extra info in the notes section.

                            Note the difference in statement parallel hint and object parallel hint.
                            Hi Dom,

                            nope, no mention of "dop" in other_xml. Is there anything I could do to make it show up?..
                            <other_xml><info type="db_version">11.2.0.1</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash">2494645258</info><info type="plan_hash_2">263533726</info>
                            <outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.1')]]></hint>
                            <hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></other_xml>
                            Best regards,
                            Nikolay
                            • 11. Re: How to find parallel degree from execution plan
                              Dom Brooks
                              nope, no mention of "dop" in other_xml.
                              Exactly. That's what I mean.

                              The tags and the note reflects statement level DOP not object level DOP.

                              e.g.
                              SQL> select /*+ parallel(t 64) */ count(*) from xyz t;
                              
                                COUNT(*)
                              ----------
                                32534644
                              
                              SQL> select * from table(dbms_xplan.display_cursor);
                              
                              PLAN_TABLE_OUTPUT
                              ------------------------------------------------------------------------------------------------------------------------
                              SQL_ID  dcyqrc0x4f5dk, child number 0
                              -------------------------------------
                              select /*+ parallel(t 64) */ count(*) from xyz t
                              
                              Plan hash value: 1197314195
                              
                              ------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              ------------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT       |          |       |  8802 (100)|          |       |       |        |      |            |
                              |   1 |  SORT AGGREGATE        |          |     1 |            |          |       |       |        |      |            |
                              |   2 |   PX COORDINATOR       |          |       |            |          |       |       |        |      |            |
                              |   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
                              |   4 |     SORT AGGREGATE     |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
                              |   5 |      PX BLOCK ITERATOR |          |    32M|  8802   (1)| 00:00:45 |     1 |    11 |  Q1,00 | PCWC |            |
                              |*  6 |       TABLE ACCESS FULL| XYZ      |    32M|  8802   (1)| 00:00:45 |     1 |    11 |  Q1,00 | PCWP |            |
                              ------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 6 - access(:Z>=:Z AND :Z<=:Z)
                              
                              
                              23 rows selected.
                              
                              SQL>  select /*+ parallel(64) */ count(*) from xyz t;
                              
                                COUNT(*)
                              ----------
                                32534644
                              
                              SQL> select * from table(dbms_xplan.display_cursor);
                              
                              PLAN_TABLE_OUTPUT
                              --------------------------------------------------------------------------------------------------------------------------------
                              SQL_ID  03a0j6a4fcmr6, child number 0
                              -------------------------------------
                               select /*+ parallel(64) */ count(*) from trade t
                              
                              Plan hash value: 2847656374
                              
                              ------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                 | Name        | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              ------------------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT          |             |       |   434 (100)|          |       |       |        |      |            |
                              |   1 |  SORT AGGREGATE           |             |     1 |            |          |       |       |        |      |            |
                              |   2 |   PX COORDINATOR          |             |       |            |          |       |       |        |      |            |
                              |   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
                              |   4 |     SORT AGGREGATE        |             |     1 |            |          |       |       |  Q1,00 | PCWP |            |
                              |   5 |      PX BLOCK ITERATOR    |             |    32M|   434   (5)| 00:00:03 |     1 |    11 |  Q1,00 | PCWC |            |
                              |*  6 |       INDEX FAST FULL SCAN| XYZ_7_IDX   |    32M|   434   (5)| 00:00:03 |     1 |    11 |  Q1,00 | PCWP |            |
                              ------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 6 - access(:Z>=:Z AND :Z<=:Z)
                              
                              Note
                              -----
                                 - Degree of Parallelism is 64 because of hint
                              
                              
                              27 rows selected.
                              
                              SQL> 
                              Also see:
                              https://blogs.oracle.com/datawarehousing/entry/explaining_explain_plan_notes
                              • 12. Re: How to find parallel degree from execution plan
                                Girish Sharma
                                Degree of Parallelism
                                The DOP determined by the optimizer is shown in the notes section of an explain plan output (shown in the following explain plan output), visible either using the explain plan statement or V$SQL_PLAN.
                                http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#BEICCFIE

                                Regards
                                Girish Sharma
                                • 13. Re: How to find parallel degree from execution plan
                                  Jonathan Lewis
                                  Nikolay Savvinov wrote:

                                  I can't get dbms_xplan to display the DoP in the notes section in my Oracle 11.2.0.1 -- am I missing something? I tried "all", "parallel", "note", "parallel +note", nothing seems to be helping.
                                  I've just done a couple of quick checks on 11.2.0.3 with little success.
                                  I just happened to spot this recently - and it may have been on an Exadata system with parallel_degree_policy set to auto, or parallel_automatic_tuning set to true (or both, or some other parallel parameter tweaked).

                                  In my little test I set the parallel_degree_policy and got a note that I hadn't run calibrate_io - so that may also be part of the requirment. In the past I've seen notes like "using DOP = 4 from tableX", or "using DOP=6 from query hint".


                                  Oops - my turn to be too late.

                                  Regards
                                  Jonathan Lewis

                                  Edited by: Jonathan Lewis on Jul 11, 2012 12:26 PM
                                  • 14. Re: How to find parallel degree from execution plan
                                    Karan
                                    Hi Nikolay, Sorry for late reply, was taking a session ... I took the following from my sql monitor report

                                    <font class="s9">
                                    <a name="pq_stats"/>
                                    <b> Parallel Execution Details

                                    (DOP=4
                                    , Servers Requested=5
                                    , Servers Allocated=4)
                                    </b>

                                    Regards
                                    Karan
                                    1 2 Previous Next