Oracle-L: RE: converting to the cost based optimizer

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 21 Nov 2002 09:29:04 -0800
Message-ID: <F001.005090DC.20021121092904@fatcity.com>

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 <http://www.hotsos.com/training/clinic> , Dec 9-11
Honolulu
- Hotsos Clinic 101 <http://www.hotsos.com/training/clinic101> , Jan 7-9
Knoxville
- Steve Adams's <http://miracleas.dk/events/MMC2003/invitation.html>
Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium> on
OracleR System Performance, Feb 9-12 Dallas

-----Original Message-----
Rajendra
Sent: Thursday, November 21, 2002 10:39 AM To: Multiple recipients of list ORACLE-L

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_at_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-----
Sent: Thursday, November 21, 2002 11:22 AM To: ORACLE-L_at_fatcity.com
Cc: Jamadagni, Rajendra

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 <ORACLE-L_at_fatcity.com>    
                    <Rajendra.Jamadagni       cc:

                    @espn.com>                Subject:     RE:
converting to the cost based optimizer                
                    Sent by:

                    root_at_fatcity.com

 

 

                    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.

As we all know it affects cost calculations and higher the value CBO thinks FTS is cheaper.
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

-----Original Message-----
Sent: Thursday, November 21, 2002 9:54 AM To: Multiple recipients of list ORACLE-L

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)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Nov 21 2002 - 11:29:04 CST