3 Replies Latest reply on Oct 14, 2014 6:57 PM by Jonathan Lewis

    column PARENT_ID of V$SQL_PLAN: wrong values? parent_id = 0 with depth > 1?

    asy

      Today I selected an execution plan from an SQL statement recently executed. I used:

      select

        id, parent_id,

        lpad (' ', 3*depth) || operation || ' ' || options ||

          decode (object_name, null, null, ' on ' || object_owner || '.' || object_name) op

      from v$sql_plan

      where sql_id = :sql_id and child_number = :child_number

      order by id;

      and analyzed it automatically. My newly-written procedure threw assertions telling me the structure is corrupt: the links by parent_id do not match the depth.

      The depth seems to be correct, the parent_id seems sometimes zeroed.

      Am I missing something?

      1.PNG

        • 1. Re: column PARENT_ID of V$SQL_PLAN: wrong values? parent_id = 0 with depth > 1?
          Jonathan Lewis

          You don't say which version of Oracle you're using, but it's fairly easy in 10g and 11g to find examples where the code that constructs the execution plan table output hasn't caught up with the complexity of the SQL that the optimizer is able to handle. In your case it looks like your select list includes decode()  expressions that contain embedded scalar subquery expressions.

           

           

           

          Regards

          Jonathan Lewis

          • 2. Re: Re: column PARENT_ID of V$SQL_PLAN: wrong values? parent_id = 0 with depth > 1?
            asy

            Amendment: I was wrong. The parent_id is right, the depth is wrong.

             

            > ... version of Oracle ...

            Not everything should depend on a version. But to detail it:

            • database: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

            • database o.s.: 6.5 Oracle Linux with UEK

            • client: 32-bit Windows: Version 11.2.0.1.0 - Production

            • client o.s.: Wndows XP SP3

             

            And maybe I should add more of the circumstances:

            The plan comes from a nonblocking execution stopped by OCIBreak(). Here is an extract that shows how I did only a minimum of execution of the statement:

            CString strPrefix;

            strPrefix.Format ("/*%s*/", (const char *) strMagic);

             

            CDbCursor cur1, cur2;

            cur1.Open (&connUser);

            cur2.Open (&connAdmin);

             

            cur2 ["prefix"] = strPrefix;

            cur2.Execute ("select address, hash_value, sql_id, child_address, child_number from v$sql where sql_text like :prefix || '%'");

            if (cur2.TryFetch())

              asqthrow_text ("There are statements in v$sql starting with '%s'.", (const char *) strPrefix);

             

            cur1 ["module"] = "my own database utility";

            cur1 ["action"] = strMagic;

            cur1.Execute ("begin sys.dbms_application_info.set_module (:appname, :action); end;");

             

            cur1.Control ("ASYNC-EXECUTION");

            cur1.Execute (strPrefix + pszSql); // <<<<<< start execution

            //while (!cur1.Control ("SYNC"))

            //  sleep_ms (0);

             

            do cur2.Execute(); while (!cur2.TryFetch()); // <<<<<< find SQL in v$sql

            cur1.Cancel(); // <<<<<< cancel execution (OCIBreak() and OCIReset())

            > your select list includes decode()  expressions that contain embedded scalar subquery expressions

            YES. The "wrong values" appear only in the "select list" part of the execution plan. Starting from id = 51, there is the "from" and "where" part of the execution plan, with perfect depths.

            • 3. Re: Re: Re: column PARENT_ID of V$SQL_PLAN: wrong values? parent_id = 0 with depth > 1?
              Jonathan Lewis

              I've just dug out a simple example that did the same sort of thing on 11g - it still has problems on 12c:

               

              select

                      case mod(id,2)

                              when 1 then (select max(t1.id) from t1 where t1.id <= t2.id)

                              when 0 then (select max(t1.id) from t1 where t1.id >= t2.id)

                      end id

              from    t2

              ;

               

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

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

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

              |   0 | SELECT STATEMENT     |      |  2500 | 10000 | 28039   (2)| 00:00:02 |

              |   1 |  SORT AGGREGATE      |      |     1 |     4 |            |          |

              |*  2 |   TABLE ACCESS FULL  | T1   |   125 |   500 |    11   (0)| 00:00:01 |

              |   3 |    SORT AGGREGATE    |      |     1 |     4 |            |          |

              |*  4 |     TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |

              |   5 |  TABLE ACCESS FULL   | T2   |  2500 | 10000 |    11   (0)| 00:00:01 |

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

              Operation IDs 3 and 4 are the second scalar subquery from the case statement, and the intuitive response is that they should be at the same level of indent as the first scalar subquery.

               

              Regards

              Jonathan Lewis