Skip to Main Content
  • Questions

  • Explain plan with select list sub-query

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dave.

Asked: May 05, 2005 - 7:25 pm UTC

Last updated: June 06, 2005 - 7:38 am UTC

Version: 9,.2.0

Viewed 1000+ times

You Asked

We are still in development, so I don't have access to more recent Oracle versions. But what is happening here is that explain plan is not including access paths from sub-queries in a select list.
I have some very complex sub-queries that seem to be generating sub-optimal plans, but I can't tell what's happening.

Simple example:


SQL*Plus: Release 9.2.0.1.0 - Production on Thu May 5 13:55:08 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

test@TEST> create table test as select * from all_objects;

Table created.

test@TEST> exec dbms_stats.gather_table_stats( user, 'TEST');

PL/SQL procedure successfully completed.

test@TEST> select count(*) from test;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=83 Card=29025)


test@TEST> select (select count(*) from test) from dual;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=4072)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=4072)


tkprof:

select count(*)
from
test

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.31 9.45 812 816 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 9.45 812 816 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
29025 TABLE ACCESS FULL TEST


select (select count(*)
from
test) from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.01 0 0 0 0
Fetch 4 0.35 9.41 1624 1638 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.36 9.42 1624 1638 0 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL




and Tom said...

I have 9203 as my "oldest" 9ir2 database -- in 9203 the plan is:

select (select count(*)
from
test) from dual

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.05 0 406 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.06 0 407 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 145 (OPS$TKYTE)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=403 r=0 w=0 time=59500 us)
29239 TABLE ACCESS FULL OBJ#(33017) (cr=403 r=0 w=0 time=39853 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=127 us)


Now, that aside, you should be able to rip out the scalar subquery and replace any "push in" correlation variables with bind variables.

Eg:

if you have:


select deptno, (select count(*) from emp where emp.deptno = dept.deptno) cnt
from dept;

you should be able to run:

select count(*) from emp where emp.deptno = :x

and supply a value or two from the original query to see what you see.


Also, if you didn't know about scalar subquery caching, you could find it appears "magic". consider:


ops$tkyte@ORA9IR2> create or replace function f( p_str1 in varchar2, p_str2 in varchar2 ) return varchar2
2 as
3 begin
4 dbms_application_info.set_client_info( userenv('client_info')+1 );
5 return p_str1 || '.' || p_str2;
6 end;
7 /

Function created.

function counts how many times it was called...


ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select (select f(owner,object_type) from dual) from t;

27952 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
2 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
3108 consistent gets
383 physical reads
0 redo size
402996 bytes sent via SQL*Net to client
20992 bytes received via SQL*Net from client
1865 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
27952 rows processed

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
292

scalar subquery was invoked 292 times -- NOT once per row, but 292 times... However:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select (select f(owner,object_type) from dual)
2 from (select * from t order by owner, object_type);

27952 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
2 0 VIEW
3 2 SORT (ORDER BY)
4 3 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
798 consistent gets
0 physical reads
0 redo size
367620 bytes sent via SQL*Net to client
20992 bytes received via SQL*Net from client
1865 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
27952 rows processed

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
137

ops$tkyte@ORA9IR2> select count(distinct owner||'.'||object_type) from t;

COUNT(DISTINCTOWNER||'.'||OBJECT_TYPE)
--------------------------------------
137

by presorting the data and then invoking the scalar subquery, we can reduce the number of scalar subquery calls to the number of distinct inputs

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select (select f(owner,object_type) from dual)
2 from (select * from t order by dbms_random.random);

27952 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
2 0 VIEW
3 2 SORT (ORDER BY)
4 3 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1731 consistent gets
0 physical reads
0 redo size
645656 bytes sent via SQL*Net to client
20992 bytes received via SQL*Net from client
1865 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
27952 rows processed

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
448

ops$tkyte@ORA9IR2> edit test


And that last bit shows why it would appear "magic" -- it depends on the order of the data processed by the query.

There is a hash table where they cache the results (see </code> http://tinyurl.com/7ztl5 <code>) and if you avoid collisions (by sorting) you get the best "cache hit" but -- you pay the sort price.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Scalar caching- value change undetected?

Ravi, June 06, 2005 - 5:51 am UTC

A case when subquery caching may not be very good, say if the cached data value has changed before the next time its taken out of cache to be output, Oracle doesn’t seem to detect the change and re-run the Function.

Consider

We need two SQL * Plus sessions for this, running at the same time:

SESSION 1:

drop table t3;
create table t3 as select object_id from dba_objects
where rownum < 10;
truncate table t3;
insert into t3 select object_id from dba_objects
where rownum < 10;
commit;


Now in another session, say SESSION2 :

drop table t4;
create table t4 as select object_id from dba_objects
where rownum < 10;
insert into t4 select object_id from dba_objects
where rownum < 10;
commit;



create or replace function foo2 return number
as
a number(10);
begin
dbms_application_info.set_client_info( userenv('client_info')+1 );
select count(*) into a from t3;

dbms_lock.sleep(15);

return a;
end;

QRY1:

begin
dbms_application_info.set_client_info(0);
end;

select foo2 from t4 where rownum < 3;

Quickly move to Session 2 and do

delete t3 ;
commit;


Output

9
0

select userenv('client_info') from dual;

2

Function was executed twice as there was no caching.

Because data is not cached, as the return value of function changed, the output changed from 9 to 0, as all 9 T3 rows were available during first record execution of foo2 while they were deleted and none available during second execution.

Now still in Session 2 try:

Reload T3 so that it is expected to give same output as QRY1

truncate table t3;
insert into t3 select object_id from dba_objects
where rownum < 10;
commit;



begin
dbms_application_info.set_client_info(0);
end;

To have subquery caching QRY2:

select (Select foo2 from dual) from t4 where rownum < 3;

Quicky move to Session 1 and do


delete t3 ;
commit;

Come back to Session 2 to see output of QRY2:

Output

9
9


select userenv('client_info') from dual;

1

Looks like subquery caching is working and hence Oracle executed function just the once, looks like as with no caching, there was no way for Oracle to check it needs to re-run the Function again?

Question:

1)Is subquery caching not quite good when the data the function returns could change after values are cached?
2)Is this an issue that is expected and developers need to keep in mind or a problem? This because if caching is not used, we got ‘correct’ data?




Tom Kyte
June 06, 2005 - 7:38 am UTC

well, you would get very unpredicable results from that regardless -- caching or no caching.

do not ascribe to a non-procedural language such as SQL any sort of procedural concepts.

You have absolutely NO true control over the number of times a function is called in SQL. we are taking advantage of a implementation detail here.


select * from t where x = :x and y = f();


the function F() could be called once per row
could be called once per query
could be called for some of the rows in T after x=:x


and could do one of the above one day and a different one of the above the next day.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.