RE: Plea for Query Tuning Help

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 12 Sep 2006 19:18:51 -0400
Message-ID: <001301c6d6c1$cf774450$0c00a8c0@Thing1>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Tuesday, September 12, 2006 5:12 PM To: Mark.Bobak_at_il.proquest.com; charlottejanehammond_at_yahoo.com; oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Tuesday, September 12, 2006 4:50 PM To: charlottejanehammond_at_yahoo.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond Sent: Tuesday, September 12, 2006 4:31 PM To: oracle-l_at_freelists.org
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.

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_at_yahoo.com> 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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 12 2006 - 18:18:51 CDT