RE: Plea for Query Tuning Help
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxxxxx>, <charlottejanehammond@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 17:12:16 -0400
If the rownum virtual table trick failed, my guess would be that Oracle
needs the value at parse time to get the good plan by peeking....
But I am impressed by the valiant efforts of the list. We're all candidates
for puzzlesolvers anonymous!
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bobak, Mark
Sent: Tuesday, September 12, 2006 4:50 PM
To: charlottejanehammond@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Plea for Query Tuning Help
One more idea:
with subq as (select id from my_table where rownum=1)
SELECT /*+ ordered use_nl(nv) */ nv.*
FROM subq sq, NASTY_VIEW nv
where sq.id = nv.id;
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"A human being should be able to change a diaper, plan an invasion,
butcher a hog, conn a ship, design a building, write a sonnet, balance
accounts, build a wall, set a bone, comfort the dying, take orders, give
orders, cooperate, act alone, solve equations, analyze a new problem,
pitch manure, program a computer, cook a tasty meal, fight efficiently,
die gallantly. Specialization is for insects." --Robert A. Heinlein
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charlotte Hammond
Sent: Tuesday, September 12, 2006 4:31 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Plea for Query Tuning Help
Hi All,
Thanks to all who have responded both on and off the list. A couple of
people have asked me to post the plan but it is huge (due to NASTY_VIEW)
so I'd rather not (at least yet!) as it'd probably be more off-putting
than anything else :-)
Essentially I just want the WHERE id = on NASTY_VIEW to treat the right
value the same if it's a bind or if it's coming from a sub-query. Many
good suggestions but no luck yet. I thought I'd post the results to see
if anyone can spot anything from them...
Here's an even simpler example:
The Good case again:
SELECT *
FROM NASTY_VIEW
WHERE id = :bind
--> < 1 sec, (CBO=410)
Any now some attempts to use a value from a SELECT.
1. Simple select:
SELECT *
FROM NASTY_VIEW
WHERE id = (SELECT :bind FROM dual)
--> long time (CBO=17378)
2. Try with a join instead:
SELECT *
FROM NASTY_VIEW nv, (SELECT :bind id FROM dual) b WHERE nv.id = b.id
--> long time (CBO=32731)
3. Try putting in a row number limitation / pseudo-column
SELECT *
FROM NASTY_VIEW nv, (SELECT :bind id, rownum rn FROM dual WHERE
rownum=1) b WHERE nv.id = b.id
--> long time (CBO=17388)
4. Try NO_MERGE hint
SELECT *
FROM NASTY_VIEW
WHERE id = (SELECT /*+ NO_MERGE */ :bind FROM dual)
--> long time (CBO=17378) (Same plan as 1)
(Same result if NO_MERGE hint in the outer SELECT)
5. Try FIRST_ROWS(n) hint
SELECT /*+ FIRST_ROWS(1) */
FROM NASTY_VIEW
WHERE id = (SELECT :bind FROM dual)
--> long time (CBO=6030) (Best yet but still very slow
compared to good case; other values of n were the same or worse)
6. Try hidden parameter tweaking
ALTER SESSION SET "_UNNEST_SUBQUERY"=FALSE; --> same as 1 ALTER SESSION
SET "_COMPLEX_VIEW_MERGING"=FALSE --> same as 1
Many thanks again for all your time.
Charlotte
> On 9/12/06, Charlotte Hammond
> <charlottejanehammond@xxxxxxxxx> wrote:
> >
> > How can I prevent this happening so that it
> "looks"
> > like the first "fantastic" query? I've tried
> messing
> > with hints (NO_MERGE and PUSH_SUBQ) but I don't
> know
> > enough to do so effectively. Statistics are full
> and
> > up to date. This is Oracle 9.2.0.6.
> >
> >
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
