6 Replies Latest reply: Feb 18, 2014 4:37 AM by Martin Preiss RSS

    Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor

    user4587979

      Oracle version 11.1.0.7.0

       


      Plan from dbms_xplan.display

      {code }

      ------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                | Name                  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

      ------------------------------------------------------------------------------------------------------------------

      |  0 | SELECT STATEMENT        |                      |    18 |  846 |    57  (4)| 00:00:01 |      |      |

      |  1 |  HASH GROUP BY          |                      |    18 |  846 |    57  (4)| 00:00:01 |      |      |

      |*  2 |  HASH JOIN              |                      |  387 | 18189 |    56  (2)| 00:00:01 |      |      |

      |*  3 |    HASH JOIN            |                      |  387 |  9288 |    53  (2)| 00:00:01 |      |      |

      |  4 |    PARTITION LIST SINGLE|                      |  387 |  5805 |    23  (0)| 00:00:01 |  KEY |  KEY |

      |*  5 |      TABLE ACCESS FULL  | t3                      |  387 |  5805 |    23  (0)| 00:00:01 |    10 |    10 |

      |  6 |    TABLE ACCESS FULL    | t2                    |  5892 | 53028 |    29  (0)| 00:00:01 |      |      |

      |  7 |    TABLE ACCESS FULL    | t1                    |  300 |  6900 |    3  (0)| 00:00:01 |      |      |

      ------------------------------------------------------------------------------------------------------------------

      {code }

       


      Plan from dbms_xplan.display_cursor

       

      -----------------------------------------------------------------------------------------------------------

      | Id  | Operation                | Name                  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

      -----------------------------------------------------------------------------------------------------------

      |  0 | SELECT STATEMENT        |                      |        |    57 (100)|      |      |          |

      |  1 |  HASH GROUP BY          |                      |    18 |    57  (4)|  972K|  972K|  624K (0)|

      |*  2 |  HASH JOIN              |                      |    387 |    56  (2)|  1269K|  1269K|  617K (0)|

      |*  3 |    HASH JOIN            |                      |    387 |    53  (2)|  1306K|  1306K|  685K (0)|

      |  4 |    PARTITION LIST SINGLE|                      |    387 |    23  (0)|      |      |          |

      |*  5 |      TABLE ACCESS FULL  | t3                    |    387 |    23  (0)|      |      |          |

      |  6 |    TABLE ACCESS FULL    | t2                    |  5892 |    29  (0)|      |      |          |

      |  7 |    TABLE ACCESS FULL    | t1                    |    300 |    3  (0)|      |      |          |

      -----------------------------------------------------------------------------------------------------------

       

       

       

      Why there is %CPU difference on both the plans. Which is one is correct or accurate. How they are actually calculated?  Above plans are from same query.

        • 1. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
          Suntrupth

          DISPLAY - to format and display the contents of a plan table.

           

           

          DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

           

           

          Have a look at : http://hoopercharles.wordpress.com/2010/02/19/what-is-the-meaning-of-the-cpu-column-in-an-explain-plan/

           

           

          DISPLAY_CURSOR provides the actual estimates and execution plan that the optimizer has used to execute the query.

           

           

          Regards,

          Suntrupth

          • 2. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
            Harmandeep Singh

            It is same in  both cases.. please check

             

            Display functions gives plan based on stats of underlying objects and so can be wrong if there are wrong or missing stats on oobjects  or when global temp tables are involved

            Display_cursor is actual runtime execution plan and can bw different as optimizer can see different cardinality values due to kicking of dynamic sampling functionality and cardinality feedback mechanisms. 

             

             

            Thanks

            Harman

            • 3. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
              Jonathan Lewis

              Ignore the percentage shown in line 0 of the display_cursor() output - it's a defect in the code.

               

              If you check v$sql_plan you'll find that Oracle hasn't recorded an io_cost and cpu_cost in line 0 - I believe it has just copied the value from the position column to report as the cost - and then the code has arbitrarily put 100 in as the CPU percentage when it would have been better to leave it blank.

               

              Regards

              Jonathan Lewis

              • 4. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
                user4587979

                Jonathan,

                 

                Thank you for your shift help. Yeah, io_cost and cpu_cost columns were null for operation 0 in v$sql_plan. Did oracle fixed this defect.?

                • 5. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
                  Jonathan Lewis

                  user4587979 wrote:

                   

                  Yeah, io_cost and cpu_cost columns were null for operation 0 in v$sql_plan. Did oracle fixed this defect.?

                   

                  Not in 11.2.0.4 - I don't have a copy of 12.1.0.1 on hand, but maybe someone else can do a quick check for you.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: Regarding %CPU for dbms_xplan.display and dbms_xplan.display_cursor
                    Martin Preiss

                    seems to be still broken in 12c:

                    -- 12.1.0.1

                    SQL> explain plan for

                      2  select * from scott.emp;

                     

                    EXPLAIN PLAN ausgeführt.

                     

                    SQL> select *

                      2    from table(dbms_xplan.display);

                     

                    PLAN_TABLE_OUTPUT

                    --------------------------------------------------------------------------

                    Plan hash value: 3956160932

                    --------------------------------------------------------------------------

                    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                    --------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |

                    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

                    --------------------------------------------------------------------------

                     

                    SQL> select id, io_cost, cpu_cost from v$sql_plan where sql_id = 'ggqns3c1jz86c';

                     

                            ID    IO_COST   CPU_COST

                    ---------- ---------- ----------

                             0

                             1          3      39667

                     

                    SQL> select *

                      2    from table( dbms_xplan.display_cursor ( 'ggqns3c1jz86c', '0'));

                     

                    PLAN_TABLE_OUTPUT

                    --------------------------------------------------------------------------

                    SQL_ID  ggqns3c1jz86c, child number 0

                    -------------------------------------

                    select * from scott.emp

                     

                    Plan hash value: 3956160932

                    --------------------------------------------------------------------------

                    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                    --------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

                    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

                    --------------------------------------------------------------------------