[20170607]再论Private Strand Flush Not Complete.txt
--//曾经跟zergduan有过讨论,链接www.itpub.net/thread-2065703-1-1.html,按照道理只要使用IMU,在日志切换时如果在Private Strand
--//有信息一定会出现Private Strand Flush Not Complete提示在alert.log日志文件中,因为要把那些信息写入redo文件.
--//我在我的一个生产系统看到,我发现一个没有很好优化的系统也大量出现,而且出现时间大部分在我优化前,
--//链接:blog.itpub.net/267265/viewspace-2135830/
--//所以给我印象是出现这种情况:redo太小,磁盘写入太慢.等因素.
--//实际上可以做一个简单的例子验证我的判断是错误的.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> select flashback_on from v$database;
FLASHBACK_ON
-------------
NO
--//是否开启IMU,可以查询视图X$KCRFSTRAND确定.
SELECT INDX
,last_buf_kcrfa
,PNEXT_BUF_KCRFA_CLN nxtbufadr
,NEXT_BUF_NUM_KCRFA_CLN nxtbuf#
,BYTES_IN_BUF_KCRFA_CLN "B/buf"
,PVT_STRAND_STATE_KCRFA_CLN state
,STRAND_NUM_ORDINAL_KCRFA_CLN strand#
,PTR_KCRF_PVT_STRAND stradr
,INDEX_KCRF_PVT_STRAND stridx
,SPACE_KCRF_PVT_STRAND strspc
,TXN_KCRF_PVT_STRAND txn
,TOTAL_BUFS_KCRFA totbufs#
,STRAND_SIZE_KCRFA strsz
FROM X$KCRFSTRAND ;
2.测试:
SCOTT@book> create table deptx tablespace tea as select * from dept ;
Table created.
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
16.27.91
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------ ---------- ---------- ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU16_4044556495$' XID 16 27 91; 16 27 91 7 1053 101 23 ACTIVE 1 1 10001B005B000000 00000000818B3C60 2017-06-07 09:11:53
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU16_4044556495$';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1053;
SCOTT@book> alter system switch logfile ;
System altered.
3.查看alert.log:
Wed Jun 07 09:12:37 2017
Thread 1 cannot allocate new log, sequence 814
Private strand flush not complete
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Current log# 2 seq# 813 mem# 0: /mnt/ramdisk/book/redo02.log
Beginning log switch checkpoint up to RBA [0x32e.2.10], SCN: 13278969076
Thread 1 advanced to log sequence 814 (LGWR switch)
Current log# 3 seq# 814 mem# 0: /mnt/ramdisk/book/redo03.log
Wed Jun 07 09:12:37 2017
Archived Log entry 1400 added for thread 1 sequence 813 ID 0x4fb7d86e dest 1:
--//注意~线的内容,也就是private strand有信息,在日志切换时要将以上信息写入日志就会出现Private strand flush not complete的
--//情况
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
16.27.91
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU16_4044556495$' XID 16 27 91; 16 27 91 7 1053 101 23 ACTIVE 1 1 10001B005B000000 00000000818B3C60 2017-06-07 09:11:53
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU16_4044556495$';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1053;
--//可以看到事务没有提交.
$ strings /mnt/ramdisk/book/redo02.log | grep -i accounting
accounting
ACCOUNTING
--//可以已经写入日志文件中.
4.继续测试:
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> update deptx set dname=lower(dname) where deptno=30;
1 row updated.
SYS@book> alter system archive log current ;
System altered.
--//查看alert.log:
Wed Jun 07 09:21:24 2017
ALTER SYSTEM ARCHIVE LOG
Wed Jun 07 09:21:24 2017
Thread 1 cannot allocate new log, sequence 816
Private strand flush not complete
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Current log# 1 seq# 815 mem# 0: /mnt/ramdisk/book/redo01.log
Beginning log switch checkpoint up to RBA [0x330.2.10], SCN: 13278969776
Thread 1 advanced to log sequence 816 (LGWR switch)
Current log# 2 seq# 816 mem# 0: /mnt/ramdisk/book/redo02.log
Archived Log entry 1402 added for thread 1 sequence 815 ID 0x4fb7d86e dest 1:
$ strings /mnt/ramdisk/book/redo01.log | grep -i sales$
sales
SALES
--//使用alter system archive log current ;切换也一样.
--//从以上测试,说明我以前学习判断存在错误,正如zergduan所讲,使用IMU出现Private Strand Flush Not Complete是正常的现象.
5.再继续测试:
--//测试如果已经从private stand写出,后续的事务是否还继续使用IMU.
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 815 52428800 512 1 YES INACTIVE 13278969564 2017-06-07 09:18:00 13278969776 2017-06-07 09:21:24
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 816 52428800 512 1 NO CURRENT 13278969776 2017-06-07 09:21:24 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 814 52428800 512 1 YES INACTIVE 13278969076 2017-06-07 09:12:37 13278969564 2017-06-07 09:18:00
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前日志是/mnt/ramdisk/book/redo02.log.
$ strings /mnt/ramdisk/book/redo02.log | grep jjjj1234
--//没有jjjj1234信息.
SCOTT@book> update deptx set dname='jjjj1234' where deptno=10;
1 row updated.
$ strings /mnt/ramdisk/book/redo02.log | grep jjjj1234
--//没有jjjj1234信息.
SYS@book> alter system checkpoint ;
System altered.
$ strings /mnt/ramdisk/book/redo02.log | grep jjjj1234
jjjj1234
--//已经写入日志.如果不出现可以执行alter system checkpoint ;多次.可以参考链接http://blog.itpub.net/267265/viewspace-2137720/
--//[20170421]警惕打开IMU对测试的影响.txt.
SCOTT@book> update deptx set dname='jjjj1234' where deptno=20;
1 row updated.
$ strings /mnt/ramdisk/book/redo02.log | grep jjjj1234
jjjj1234
jjjj1234
--//可以发现后续的事务,不再使用IMU.
总结:
1.使用IMU在alert.log文件中出现Private Strand Flush Not Complete是正常的现象.不必过滤.
2.如果出现与redo相关的等待事件,才要引起关注,是否切换太频繁,是否日志太小,磁盘IO太慢等因素.