RE: converting to the cost based optimizer

Cary Millsap Thu, 21 Nov 2002 10:12:23 -0800

Title: RE: converting to the cost based optimizer

Are you sure TIMED_STATISTICS = TRUE?

I don’t have time right now to check V$FIXED_VIEW_DEFINITION, but check how the AVERAGE_WAIT column is calculated. Just from querying V$SYSTEM_EVENT (I have 9iR1, by the way, not R2), it looks like TIME_WAITED is still rounded to centiseconds, and TIME_WAITED_MICRO is listed in unadulterated microseconds (for systems with usec or better gettimeofday resolution). The answer you probably want is

(TIME_WAITED_MICRO/TOTAL_WAITS)/10**6 seconds

instead of AVERAGE_WAIT anyway.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Dec 9–11 Honolulu
- Hotsos Clinic 101, Jan 7–9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13–15 Copenhagen
- 2003 Hotsos Symposium on Oracle^® System Performance, Feb 9–12 Dallas

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni, Rajendra
Sent: Thursday, November 21, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: converting to the cost based optimizer

No .... I should have clarified ... so far we have made changes to o_i_c_a parameter at session level only ... we haven't dared it change at system level. I read through Tim's paper ('Searching for ...') and try to came up with an appropriate value, but for my db this is the (always) scenario ...

oraclei@ariel-NCS2> sys

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 21 11:36:43 2002

Connected.
SQL> SELECT EVENT, AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'db file s%';

EVENT                                                            AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file sequential read                                                     0
db file scattered read                                                      0
db file single write                                                        0

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

So, looks like until we hit 9202 we can't use this ratio (sequential/scattered) reads... does anyone know of a equiv sql using x$ tables ??

Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 11:22 AM
To: [EMAIL PROTECTED]
Cc: Jamadagni, Rajendra
Subject: RE: converting to the cost based optimizer

Raj,

I'm just curious whether you also have reset your optimizer_index_
parameters in these databases where you have changed and/or are considering
changing the db_file_multiblock_read_count? In particular, has it been set
on the production database where you set db_file_multiblock_read_count to
4?

Thanks,

Cherie

                    "Jamadagni,
                    Rajendra"                 To:     Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
                    <Rajendra.Jamadagni       cc:
                    @espn.com>                Subject:     RE: converting to the cost based optimizer
                    Sent by:
                    [EMAIL PROTECTED]


                    11/21/02 09:40 AM
                    Please respond to
                    ORACLE-L

Cherie,

We are actually stuck at a value of 32 ... don't know how it got in there,
but it was probably us who made the mistake. That's how we tested it and
that's how we are staying put. We have initiated a test process to bring it
down to a value of 4 (most likely candidate) or 8. There are more than one
reasons to do this.

1. As we all know it affects cost calculations and higher the value CBO
thinks FTS is cheaper.
2. On AIX RAC, it also affects GC traffic, and we were looking at a high
number of
   'socket buffer overflows' (visible through netstat -p udp) which
indicated
   GC traffic and buffer writing problems. The solution is to raise the
udp_rcvsize
   and udp_sendsize to a higher value or reduce db_file_mb_count parameter
to a
   respectable value of 4 or 8.

Also as we are watching the application and see that it is choosing wrong
indexes with current setting of 32, and choosing different indexes at the
setting of 4. We have seen this behavior in multiple cases. So we are
leaning towards a value of 4. But as are nowhere near that as of now.

We did however change this parameter on one of the production instances, so
far we haven't seen huge performance changes, the global cache traffic is
much more manageable and we don't see as many socket buffer overflows ..

Bottom line, in the instance where we made the change to 4, it not faster,
but it is behaving better (i.e. choosing right indexes) ... We did 10046
traces before and after the changes, looked at explain plans for critical
processes and found that after bringing down the variable, things are
better. How much you may ask? I don't know ... we haven't been able to
quantify that 'how much?' yet. Some day we might, but at the moment other
fires are keeping us busy. And the d^Hmanagement hasn't asked us that
question yet as well.

And our db is a OLTP instance (all the way), I know these methods are no
where near as scientific, but based on our observation .. it works for us.

Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: converting to the cost based optimizer

Raj,

Can you elaborate on the process that you used to determine the optimal
value for db_file_multiblock_read_count?

We are doing some performance testing on a new generation of our product
with some new databases.   We increased our database block size from 8k to
16k (on Sun Solaris 2.6).    db_file_multiblock_read_count is defaulted to
8.   I wonder if we need to half the value to 4.   We have both a small
OLTP database and a medium-size DSS database that are on 9.2.0.1.

I've read through the documentation on db_file_multiblock_read_count that I

can find on Metalink but I can't find any specifics on calculating what
value it should optimally be set at or how to test if it is correct.

What method did you use for setting the value and then for testing for
optimal afterwards?

Thanks for sharing your experience with us.

Cherie Machler
Oracle DBA
Gelco Information Network (See attached file: ESPN_Disclaimer.txt)

RE: converting to the cost based optimizer

Reply via email to