Posts Tagged ‘cursor expression’
The Pitfall of Cursor Expression
Cursor expression is one of the powerful but forgotten features of Oracle query. It has following syntax.
select c1, c2,
cursor(select * from t2 where t2 where c1 = t1.c1)
from t1;
Cursor expression is a very handy way of generating multidimensional output from RDBMS. But you should recognize following pitfalls and avoid them if necessary. The pitfalls of cursor expression are
- View merging is disabled(as pointed out at http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/opt_ops.htm)
- You could have excessive calls.
- Some confusion with sql_trace and plan statistics.
I would demonstrate above pitfalls with very simple test cases. Looks long, but you will find it very interesting.
1. Query without cursor expression. Note that inline view is successfully merged.
select /* without_cursor */
t1.c1, x.c2
from
t1, (select c1, c2 from t2) x
where
t1.c1 = x.c1
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 8 0.00 0.00 0 31 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.00 0.00 0 31 0 100
Rows Row Source Operation
------- ---------------------------------------------------
100 TABLE ACCESS BY INDEX ROWID T2 (cr=31 pr=0 pw=0 time=6620 us)
201 NESTED LOOPS (cr=23 pr=0 pw=0 time=4782 us)
100 INDEX FULL SCAN T1_N1 (cr=8 pr=0 pw=0 time=431 us)(object id 79292)
100 INDEX RANGE SCAN T2_N1 (cr=15 pr=0 pw=0 time=2145 us)(object id 79293)
2. Query with cursor expression. Note that we failed at view merging. The result is increased workloads with suboptimal execution plan.
select /* with_cursor */
t1.c1, x.c2, cursor(select * from dual)
from
t1, (select c1, c2 from t2) x
where
t1.c1 = x.c1
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 51 0.03 0.05 0 154 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53 0.03 0.05 0 154 0 100
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)
100 NESTED LOOPS (cr=154 pr=0 pw=0 time=6135 us)
100 VIEW (cr=53 pr=0 pw=0 time=1466 us)
100 TABLE ACCESS FULL T2 (cr=53 pr=0 pw=0 time=754 us)
100 INDEX RANGE SCAN T1_N1 (cr=101 pr=0 pw=0 time=3015 us)(object id 79292)
3. Query without cursor expression. Take a close look at the parse/execute/fetch values.
select
y.*
from
(select level from dual connect by level <= 1000) x,
(select * from t1 where rownum <= 10) y
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 668 0.21 0.18 0 3 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.21 0.19 0 3 0 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
10000 MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=147360 us)
1000 VIEW (cr=0 pr=0 pw=0 time=18409 us)
1000 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=11399 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=13 us)
10000 BUFFER SORT (cr=3 pr=0 pw=0 time=52983 us)
10 VIEW (cr=3 pr=0 pw=0 time=231 us)
10 COUNT STOPKEY (cr=3 pr=0 pw=0 time=160 us)
10 TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=94 us)
4. Query with cursor expression. The output is almost same as that of previous query, but…
Oops. No logical reads? What happened with this query?
select
cursor(select * from t1 where rownum <= 10)
from dual
connect by level <= 1000
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 501 0.29 0.22 0 0 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 0.29 0.22 0 0 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)
1000 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14289 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=12 us)
The problem is that the query in the cursor expression is executed as recursive query. Look at the parse/execute/fetch values and logical reads! This recursive query was parsed and executed 1000 times, whose value is sames as the rows fetched by main query. Very bad, isn’t it? This is one of the main performance problem of cursor expression.
SELECT "A2"."C1" "C1","A2"."C2" "C2"
FROM
"T1" "A2" WHERE ROWNUM<=10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1000 0.00 0.02 0 0 0 0
Execute 1000 0.09 0.04 0 0 0 0
Fetch 1000 0.32 0.27 0 3000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3000 0.42 0.35 0 3000 0 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=3 pr=0 pw=0 time=172 us)
10 TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=98 us)
5. Another annoying problem. The main query does not show *real* execution plan. See following.
select /* with_cursor2 */
cursor(
select
t1.c1, x.c2
from t1, (select c1, c2 from t2) x
where
t1.c1 = x.c1
)
from dual
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_N1 (cr=0 pr=0 pw=0 time=0 us)(object id 79292)
1 FAST DUAL (cr=0 pr=0 pw=0 time=13 us)
It looks as if the query in the cursor expression failed at view merging. But the actual execution plan from recursive query clearly says that it successfully merged the view! Looks like a bug, doesn’t it? This annoying phenomenon is observed as of 10gR2.
SELECT "A3"."C1" "C1","A2"."C2" "C2"
FROM
"T1" "A3", (SELECT "A4"."C1" "C1","A4"."C2" "C2" FROM "T2" "A4") "A2" WHERE
"A3"."C1"="A2"."C1"
Rows Row Source Operation
------- ---------------------------------------------------
100 TABLE ACCESS BY INDEX ROWID T2 (cr=28 pr=0 pw=0 time=7315 us)
201 NESTED LOOPS (cr=21 pr=0 pw=0 time=5606 us)
100 INDEX FULL SCAN T1_N1 (cr=7 pr=0 pw=0 time=1426 us)(object id 79292)
100 INDEX RANGE SCAN T2_N1 (cr=14 pr=0 pw=0 time=1905 us)(object id 79293)
6. The last one. Cursor expression does not go well with the plan statistics. Very disappointing.
UKJA@ukja102> select * from table
2 (dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 4um7hxd6fkkh7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ cursor( select t1.c1,
x.c2 from t1, (select c1, c2 from t2) x where t1.c1 =
x.c1 ) from dual
Plan hash value: 1702068269
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-----------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 0 | 100 | 0 |00:00:00.01 |
| 2 | VIEW | | 0 | 100 | 0 |00:00:00.01 |
| 3 | TABLE ACCESS FULL| T2 | 0 | 100 | 0 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 0 | 1 | 0 |00:00:00.01 |
| 5 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 |
-----------------------------------------------------------------------------
Oops! Oracle does not report the plan statistics of the query executed in the cursor expression. Why? Not sure. Maybe some implementation limit. Looks like Oracle does not sum up the workloads of recursive query in this special case. Another limitation of plan statistics besides the parallel execution.
Don’t forget above pitfalls when using cursor expression.
Dion Cho - Oracle Performance Storyteller 