scalar subqueries getting rewritten to use sort aggregate?
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
- Date: Mon, 27 Dec 2010 15:59:20 -0500
10.2.0.4, default optimizer parameters.
I've come across many cases where queries that call a function in the select
statement executes the function for each row returned. What I did not
expect was Oracle not performing the same action when dealing with a scalar
subquery. According to the plans I'm receiving, the scalar subquery has
been rewritten and merged into the main query. I wasn't expecting this
behavior and it's making a performance issue rather difficult to diagnose.
Here's an oversimplified example of the behavior. Function cube_salaries
simply returns the sum of the cubed salaries. My simplified query contains
a scalar subquery and a function call. I expected both the scalar subquery
and the function's sql to be run once for each of the rows in dept. I've
been reading about subquery caching but I wouldn't expect this to be the
case because I'm using a different value for deptno each time the scalar
subquery executes.
Obviously, the queries I'm trying to troubleshoot are far more complicated
than this one but I think the code below explains the behavior I'm
experiencing:
create or replace function cube_salaries(in_deptno in number)
return number
is
cube_salary number := 0;
begin
select sum(power(sal,3))
into cube_salary
from emp e
where e.deptno = in_deptno;
return cube_salary;
end;
/
select dname
, (select sum(sal) from emp z where z.deptno = a.deptno) sal_sum
, cube_salaries(deptno)
from dept a;
select dname
, (select sum(sal) from emp z where z.deptno = a.deptno) sal_sum
, cube_salaries(deptno)
from dept a
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 36 0
4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.01 0.01 0 36 0
4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT AGGREGATE (cr=28 pr=0 pw=0 time=354 us)
14 TABLE ACCESS FULL EMP (cr=28 pr=0 pw=0 time=279 us)
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=136 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 2 0.00
0.00
SQL*Net message from client 2 0.00
0.00
********************************************************************************
SELECT SUM(POWER(SAL,3))
FROM
EMP E WHERE E.DEPTNO = :B1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 4 0.00 0.00 0 0 0
0
Fetch 4 0.00 0.00 0 28 0
4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 9 0.00 0.00 0 28 0
4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT AGGREGATE (cr=28 pr=0 pw=0 time=448 us)
14 TABLE ACCESS FULL EMP (cr=28 pr=0 pw=0 time=317 us)
Since a SORT AGGREGATE operation is performed, does that mean Oracle is
rewriting the scalar subquery, minus the parameters(deptno) and then merging
it into the query results, similar to a join?
Other related posts:
