Re: Tune for 'db file sequential read'

as a clarification, cost IS NOT an option for optimization_mode in init.ora

Rule, Choose, First_rows or all_rows.

joe

Rajagopal Venkataramany 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----
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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
> > 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).
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: Rajagopal Venkataramany
> INET: rajagopalvr_at_hotmail.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
Received on Thu May 04 2000 - 14:25:37 CDT