Forum Stats

  • 3,826,742 Users
  • 2,260,702 Discussions
  • 7,897,067 Comments

Discussions

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

asy
asy Member Posts: 285
edited Oct 14, 2014 2:57PM in General Database Discussions

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

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond

    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

  • asy
    asy Member Posts: 285
    edited Oct 14, 2014 2:35PM

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond

    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

This discussion has been closed.