10046 tracing Scalar Subquery

I'm trying to get execution plan for the statement that includes scalar subquery, and I'm not getting tracing information relevent to scalar subquery.
My query is somewhat complicated, but the following simple SELECT demonstrates the problem.  

SELECT username, to_number(substr(data,1,10)) CNT,

         to_number(substr(data,11)) AVG

    FROM (        SELECT A.username, (SELECT to_char(count(*), 'fm0000000009') ||

                                    avg(object_id)

                              FROM all_objects B

                              WHERE B.owner = A.username) data

          FROM all_users A);

 

In 10046 trace I'm not getting STAT lines for this part of the query:  

(SELECT to_char(count(*), 'fm0000000009') ||

                                    avg(object_id)

                              FROM all_objects B

                              WHERE B.owner = A.username) data

 

Here is 10046 trace:  

PARSING IN CURSOR #8 len=363 dep=0 uid=34 oct=3 lid=34 tim=15311589822 hv=2544473847 ad='1dec0d10'

SELECT username, to_number(substr(data,1,10)) CNT,

         to_number(substr(data,11)) AVG

    FROM (        SELECT A.username, (SELECT to_char(count(*), 'fm0000000009') ||

                                    avg(object_id)

                              FROM all_objects B

                              WHERE B.owner = A.username) data

          FROM all_users A)

END OF STMT PARSE
#8:c=78125,e=81609,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=2,tim=15311589808

BINDS #8: EXEC #8:c=0,e=392,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=15311592223

WAIT #8: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0

FETCH
#8:c=1140625,e=1135023,p=0,cr=42989,cu=0,mis=0,r=1,dep=0,og=2,tim=153127 27953

WAIT #8: nam='SQL*Net message from client' ela= 1719 p1=1413697536 p2=1 p3=0

WAIT #8: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0

FETCH
#8:c=546875,e=552605,p=0,cr=10626,cu=0,mis=0,r=11,dep=0,og=2,tim=1531328 3392

WAIT #8: nam='SQL*Net message from client' ela= 849571 p1=1413697536 p2=1 p3=0

STAT #8 id=34 cnt=12 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=46 pr=0 pw=0 time=256 us)'

STAT #8 id=35 cnt=12 pid=34 pos=1 obj=0 op='NESTED LOOPS (cr=31 pr=0 pw=0 time=1350 us)'

STAT #8 id=36 cnt=12 pid=35 pos=1 obj=22 op='TABLE ACCESS FULL USER$
(cr=5 pr=0 pw=0 time=485 us)'

STAT #8 id=37 cnt=12 pid=35 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$
(cr=26 pr=0 pw=0 time=857 us)'

STAT #8 id=38 cnt=12 pid=37 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS#
(cr=2 pr=0 pw=0 time=284 us)'

STAT #8 id=39 cnt=12 pid=34 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$
(cr=15 pr=0 pw=0 time=411 us)'

STAT #8 id=40 cnt=12 pid=39 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS#
(cr=2 pr=0 pw=0 time=134 us)'
 

As you can see, there is no references to all_objects (or underlying table for this view).

And obviously, according to these STAT lines tkprof shows the following execution plan:  

call count cpu elapsed disk query current rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

Parse 1 0.07 0.08 0 5 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 1.68 1.68 0 53615 0 12

• ------ -------- ---------- ---------- ---------- ----------

  ---

total 4 1.76 1.76 0 53620 0 12  

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 34  

Rows Row Source Operation

• ---------------------------------------------------

     12 NESTED LOOPS (cr=46 pr=0 pw=0 time=256 us)

     12 NESTED LOOPS (cr=31 pr=0 pw=0 time=1350 us)

     12 TABLE ACCESS FULL USER$ (cr=5 pr=0 pw=0 time=485 us)

     12 TABLE ACCESS CLUSTER TS$ (cr=26 pr=0 pw=0 time=857 us)

     12 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=284 us)(object id 7)

     12 TABLE ACCESS CLUSTER TS$ (cr=15 pr=0 pw=0 time=411 us)

     12 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=134 us)(object id 7)  

So, the question is there any way to get "complete" execution plan for SELECT that includes scalar subquery?  

Igor Neyman, OCP DBA

ineyman_at_perceptron.com    

--
http://www.freelists.org/webpage/oracle-l