分享程序员开发的那些事...
更新时间：2022-09-13 23:15:29
[20150511]关于11G Direct Path Read.txt
--前一阵子被别人问到升级到11G要注意那些细节,我给对方讲了审计,用户口令管理(大小写),sql tuning advisor,auto space advisor,
--以及Direct Path Read等问题,对方问了很多Direct Path Read相关的问题.
--实际上就是讲简单就是一些表大于一定的情况下,读取数据从磁盘,绕过buffer cache的情况.
--如果有用户使用Direct Path Read,系统将将buffer cache的脏块转储到磁盘.在这个过程中,会话会出现一个"enq: KO – fast object
-- checkpoint" 等待事件.
--我喜欢做例子来说明情况,这样更好的学习与记忆:
1.建立测试环境:
SCOTT@test> create table t as select rownum id , 'test' name,lpad('a',100,'a') pad from dual connect by level Table created.
SCOTT@test> @stats t
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test> select rowid from t where rownum=1;
ROWID
------------------
AABJ+eAAEAAAACjAAA
SCOTT@test> @ lookup_rowid AABJ+eAAEAAAACjAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
303006 4 163 0 4,163 alter system dump datafile 4 block 163 ;
2.开始测试:
-- session 1,执行修改不提交:
SCOTT@test> update t set name=upper(name);
1000 rows updated.
--session 2:
SCOTT@test> alter session set "_serial_direct_read"=always;
Session altered.
--为了测试方表,这样全表扫描都是直接路径读取.
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
397 5 16415 alter system kill session '397,5' immediate;
SCOTT@test> select * from v$session_event where sid=397;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
397 Disk file operations I/O 1 0 0 .04 0 382 166678035 1740759767 8 User I/O
397 SQL*Net message to client 18 0 0 0 0 37 2067390145 2000153315 7 Network
397 SQL*Net message from client 18 0 12256 680.89 4590 122560454 1421975091 2723168908 6 Idle
397 events in waitclass Other 1 1 0 0 0 4 1736664284 1893977003 0 Other
SCOTT@test> select count(*) from t;
COUNT(*)
----------
1000
SCOTT@test> select * from v$session_event where sid=397;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
397 Disk file operations I/O 2 0 0 .03 0 683 166678035 1740759767 8 User I/O
397 enq: KO - fast object checkpoint 1 0 2 2.18 2 21802 4205197519 4217450380 1 Application
397 SQL*Net message to client 23 0 0 0 0 48 2067390145 2000153315 7 Network
397 SQL*Net message from client 22 0 13648 620.35 4590 136476274 1421975091 2723168908 6 Idle
397 events in waitclass Other 2 1 0 .06 0 1234 1736664284 1893977003 0 Other
--可以发现出现1次enq: KO - fast object checkpoint等待事件.
3.做一个转储:
SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x49f9e csc: 0x02.f46954eb itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0002.f46954eb
0x02 0x0003.018.00007cd9 0x00c0099d.2691.04 ---- 63 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000a3
data_block_dump,data header at 0x2a9750127c
--从itl=0x02 ,Lck可以知道已经写盘.后面的信息也可以证明
block_row_dump:
tab 0, row 0, @0x1f10
tl: 112 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 4] 54 45 53 54
col 2: [100]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
SCOTT@test> select dump('TEST',16) from dual ;
DUMP('TEST',16)
-------------------------
Typ=96 Len=4: 54,45,53,54
--说明已经写盘.
4.继续测试:
--session 1:
SCOTT@test> rollback ;
Rollback complete.
--session 2:
SCOTT@test> select count(*) from t;
COUNT(*)
----------
1000
SCOTT@test> select * from v$session_event where sid=397;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
397 Disk file operations I/O 4 0 0 .03 0 1253 166678035 1740759767 8 User I/O
397 control file sequential read 7 0 0 0 0 138 3213517201 4108307767 9 System I/O
397 enq: KO - fast object checkpoint 2 0 4 2.22 2 44387 4205197519 4217450380 1 Application
397 log file sync 1 0 0 .29 0 2908 1328744198 3386400367 5 Commit
397 SQL*Net message to client 30 0 0 0 0 73 2067390145 2000153315 7 Network
397 SQL*Net message from client 29 0 52863 1822.87 28526 528631951 1421975091 2723168908 6 Idle
397 SQL*Net break/reset to client 2 0 0 .01 0 106 1963888671 4217450380 1 Application
397 events in waitclass Other 3 1 0 .05 0 1365 1736664284 1893977003 0 Other
8 rows selected.
-可以发现又增加了1次enq: KO - fast object checkpoint等待事件.
--再次转储:
SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x49f9e csc: 0x02.f46954eb itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0002.f46954eb
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000a3
--从itl=0x02 ,Lck=0,说明已经写盘.后面的信息也可以证明:
block_row_dump:
tab 0, row 0, @0x1f10
tl: 112 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02
col 1: [ 4] 74 65 73 74
col 2: [100]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
5.深入了解enq: KO - fast object checkpoint:
SCOTT@test> @ev_name 'enq: KO'
old 1: select * from v$event_name where lower(name) like lower('%&&1%')
new 1: select * from v$event_name where lower(name) like lower('%enq: KO%')
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
108 4205197519 enq: KO - fast object checkpoint name|mode 2 0 4217450380 1 Application
SCOTT@test> column p1text format a20
SCOTT@test> column p2text format a20
SCOTT@test> column p3text format a20
SCOTT@test> select * from V$SESSION_WAIT_HISTORY where sid=397 and event='enq: KO - fast object checkpoint';
SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO
---- ---- ------- --------------------------------- ---------- ---------- ------- ------ ------- ---- ---------- --------------- --------------------------
397 7 108 enq: KO - fast object checkpoint name|mode 1263468550 2 65570 0 1 2 22585 33
SCOTT@test> select * from v$lock_type where type='KO';
TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
----- --------------------------- --------- --------- --- -----------------------------------------------
KO Multiple Object Checkpoint 2 0 NO Coordinates checkpointing of multiple objects
-- 不了解P1,P2表示什么,放弃.