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
> > 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
>
>
__________________________________________________
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
Received on Fri May 05 2000 - 08:14:55 CDT
