- Build your query as normal leaving out the first column.
- Create an experssion that includes the hint(s) you want and the first
column. For example if your first column was "A.EMPLID", the expression
would be "/*+ RULE*/ A.EMPLID". Then look at the generated sql. You'll see
the hint right where you want it.
- Drag the expression over to the fields list.
- Move the expression up to the first postion
--
Chuck Hamilton
QVC Inc.
Enterprise Technical Services
Oracle DBA
Deepak Sharma
<sharmakdeep@ To: CHUCK HAMILTON/QVC_at_QVC, ORACLE-L_at_fatcity.com
yahoo.com> cc: Deepak Sharma <sharmakdeep_at_yahoo.com>
Ext: NA Subject: RE: Tune for 'db file sequential read'
05/05/00
01:10 PM
Chuck,
Could you elaborate on how you would trick the query
tool by passing Hint as the 1st column, I'm not quite
getting it. This sounds interesting and maybe a viable
overall solution.
Thanks
Deepak
- CHUCK_HAMILTON_at_qvc.com wrote:
>
> We ran into the same problem with PS HRMS. Coverting
> the database to CBO
> and analyzing improved the panel response time (OLTP
> type of queries)
> immensely. But it destroyed performance on many SQRs
> and queries run by the
> query tool. We ended up doing serveral things. For
> some queries we created
> views with embedded hints and tuned the SQL to speed
> up access. We also
> discovered a way to trick the query tool into
> passing hints to the DB. If
> you make the first column a calculated field or
> expression ( I forget what
> they call it ), you could embed hints in it that the
> DB would recognize.
> For other jobs that we couldn't get to run under CBO
> well at all we created
> a 2nd instance that gets refreshed every night on a
> DSS system and ran that
> one under RBO.
> --
> Chuck Hamilton
> QVC Inc.
> Enterprise Technical Services
> Oracle DBA
>
>
>
>
>
> Deepak Sharma
>
>
> <sharmakdeep@ To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> yahoo.com> cc:
> (bcc: CHUCK HAMILTON/QVC)
>
> Ext: NA Subject:
> RE: Tune for 'db file sequential read'
>
> Sent by:
>
>
> root_at_fatcity.
>
>
> com
>
>
>
>
>
>
>
>
> 05/04/00
>
>
> 06:05 PM
>
>
> Please
>
>
> respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> I am relatively new to the client here, so I'm
> picking
> up things from where the previous DBA had left. The
> PS
> financial development had/has been using RULE, while
> production is running under CHOOSE. Now, the PS
> Financial is on track of merging with PS Order
> Management, which BTW uses CHOOSE. My suggestion of
> changing their development also to CHOOSE so as to
> have everything in Sync., AND Analyzing the entire
> Schema, has created gains in some queries, but it
> has
> also degraded some (one of them is where I suggested
> using RULE hint). So, I believe the change is going
> to
> take its own course, or does someone has a quick
> suggestion how to go about this transition from RULE
> to CHOOSE so as to cause minimal changes to the
> code.
>
> Thanks,
> Deepak
>
> --- Rajagopal Venkataramany
> <rajagopalvr_at_hotmail.com>
> wrote:
> > Hi Deepak,
> >
> > Try to create histograms whenever you analyze.
> > This can improve
> > CBO plan in a long way.
> >
> > If the application you are using has been
> > specifically tuned for RULE
> > then you can attempt the following :
> >
> > 1. The optimizer Mode in Init.ora can be changed
> > to RULE if most of
> > your application(s) are tuned for RULE. Else
> > this suggestion would
> > not help you.
> >
> > 2. You can set a session to work on RULE based
> > with the Init.ora
> > being set as COST or CHOOSE.
> >
> > 3. If the default mode is COST or CHOOSE then
> use
> > RULE hint to force
> > RBO wherever needed.
> >
> > ** If it is possible for you to provide a
> specific
> > SQL with the
> > background details about the indexes
> available,
> > table volume,
> > growth etc, I think we can come to a
> conclusion
> > better.
> >
> > Regards
> > Rajagopal Venkataramany
> >
> > ----Original Message Follows----
> > From: Deepak Sharma <sharmakdeep_at_yahoo.com>
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > Subject: RE: Tune for 'db file sequential read'
> > Date: Thu, 04 May 2000 10:56:24 -0800
> >
> > I found out that the explain plan showed the
> > instance
> > on which the query was running faster was RULE
> > based.
> > Adding a RULE hint to the SQL on the slower
> instance
> > overcame the performance issue. We then tried
> > Analyzing ALL the tables in that query (under
> > CHOOSE).
> > The query didn't improve. So, the only solution is
> > to
> > use RULE hint. It worked in this case, but is
> there
> > any other solution. It is a peoplesoft
> environment,
> > where I believe application such as Crystal
> Report,
> > work better under RULE. The problem, however, is
> our
> > production is running under CHOOSE, so does it
> mean
> > we'll have to apply RULE hint at lots of places
> ???
> >
> > -- Deepak
> >
> > --- "Khedr, Waleed" <Waleed.Khedr_at_FMR.COM> wrote:
> > > I think the problem is doing thousands of index
> > > scans (unique or range
> > > scan).
> > > Doing thousands of random single block I/O is
> > very
> > > sensitive to the
> > > performance of the disk system, the memory
> cache
> > on
> > > the top of the disk
> > > system, the structure of the index and the size
> > of
> > > buffer cache.
> > >
> > > Increasing the buffer cache could help but will
> > be
> > > very limited.
Received on Fri May 05 2000 - 12:30:51 CDT
