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@xxxxxxxxxxxxxx

 

 



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

Other related posts: