[20180319]直接路径读特例12c.txt
--//昨天的测试突然想起以前遇到的直接路径读特例,在12c重复测试看看.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t as select * from all_objects order by DBMS_RANDOM.random;
Table created.
SCOTT@test01p> create index i_t_object_id on t(object_id);
Index created.
SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
107151 107151
--//分析.
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
SCOTT@test01p> select rowid ,owner from t where object_id between 1 and 10;
ROWID OWNER
------------------ --------------------
AAAaKPAAJAAAAY7AAE SYS
AAAaKPAAJAAAAaWAAP SYS
AAAaKPAAJAAAARtAAd SYS
AAAaKPAAJAAAAK5AAw SYS
AAAaKPAAJAAAAbGAAV SYS
AAAaKPAAJAAAAIFAAK SYS
AAAaKPAAJAAAASIAAB SYS
AAAaKPAAJAAAAMIAAQ SYS
AAAaKPAAJAAAAOMAAp SYS
9 rows selected.
2.测试:
SCOTT@test01p> alter system flush buffer_cache ;
System altered.
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAF';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
AAAaKPAAJAAAAY7AAF SYS 10575
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.07 | 5 | 2 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 225 | 5175 | 421 (1)| 00:00:01 | 2 |00:00:00.07 | 5 | 2 |
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.01 | 5 | 1 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 225 | 5175 | 421 (1)| 00:00:01 | 2 |00:00:00.01 | 5 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
--//多次执行总存在一个物理读.
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 4 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAF';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
AAAaKPAAJAAAAY7AAF SYS 10575
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 5 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
3.不过如果where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAE';
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAE';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 5 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
--//physical reads direct没有增加.
SCOTT@test01p> select file#,block#,status from v$bh where OBJD=107151 and STATUS<>'free';
FILE# BLOCK# STATUS
---------- ---------- --------------------
9 186 xcur
9 1595 xcur
SCOTT@test01p> @ rowid AAAaKPAAJAAAAY7AAE
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 1595 4 0x240063B 9,1595 alter system dump datafile 9 block 1595
--//rowid='AAAaKPAAJAAAAY7AAE'已经进入数据缓存.
SCOTT@test01p> select header_file,header_block from dba_segments where owner=user and segment_name='T';
HEADER_FILE HEADER_BLOCK
----------- ------------
9 186
--//dba =9 , 186 对应 段头.
--//我一直不明白为什么oracle要这样设计.12c与11g的测试一样.
--//还有1点不明白的地方就是:
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.04 | 5 | 1 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 2 | 46 | 421 (1)| 00:00:01 | 2 |00:00:00.04 | 5 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAB';
Plan hash value: 3207308387
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | TABLE ACCESS BY USER ROWID| T | 1 | 1 | 23 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-----------------------------------------------------------------------------------------------------------------------------
--//为什么前者逻辑读是5,而后者为1.
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT off';
D:\tools\rlwrap>grep "started" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_5280.trc
grep "started" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_5280.trc
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
--//转储内容:
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
env [0x0000000002197E9C]: (scn: 0x0000.019be656 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.019be619 flg: 0x00000661)
ktrgcm(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
env [0x0000000002197E9C]: (scn: 0x0000.019be656 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.019be619 flg: 0x00000660)
ktrgcm(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
SCOTT@test01p> @ 10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAASIAAB SYS 8
AAAaKPAAJAAAASIAAC SYS 10174
SCOTT@test01p> @ 10046off
Session altered.
=====================
PARSING IN CURSOR #35276568 len=102 dep=0 uid=109 oct=3 lid=109 tim=5262812718 hv=3900635955 ad='7ff13b72e40' sqlid='c5tpurvn7xytm'
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC'
END OF STMT
PARSE #35276568:c=15600,e=5133,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=280204748,tim=5262812715
EXEC #35276568:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=280204748,tim=5262813350
WAIT #35276568: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5262813588
WAIT #35276568: nam='Disk file operations I/O' ela= 598 FileOperation=2 fileno=9 filetype=2 obj#=107151 tim=5262814790
WAIT #35276568: nam='direct path read' ela= 40173 file number=9 first dba=1160 block cnt=1 obj#=107151 tim=5262855268
FETCH #35276568:c=0,e=41975,p=1,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=280204748,tim=5262855697
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> CR=4,为什么?
WAIT #35276568: nam='SQL*Net message from client' ela= 2441 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5262858326
WAIT #35276568: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5262858682
FETCH #35276568:c=0,e=332,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=280204748,tim=5262858968
STAT #35276568 id=1 cnt=2 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY ROWID RANGE T (cr=5 pr=1 pw=0 time=41932 us cost=421 size=5175 card=225)'
*** 2018-03-19 21:34:02.984
WAIT #35276568: nam='SQL*Net message from client' ela= 4953864 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5267820265
CLOSE #35276568:c=0,e=32,dep=0,type=0,tim=5267820714
=====================