Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: reference cursors

Re: reference cursors

From: Jonathan Lewis <>
Date: Fri, 30 Jan 2004 16:44:59 -0000
Message-ID: <006601c3e750$6d6ac6a0$6702a8c0@Primary>

According to the 9.2 manual, the cardinality() hint should take a list of table aliases, and a cardinality. Then when that list of tables starts the join order, the cardinality of the join up to that point is what you have specified.

Consequently the four separate cardinality hints in your query are saying:

    if you start with t1, the cardinality from t1 is 10
    if you start with t2, the cardinality from t2 is 10
    if you start with t3, the cardinality from t3 is 10
    if you start with t4, the cardinality from t4 is 10
This may not be what you intend. As a consequence, it is possible that some other small effect relating to ref cursors or dynamic sql is causing a different table ordering, and a dramatic change in the statistics that Oracle is required to use.

L:ine 11 may be a big part of your problem. Oracle assumes the cardinality of a table cast() is 8,168 - but your run time stats show 2 rows returned.


Jonathan Lewis

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG - v$ and x$  March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:

Three-day seminar:
see ____UK___February

The Co-operative Oracle Users' FAQ

Interesting suggestion. I might fiddle with it some more. Here is what is in the 10046 level 12 trace file.

PARSING IN CURSOR #12 len=2181 dep=1 uid=19 oct=3 lid=19 tim=16595183616 hv=1672063353 ad='117f50f0'
SELECT /*+ NO_EXPAND INDEX (tmr TMR_LCT_FK_IDX) INDEX (tmri TMRC_MIN_DY_IDX) cardinality (t4, 10) cardinality (t3, 10) cardinality (t2, 10) cardinality (t1, 10) */ etc. etc.

And one can clearly see where the thing hits the wall: STAT #12 id=5 cnt=13180531 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=25497 r=4 w=0 time=30502912 us)'

STAT #12 id=14 cnt=13179320 pid=5 pos=2 obj=223485 op='INDEX RANGE SCAN TMRC_MIN_DY_IDX (cr=24202 r=0 w=0 time=20999168 us)'

When the hints work, the query does very little work.

You DO have to wonder just exactly what is a COLLECTION ITERATOR PICKLER FETCH. Is that a kosher pickler?

STAT #12 id=11 cnt=2 pid=10 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH (cr=0 r=0 w=0 time=0 us)'

-----Original Message-----

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jan 30 2004 - 10:44:59 CST

Original text of this message