Oracle Database Discussions

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

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.

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