RE: Tune for 'db file sequential read'

Yes there are other options. You can tinker with the optimizer_index_cost_adj parameter. You can add indexes. You can compute histograms if you aren't using bind variables(I don't know if Crystal reports does).

• Deepak Sharma <sharmakdeep_at_yahoo.com> wrote:
  > 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
  > > indexes, percentage busy, queue
  > > length, etc.
  > > Separate heavily used indexes on separate disks.
  > >
  > > Also your process could be using index scan on the
  > > slow system while it is
  > > using FTS on the fast one.
  > >
  > >
  > > Regards,
  > >
  > > Waleed
  > >
  > > -----Original Message-----
  > > Sent: Wednesday, May 03, 2000 2:07 PM
  > > To: Multiple recipients of list ORACLE-L
  > >
  > >
  > > On querying the v$session_event, I could see one
  > > process taking a very large wait time. The process
  > > takes about 4 Hrs to complete on *this* instance
  > > (A),
  > > whereas it takes a few minutes on another
  > > instance(B)
  > > with almost same amount of data. The Metalink pages
  > > suggest to test by increasing DB_BLOCK_BUFFERS, but
  > > the interesting thing is that instance A's SGA is
  > > 128M, whereas instance B's is 44M. Any suggestions ?
  > >
  > > __________________________________________________
  > > Do You Yahoo!?
  > > Send instant messages & get email alerts with Yahoo!
  > > Messenger.
  > > http://im.yahoo.com/
  > > --
  > > Author: Deepak Sharma
  > > INET: sharmakdeep_at_yahoo.com
  > >
  > > Fat City Network Services -- (858) 538-5051 FAX:
  > > (858) 538-5051
  > > San Diego, California -- Public Internet
  > > access / Mailing Lists
  > >
  > --------------------------------------------------------------------
  > > To REMOVE yourself from this mailing list, send an
  > > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > > ORACLE-L
  > > (or the name of mailing list you want to be removed
  > > from). You may
  > > also send the HELP command for other information
  > > (like subscribing).
  > > --
  > > Author: Khedr, Waleed
  > > INET: Waleed.Khedr_at_FMR.COM
  > >
  > > Fat City Network Services -- (858) 538-5051 FAX:
  > > (858) 538-5051
  > > San Diego, California -- Public Internet
  > > access / Mailing Lists
  > >
  > --------------------------------------------------------------------
  > > To REMOVE yourself from this mailing list, send an
  > > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > > ORACLE-L
  > > (or the name of mailing list you want to be removed
  > > from). You may
  > > also send the HELP command for other information
  > > (like subscribing).
  > >
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Send instant messages & get email alerts with Yahoo! Messenger.
  > http://im.yahoo.com/
  > --
  > Author: Deepak Sharma
  > INET: sharmakdeep_at_yahoo.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
  > San Diego, California -- Public Internet access / Mailing Lists
  > --------------------------------------------------------------------
  > To REMOVE yourself from this mailing list, send an E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).
  >
  

  ---