RE: [SPAM] RE: Plea for Query Tuning Help
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Charlotte Hammond" <charlottejanehammond@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 17:13:13 -0400
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@xxxxxxxxx]
Sent: Tuesday, September 12, 2006 5:13 PM
To: Bobak, Mark; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> 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@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
>
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: [SPAM] RE: Plea for Query Tuning Help
