Message-ID: <F001.004AC688.20020806102824@fatcity.com>
Date: Tue, 06 Aug 2002 10:28:24 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Jared.Still@radisys.com
Subject: RE: wait event:controlfile parallel write and sql*net more data f
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

He Dennis,

I can get a 100% hit ratio too!

See:

                          LOGICAL  I/O PHYSICAL I/O       BLOCK
USERNAME   SYS_ID PROCESS       BLOCKS       BLOCKS     CHANGES IDLE TIME  
HITRATIO
---------- ------ ------- ------------ ------------ ----------- 
----------- --------
JKSTILL    15     21092      1,277,838          311         304 
00:00:00:00 99.98


OK, so it's only 99.98.

Here's how:

drop table c1;
drop table c2;

create table c1 as
select owner, table_name, tablespace_name
from dba_tables
where rownum < 10001
/

create table c2 as
select owner, table_name, tablespace_name
from dba_tables
where rownum < 10001
/

select
   c1.owner
   , c1.table_name
   , c1.tablespace_name
   , c2.owner
   , c2.table_name
   , c2.tablespace_name
from c1, c2
/

If I run this every 5 minutes from DBMS_JOB, I can keep my system Hit 
Ratio
above 98%, and I won't need to do any tuning. 

;^)

Jared


DENNIS WILLIAMS <DWILLIAMS@LIFETOUCH.COM>
Sent by: root@fatcity.com
08/06/2002 09:13 AM
Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
        cc: 
        Subject:        RE: wait event:controlfile parallel write and sql*net more data f


Chaos
  Oracle continually writes to all copies of the control file. The thing 
in
your report that jumps out at me is that you have zero (count 'em zero)
physical reads. You must have succeeded in caching all your data in 
memory.
Congratulations. Also you seem to have few writes. My guess is that
controlfile parallel write is bubbling to the top in the absence of other
waits. Not a bad situation. You may want to look at add other disks in the
future, depending on how important recoverability is to this instance. If
you were to lose this disk, it might be difficult to recover this 
database.

Dennis Williams 
DBA 
Lifetouch, Inc. 
dwilliams@lifetouch.com 


-----Original Message-----
Sent: Tuesday, August 06, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L
from db-link.


hi, dbas:
        I make a statspack report on one of my database and found a wait
event that i did not see quite often.
        This is a query intensive database, all 6 tables are snapshot from 
a
master site and refresh every 1 hour.All other activity is query the 
tables
and get a lot of data out of the database to middleware.
        The following is the statspack,I looked at the Oracle reference 
and
searched google for control file parallel write, but did not get much
valuable information.
        Hope someone here can help me. Does this event really have adverse
impact on database performance, and how to tune it.I have three 
controlfiles
on the same disk(only one disk in the server).

(One day activity)
                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:                    1238 06-Aug-02 10:43:39       30
   End Snap:                    1239 06-Aug-02 21:24:49       30
    Elapsed:                                     641.17 (mins)

Cache Sizes
~~~~~~~~~~~
           db_block_buffers:     156672          log_buffer:
2097152
              db_block_size:       8192    shared_pool_size:
157286400

Load Profile
~~~~~~~~~~~~ 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS@LIFETOUCH.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@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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still@radisys.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@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).