RE: Tune for 'db file sequential read'

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).
    >
    

    ---