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 :
- 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.
- You can set a session to work on RULE based with the Init.ora
being set as COST or CHOOSE.
- 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
> 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).
Received on Thu May 04 2000 - 13:33:09 CDT
