RE: Plea for Query Tuning Help

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 12 Sep 2006 16:50:20 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF2709831236@AABO-EXCHANGE02.bos.il.pqe>

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.

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_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 Received on Tue Sep 12 2006 - 15:50:20 CDT