RE: duplexing redos and archives

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Wed, 07 Aug 2002 10:34:45 -0800
Message-ID: <F001.004ADD90.20020807103445@fatcity.com>

Cary,

> I haven't run "truss" on LGWR lately, but it was my understanding from
> talking to some of the guys who wrote it that LGWR actually does some
> read operations to ensure integrity of the writes (at least
> when you let
> Oracle do the redo multiplexing inside the kernel).

Picking up on the above subject: Stats from a high volume 7.3.4 Db [shame! Our biggest is still at 7.3 :( ]

Unnecessary details removed:

 OraSid Ser# OS Pid OS User OraUser Status Schema ------- ------- --------- -------- ------------- ---------- ------------

Program                                          Logon Time
------------------------------------------------ ------------------
      4       1 3553      oracle                 ACTIVE     SYS
oracle_at_saqhdp01 (LGWR)                           27-JUL-02 13:41:45

>From session details (V$SESSION_EVENT)

SQL> @sess_dtls
Enter value for sid_to_check: 4
old 3: from v$session_event where sid = &&sid_to_check new 3: from v$session_event where sid = 4

EVENT                                TOT_WAITS TOT_TMOUTS TIME_WAITED

AV_WAIT

----------------------------------- ---------- ---------- -----------
----------
log file parallel write                1213097          0     1691686
1.3945
latch free                                6764       6764        9143
1.3517
control file sequential read              5438          0        2723
.5007
control file parallel write               2966          0        2632
.8874
log file single write                     1054          0        1361

1.2913
*** The one of interest! -------

log file sequential read                   540          0        2517

4.6611

*** ----------------------------
rdbms ipc reply                            258          0        8700
33.7209
db file sequential read                    210          0           9
.0429
db file single write                       210          0          19
.0905
enqueue                                     18         12        4598

255.4444

Relevant details from V$SESSTAT

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo wastage                                                      294527368
redo writer latching time                                             11550
redo writes                                                         1213099
redo blocks written                                                41564631
redo write time                                                     1691943
OS Bytes from Logical reads                                         4059811

Relevant details from V$SYSTEM_EVENT ( Note that the *ONLY* 'log file sequential read' event in the whole system was from LGWR, evident from a comparison of the TOTAL_WAITS)

SQL> @system_event

EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED

AV_WAIT

------------------------------ ----------- -------------- -----------
----------
log file parallel write            1213239              0     1691802
1.39445072
control file parallel write          66989              0       88863
1.32653122
db file single write                 50112              0       30587
.610372765
log file single write                 1054              0        1361
1.29127135
log file sequential read               540              0        2517

4.66111111

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Disappointments are inevitable in Life, but discouragement is optional. You decide!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Wednesday, August 07, 2002 9:50 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: duplexing redos and archives
>
>
> I haven't run "truss" on LGWR lately, but it was my understanding from
> talking to some of the guys who wrote it that LGWR actually does some
> read operations to ensure integrity of the writes (at least
> when you let
> Oracle do the redo multiplexing inside the kernel).
>
> <aside>If this is true, then of course it's one more reason
> *not* to use
> RAID 5 for your online redo log files. If it's true, then redo log I/O
> is not truly sequential, contrary to what most people say.</aside>
>
> I don't have a high-volume-of-DML system to test this week, but the
> truss experiment should be interesting.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
> Honolulu
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
> - Next event: NCOAUG Training Day, Aug 16 Chicago
>
>
>
> -----Original Message-----

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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 Wed Aug 07 2002 - 13:34:45 CDT