Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: duplexing redos and archives

RE: duplexing redos and archives

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Wed, 07 Aug 2002 19:37:16 -0800
Message-ID: <F001.004AE819.20020807193716@fatcity.com> 





Cary/John/All,



I took a 10046 trace of LGWR, and observed that the 'log file sequential
read' event is posted by LGWR at log file switch, while control file
read/write occured before and after the log switch. 



Here is a trace of LGWR just about when log file switch took place: 


WAIT #0: nam='log file parallel write' ela= 4 p1=2 p2=526 p3=4
WAIT #0: nam='rdbms ipc message' ela= 5 p1=269 p2=0 p3=0
WAIT #0: nam='log file parallel write' ela= 6 p1=2 p2=524 p3=2
WAIT #0: nam='rdbms ipc message' ela= 1 p1=256 p2=0 p3=0
WAIT #0: nam='log file parallel write' ela= 7 p1=2 p2=524 p3=4
WAIT #0: nam='LGWR wait for redo copy' ela= 0 p1=0 p2=0 p3=0
WAIT #0: nam='log file parallel write' ela= 1 p1=2 p2=506 p3=2
WAIT #0: nam='log file parallel write' ela= 2 p1=2 p2=18 p3=2
WAIT #0: nam='rdbms ipc message' ela= 0 p1=239 p2=0 p3=0
WAIT #0: nam='rdbms ipc message' ela= 1 p1=239 p2=0 p3=0
WAIT #0: nam='log file parallel write' ela= 2 p1=2 p2=524 p3=4
WAIT #0: nam='rdbms ipc message' ela= 1 p1=234 p2=0 p3=0
WAIT #0: nam='log file parallel write' ela= 1 p1=2 p2=6 p3=2
WAIT #0: nam='rdbms ipc message' ela= 1 p1=232 p2=0 p3=0
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=2 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=5 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=7 p3=1
WAIT #0: nam='control file parallel write' ela= 1 p1=3 p2=3 p3=3
WAIT #0: nam='control file parallel write' ela= 1 p1=3 p2=3 p3=3
WAIT #0: nam='log file parallel write' ela= 1 p1=2 p2=294 p3=2
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=8 p3=1
WAIT #0: nam='control file parallel write' ela= 1 p1=3 p2=3 p3=3
WAIT #0: nam='control file sequential read' ela= 1 p1=0 p2=55 p3=1
WAIT #0: nam='file identify' ela= 0 p1=0 p2=0 p3=0
WAIT #0: nam='file identify' ela= 0 p1=0 p2=0 p3=0
WAIT #0: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #0: nam='log file single write' ela= 2 p1=0 p2=1 p3=1
WAIT #0: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #0: nam='log file single write' ela= 2 p1=1 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=7 p3=1
WAIT #0: nam='log file sequential read' ela= 2 p1=0 p2=1 p3=1
WAIT #0: nam='log file sequential read' ela= 1 p1=1 p2=1 p3=1
WAIT #0: nam='log file single write' ela= 1 p1=0 p2=1 p3=1
WAIT #0: nam='log file single write' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 1 p1=3 p2=3 p3=3
WAIT #0: nam='control file sequential read' ela= 1 p1=0 p2=209 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=3 p2=3 p3=3
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=7 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=5 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=3 p2=3 p3=3
WAIT #0: nam='control file parallel write' ela= 0 p1=3 p2=3 p3=3







-----Original Message-----


Sent: Wednesday, August 07, 2002 6:48 PM
To: Multiple recipients of list ORACLE-L




Well, there you go: LGWR reads. Thanks, John!




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-----


Kanagaraj


Sent: Wednesday, August 07, 2002 1:35 PM
To: Multiple recipients of list ORACLE-L



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!





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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 - 22:37:16 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US