??Database Replay Capture????
- by Liu Maclean(???)
Database Replay?11g??????,??workload capture??????????????,????????
??Workload Capture???????:
???????????????,???????2????,??????,???????????OLTP???????capture 10????1G????
?????:
?????????????????????
??startup restrict????,??????????
??capture???restrict??
?????????????
???????????????:
??scn????????
????????
????????
Capture???????????workload?????
???????SYSDBA?SYSOPER????OS??
????:
?TPCC???capture??????4.5%
????session????64KB???
???Workload Capture??????????
????????2?, ??RAC????workload capture file??????????????,??start_capture?????
????session????64KB???,??????????????workload capture file????Server Process??????,?????????parse???execution????,Server Process??LOGON?LOGOFF?SQL??????????PGA?,???WCR Capture PG?WCR Capture PGA?,?PGA?????????????????,Server Process???????????WCR???,?????WCR???Server Process??’WCR: capture file IO write’?????
?WCR?????????:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select name from v$event_name where name like '%WCR%';
NAME
----------------------------------------------------------------
WCR: replay client notify
WCR: replay clock
WCR: replay lock order
WCR: replay paused
WCR: RAC message context busy
WCR: capture file IO write
WCR: Sync context busy
latch: WCR: sync
latch: WCR: processes HT
11g????????WCR???LATCH
1* select name,gets from v$latch where name like '%WCR%'
SQL> /
NAME GETS
------------------------------ ----------
WCR: kecu cas mem 3
WCR: kecr File Count 37
WCR: MMON Create dir 1
WCR: ticker cache 0
WCR: sync 495
WCR: processes HT 0
WCR: MTS VC queue 0
7 rows selected.
????????????Database Replay Capture?????
1. ????capture dbms_workload_capture.start_capture
CREATE OR REPLACE DIRECTORY dbcapture AS '/home/oracle/dbcapture';
execute dbms_workload_capture.start_capture('CAPTURE','DBCAPTURE',default_action=>'INCLUDE');
SQL> select id,name,status,start_time,end_time,connects,user_calls,dir_path from
dba_workload_captures where id = (select max(id) from dba_workload_captures) ;
ID
----------
NAME
--------------------------------------------------------------------------------
STATUS START_TIM END_TIME CONNECTS
---------------------------------------- --------- --------- ----------
USER_CALLS
----------
DIR_PATH
--------------------------------------------------------------------------------
1
CAPTURE
IN PROGRESS 08-DEC-12 11
ID
----------
NAME
--------------------------------------------------------------------------------
STATUS START_TIM END_TIME CONNECTS
---------------------------------------- --------- --------- ----------
USER_CALLS
----------
DIR_PATH
--------------------------------------------------------------------------------
167
/home/oracle/dbcapture
2. ?? capture file??
[oracle@mlab2 dbcapture]$ ls -lR
.:
total 8
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 cap
drwxr-xr-x 3 oracle oinstall 4096 Dec 8 07:24 capfiles
-rw-r--r-- 1 oracle oinstall 0 Dec 8 07:24 wcr_cap_00001.start
./cap:
total 4
-rw-r--r-- 1 oracle oinstall 91 Dec 8 07:24 wcr_scapture.wmd
./capfiles:
total 4
drwxr-xr-x 12 oracle oinstall 4096 Dec 8 07:24 inst1
./capfiles/inst1:
total 40
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 08:31 aa
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ab
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ac
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ad
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ae
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 af
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ag
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ah
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 ai
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 07:24 aj
./capfiles/inst1/aa:
total 316
-rw-r--r-- 1 oracle oinstall 1762 Dec 8 07:25 wcr_c6cdah0000001.rec
-rw-r--r-- 1 oracle oinstall 16478 Dec 8 07:28 wcr_c6cf1h0000002.rec
-rw-r--r-- 1 oracle oinstall 1772 Dec 8 07:29 wcr_c6cjdh0000004.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:29 wcr_c6cnah0000005.rec
-rw-r--r-- 1 oracle oinstall 1821 Dec 8 07:41 wcr_c6cpfh0000007.rec
-rw-r--r-- 1 oracle oinstall 1815 Dec 8 07:33 wcr_c6cq6h000000a.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:34 wcr_c6cxmh000000h.rec
-rw-r--r-- 1 oracle oinstall 1427 Dec 8 07:41 wcr_c6cxvh000000j.rec
-rw-r--r-- 1 oracle oinstall 1425 Dec 8 07:41 wcr_c6czph000000k.rec
-rw-r--r-- 1 oracle oinstall 2398 Dec 8 07:49 wcr_c6dqfh000000q.rec
-rw-r--r-- 1 oracle oinstall 259321 Dec 8 08:35 wcr_c6du7h000000r.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 07:55 wcr_c6f6yh000000t.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 08:28 wcr_c6h3qh0000013.rec
./capfiles/inst1/ab:
total 0
./capfiles/inst1/ac:
total 0
./capfiles/inst1/ad:
total 0
./capfiles/inst1/ae:
total 0
./capfiles/inst1/af:
total 0
./capfiles/inst1/ag:
total 0
./capfiles/inst1/ah:
total 0
./capfiles/inst1/ai:
total 0
./capfiles/inst1/aj:
total 0
[oracle@mlab2 dbcapture]$ cd ./capfiles/inst1/aa
[oracle@mlab2 aa]$ ls -l
total 316
-rw-r--r-- 1 oracle oinstall 1762 Dec 8 07:25 wcr_c6cdah0000001.rec
-rw-r--r-- 1 oracle oinstall 16478 Dec 8 07:28 wcr_c6cf1h0000002.rec
-rw-r--r-- 1 oracle oinstall 1772 Dec 8 07:29 wcr_c6cjdh0000004.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:29 wcr_c6cnah0000005.rec
-rw-r--r-- 1 oracle oinstall 1821 Dec 8 07:41 wcr_c6cpfh0000007.rec
-rw-r--r-- 1 oracle oinstall 1815 Dec 8 07:33 wcr_c6cq6h000000a.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:34 wcr_c6cxmh000000h.rec
-rw-r--r-- 1 oracle oinstall 1427 Dec 8 07:41 wcr_c6cxvh000000j.rec
-rw-r--r-- 1 oracle oinstall 1425 Dec 8 07:41 wcr_c6czph000000k.rec
-rw-r--r-- 1 oracle oinstall 2398 Dec 8 07:49 wcr_c6dqfh000000q.rec
-rw-r--r-- 1 oracle oinstall 259321 Dec 8 08:35 wcr_c6du7h000000r.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 07:55 wcr_c6f6yh000000t.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 08:28 wcr_c6h3qh0000013.rec
[oracle@mlab2 aa]$ ls -l |wc -l
14
???????14???
3. ??LOGON????Server Process
[oracle@mlab2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 8 08:37:40 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
?????wcr??
[oracle@mlab2 aa]$ ls -ltr
total 316
-rw-r--r-- 1 oracle oinstall 1762 Dec 8 07:25 wcr_c6cdah0000001.rec
-rw-r--r-- 1 oracle oinstall 16478 Dec 8 07:28 wcr_c6cf1h0000002.rec
-rw-r--r-- 1 oracle oinstall 1772 Dec 8 07:29 wcr_c6cjdh0000004.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:29 wcr_c6cnah0000005.rec
-rw-r--r-- 1 oracle oinstall 1815 Dec 8 07:33 wcr_c6cq6h000000a.rec
-rw-r--r-- 1 oracle oinstall 1535 Dec 8 07:34 wcr_c6cxmh000000h.rec
-rw-r--r-- 1 oracle oinstall 1425 Dec 8 07:41 wcr_c6czph000000k.rec
-rw-r--r-- 1 oracle oinstall 1427 Dec 8 07:41 wcr_c6cxvh000000j.rec
-rw-r--r-- 1 oracle oinstall 1821 Dec 8 07:41 wcr_c6cpfh0000007.rec
-rw-r--r-- 1 oracle oinstall 2398 Dec 8 07:49 wcr_c6dqfh000000q.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 07:55 wcr_c6f6yh000000t.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 08:28 wcr_c6h3qh0000013.rec
-rw-r--r-- 1 oracle oinstall 259321 Dec 8 08:35 wcr_c6du7h000000r.rec
-rw-r--r-- 1 oracle oinstall 0 Dec 8 08:37 wcr_c6hp4h0000018.rec
??????wcr_c6hp4h0000018.rec ???
SQL> select spid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID
------------------------
14293
????????????????14293, ???????????????,??????wcr_c6hp4h0000018.rec
[oracle@mlab2 ~]$ ls -l /proc/14293/fd
total 0
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 8 08:39 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 8 08:39 10 -> /u01/app/oracle/product/11201/db_1/rdbms/audit/CRMV_ora_14293_1.aud
l-wx------ 1 oracle oinstall 64 Dec 8 08:39 11 -> /u01/app/oracle/diag/rdbms/crmv/CRMV/trace/CRMV_ora_14293.trc
l-wx------ 1 oracle oinstall 64 Dec 8 08:39 12 -> pipe:[34585895]
l-wx------ 1 oracle oinstall 64 Dec 8 08:39 13 -> /u01/app/oracle/diag/rdbms/crmv/CRMV/trace/CRMV_ora_14293.trm
l-wx------ 1 oracle oinstall 64 Dec 8 08:39 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 5 -> /u01/app/oracle/product/11201/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 6 -> /proc/14293/fd
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 7 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Dec 8 08:39 8 -> /home/oracle/dbcapture/capfiles/inst1/aa/wcr_c6hp4h0000018.rec
lr-x------ 1 oracle oinstall 64 Dec 8 08:39 9 -> pipe:[34585894]
?????lsof??
[root@mlab2 ~]# lsof|grep wcr_c6hp4h0000018.rec
oracle 14293 oracle 8u REG 8,1 0 17629644 /home/oracle/dbcapture/capfiles/inst1/aa/wcr_c6hp4h0000018.rec
????????,??Server Process????WCR REC??,?Server Process LOGON??????
3.????SQL??:
SQL> select 1 from dual;
1
----------
1
SQL> /
1
----------
1
[oracle@mlab2 aa]$ strings wcr_c6hp4h0000018.rec
==»????SQL????, ???????
??????SQL???,???????????????WCR??????,LOGON???????????SQL????,?????????
[oracle@mlab2 aa]$ strings wcr_c6hp4h0000018.rec
11.2.0.3.0
*File header info. (Shadow process='14293')
D0576B5D710A34F4E043B201A8C0ECFE
SYS;
NLS_LANGUAGE?
AMERICAN>
NLS_TERRITORY?
AMERICA>
NLS_CURRENCY?
NLS_ISO_CURRENCY?
AMERICA>
NLS_NUMERIC_CHARACTERS?
NLS_CALENDAR? GREGORIAN>
NLS_DATE_FORMAT? DD-MON-RR>
NLS_DATE_LANGUAGE?
AMERICAN>
NLS_CHARACTERSET?
AL32UTF8>
NLS_SORT?
BINARY>
NLS_TIME_FORMAT?
HH.MI.SSXFF AM>
NLS_TIMESTAMP_FORMAT?
DD-MON-RR HH.MI.SSXFF AM>
NLS_TIME_TZ_FORMAT?
HH.MI.SSXFF AM TZR>
NLS_TIMESTAMP_TZ_FORMAT?
DD-MON-RR HH.MI.SSXFF AM TZR>
NLS_DUAL_CURRENCY?
NLS_SPECIAL_CHARS?
NLS_NCHAR_CHARACTERSET?
UTF8>
NLS_COMP?
BINARY>
NLS_LENGTH_SEMANTICS?
BYTE>
NLS_NCHAR_CONV_EXCP?
FALSE
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/oracle/product/11201/db_1/bin/oracle)(ARGV0=oracleCRMV)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO))(CONNECT_DATA=(CID=(PROGRAM=sqlplus)(HOST=mlab2.oracle.com)(USER=oracle))))
,[email protected] (TNS V1-V3)U
tselect spid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat))
` _
select 1 from dual
select 1 from dual
???????????????????
[oracle@mlab2 aa]$ strings wcr_c6hp4h0000018.rec
9`9_^B
create table vva(t1 int)
`:_i
:`:_iB
`;_^
;`;_^B
create table vva(t1 int)
`_i
>`>_iB
FusC
`?_^
?`?_^B
FvWC
_begin
for i in 1..50000 loop
execute immediate 'select 1 from dual where 2='||i;
end loop;
end;
?SERVER PROCESS LOGOFF ???????
C`E_ B
k^2C
????Server Process????parse?execution???WCR??,??????????PGA?,????????????,????????,?????WCR???????????,????????
4. ??????
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump processstate 10;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/crmv/CRMV/trace/CRMV_ora_14293.trc
?processstate ??????????????? WCR: capture file IO write,??Server process??WCR ??
3: waited for 'SQL*Net message to client'
driver id=0x62657100, #bytes=0x1, =0x0
wait_id=139 seq_num=140 snap_id=1
wait times: snap=0.000007 sec, exc=0.000007 sec, total=0.000007 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.934091 sec of elapsed time
4: waited for 'latch: shared pool'
address=0x60106b20, number=0x133, tries=0x0
wait_id=138 seq_num=139 snap_id=1
wait times: snap=0.000066 sec, exc=0.000066 sec, total=0.000066 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 1.180690 sec of elapsed time
5: waited for 'WCR: capture file IO write'
=0x0, =0x0, =0x0
wait_id=137 seq_num=138 snap_id=1
wait times: snap=0.000189 sec, exc=0.000189 sec, total=0.000189 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 3.122783 sec of elapsed time
6: waited for 'WCR: capture file IO write'
=0x0, =0x0, =0x0
wait_id=136 seq_num=137 snap_id=1
wait times: snap=0.000191 sec, exc=0.000191 sec, total=0.000191 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 3.053132 sec of elapsed time
7: waited for 'WCR: capture file IO write'
5.??PGA????
SQL> oradebug dump heapdump 536870917;
Statement processed.
grep WCR /u01/app/oracle/diag/rdbms/crmv/CRMV/trace/CRMV_ora_14293.trc
Chunk 7fb1b606bfc0 sz= 65600 freeable "WCR Capture PG " ds=0x7fb1b6115f90
Chunk 7fb1b6111e18 sz= 4224 freeable "WCR Capture PG " ds=0x7fb1b6115f90
Chunk 7fb1b6112e98 sz= 4184 freeable "WCR Capture PG " ds=0x7fb1b6115f90
Chunk 7fb1b6113ef0 sz= 4224 freeable "WCR Capture PG " ds=0x7fb1b6115f90
Chunk 7fb1b6114f70 sz= 4104 recreate "WCR Capture PG " latch=(nil)
Chunk 7fb1b6115f78 sz= 160 freeable "WCR Capture PGA"
Chunk 7fb1b6116018 sz= 3248 freeable "WCR Capture PGA"
Subheap ds=0x7fb1b6115f90 heap name= WCR Capture PG size= 82336
HEAP DUMP heap name="WCR Capture PG" desc=0x7fb1b6115f90
FIVE LARGEST SUB HEAPS for heap name="WCR Capture PG" desc=0x7fb1b6115f9
PGA???WCR Capture PG ?WCR Capture PGA?freeable or recreate??chunk,???????Server Process???OS
Chunk 7fb1b606bfc0 sz= 65600 freeable "WCR Capture PG " ds=0x7fb1b6115f90
sz= 65600=» 64k ??????????64k??,???????????????64k
WCR????????????:)!
6.????
??WCR CAPTURE????????2?
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm in ('_capture_buffer_size','_wcr_control');
NAME VALUE DESCRIB
-------------------- -------------------- ------------------------------------------------------------
_wcr_control 0 Oracle internal test WCR parameter used ONLY for testing!
_capture_buffer_size 65536 To set the size of the PGA I/O recording buffers
??_capture_buffer_size ??PGA?WCR BUFFER?SIZE,???64k
_wcr_control ??WCR?????,??????
????,??????:
1. ???WCR WORKLOAD CAPTURE???????????,??Server Process????(????)2. ???server process????WCR??3. Server Proess???LOGON?LOGOFF?SQL?????????WCR???4. Server Process????????Immediate mode,????????PGA?(WCR Capture) subheap?,??????????????(timeout?????)5. ????, Server Process????????Immediate mode,?capture????parse??execution??(?????capture???parse?????????????,parse????capture???),?????LOGON?SQL??(???????)??PGA?WCR Capture?????,???????,????????,??tpcc??????4.5%6. ????_capture_buffer_size ??PGA?WCR BUFFER?SIZE,???64k7. WCR Capture?????binrary 2????,?????,????????????????WCR capture file8. WCR: capture file IO write?????Server Process??WCR??
