RE: Plea for Query Tuning Help
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mwf@xxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxxxxx>, <charlottejanehammond@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 19:18:51 -0400
Charlotte reported back that there are no histograms, so it is not peeking.
But it does know that it is looking for exactly one id (which it does not
know in the join despite the rownum trick, 'cause even with a stopkey plan
oracle apparently does not figure out that it will be solving for exactly
one id on the join.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Tuesday, September 12, 2006 5:12 PM
To: Mark.Bobak@xxxxxxxxxxxxxxx; charlottejanehammond@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: Plea for Query Tuning Help
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
