Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: reference cursors

From: <Stephen.Lee_at_DTAG.Com>
Date: Fri, 30 Jan 2004 08:59:03 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B079091C6@dtagpo1.dtg.local>

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-----

As a speculative thought:
If the ref cursor is created through a text string, with dynamic execution, perhaps the parser is taking out the hints because it mistakenly recognises them as comments. (You should be able to see the actual text that arrives in the 10046 trace). If this is the case, you could experiment with the alternative format for hint/commenting to see if that bypasses the problem.

Hints can be wrapped by /*+ */
or preceded (as one liners) by --+
so you may have to play about with building strings including || chr(10) || and so on, toisolate the hint bit suitably.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  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:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

After playing around some, it looks like when the select is opened via a refcursor, the hints don't work. If I create a global temporary table, have the select table dump its load into the temporary table, then open the refcursor as select * from the temporary table, everything moves right along again. In more detail (because I'm playing around with this stuff): I created a row type, then created a table type, then create a variable of that table type; then do a bulk select into the table type variable. Now I wish to hell there was a way to pass a pointer to that table type variable as the refcursor, but I couldn't figure out a way. So I did a loop to insert the rows of the table type variable into the temporary table; then do the refcursor on the temporary table. I don't know if I will actually suggest this as a "solution". It's kind of Rube Goldberg-y and is bound to complicate application maintenance .. which is the last thing in the world this application needs.

Anybody got any better ideas (other than re-write the app)? If there is a way to pass a pointer to the table type variable without having to use the temporary table gadget, I'm all ears.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Jan 30 2004 - 08:59:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US