Re: OPT_PARAM
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Peter Barnett <regdba@xxxxxxxxx>
- Date: Tue, 8 Dec 2009 08:07:25 +0100
Hi
The code wasnt really complicated.
It's SQL such as this one:
select ....
from t1
where c1 in (select v1.c2 from v1
where v1.c3 in (select .......... from v2 minus select
.......... from v3))
When I changed the code to
select ....
from t,
(select v1.c2 from v1
where v1.c2 in (select .......... from v2 minus select .......... from
v3)) t2
where t1.c1 = t2.c2
We got the performance back!
Seems it doesnt like nested subqueries because we there were 4 queries such
as this with same problem
Thanks
--
LSC
On Mon, Dec 7, 2009 at 4:31 PM, Peter Barnett <regdba@xxxxxxxxx> wrote:
> We had a similar situation two years ago. We were even preparing to spend
> big bucks for someone to come on site from Oracle consulting services. He
> somewhat diplomatically suggested that a) we were wasting the money we spent
> upgrading to the next version of Oracle if we wanted to use the 9.2
> optimizer and, b) the most likely culprit was poorly crafted SQL which it
> was our responsibility to fix.
>
> It took nearly a year to complete the work, but we fixed the code.
>
> Pete Barnett
> Database Technologies Lead
> Regence
>
>
> --- On Tue, 11/24/09, LS Cheng <exriscer@xxxxxxxxx> wrote:
>
> > From: LS Cheng <exriscer@xxxxxxxxx>
> > Subject: Re: OPT_PARAM
> > To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
> > Cc: "Oracle Mailinglist" <oracle-l@xxxxxxxxxxxxx>
> > Date: Tuesday, November 24, 2009, 11:32 AM
> > Hi
> >
> > I saw that list yesterday but I knew it is not correct list
> > because I have tested some parameters which are not listed
> > in the documentation and they works.
> >
> > I did try without quotes which does not work as expected
> > since when specifying O_F_E parameter we have to quote the
> > numbers.
> >
> >
> > Thanks!
> >
> > --
> > LSC
> >
> >
> > On Tue, Nov 24, 2009 at 7:50 PM,
> > Allen, Brandon <Brandon.Allen@xxxxxxxxxxx>
> > wrote:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Not sure if
> > you already saw this, but here is the list of
> > “supported” parameters, which another member referred to
> > earlier, however as Dion mentioned, the
> > hint seems to work for more than just these supported
> > parameters, but apparently you just have to guess as to
> > which ones work and which ones don’t – I couldn’t find
> > a list anywhere on the Internet or on MOS.
> >
> >
> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#SQLRF51119
> >
> >
> > “This
> > hint is valid only for the following parameters:
> > OPTIMIZER_DYNAMIC_SAMPLING,
> > OPTIMIZER_INDEX_CACHING,
> > OPTIMIZER_INDEX_COST_ADJ,
> > OPTIMIZER_SECURE_VIEW_MERGING, and
> > STAR_TRANSFORMATION_ENABLED”
> >
> > Just one more
> > thing – did you try it without putting 9.2.0 in quotation
> > marks?
> >
> >
> >
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > On Behalf Of LS Cheng
> >
> >
> >
> >
> >
> > Does anyone know if there is a list which contains
> > parameters supported by OPT_PARAM :-?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Privileged/Confidential Information may be
> > contained in this message or attachments hereto. Please
> > advise immediately if you or your employer do not consent to
> > Internet email for messages of this kind. Opinions,
> > conclusions
> > and other information in this message that do not relate
> > to the official business of this company shall be understood
> > as neither given nor endorsed by it.
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
Other related posts:
