Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘cursor expression

The Pitfall of Cursor Expression

with one comment

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

  1. View merging is disabled(as pointed out at http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/opt_ops.htm)
  2. You could have excessive calls.
  3. 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.





Written by Dion Cho

February 25, 2009 at 12:19 pm

Follow

Get every new post delivered to your Inbox.

Join 58 other followers