Wow, this one is really stubborn, eh? ;-)
--
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: Charlotte Hammond [mailto:charlottejanehammond_at_yahoo.com]
Sent: Tuesday, September 12, 2006 5:13 PM
To: Bobak, Mark; oracle-l_at_freelists.org
Subject: [SPAM] RE: Plea for Query Tuning Help
Importance: Low
Hi Mark,
Thanks for the idea, yet another different plan but sadly CBO=19223 and
it runs "forever" :-(
Thanks
Charlotte
--- "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> wrote:
> 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
>
>
>
__________________________________________________
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
Received on Tue Sep 12 2006 - 16:13:13 CDT
