RE: Tune for 'db file sequential read'

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.
  > > > Check the performance of the disks that have