| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: reference cursors
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.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |