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 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: