SQL> create table t as select object_id,owner from dba_objects where rownum < 1000;
表已创建。
SQL> alter table t add constraint t_id_pk primary key(object_id);
SQL> update t set owner='AA' where object_id=500;
已更新 1 行。
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'9999999999999') from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC TO_CHAR(START_
---------- ---------- ---------- ---------- ---------- ---------- --------------
6 6 1942 3683 3 13 4119141
SQL> select name from v$rollname where usn=6;
NAME
------------------------------
_SYSSMU6_2897970769$
SESSION2:
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum < 2));
SPID
------------------------
8463
SQL> alter system flush BUFFER_CACHE;
系统已更改。
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
会话已更改。
SQL> select * from test.t where object_id=500;
OBJECT_ID OWNER
---------- ------------------------------
500 SYS
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
会话已更改。
=====================
PARSING IN CURSOR #3 len=42 dep=0 uid=91 oct=3 lid=91 tim=1406434400557799 hv=3232312308 ad='24f4f408' sqlid='gqqq2p30akazn'
select * from test.t where object_id=500
END OF STMT
PARSE #3:c=2000,e=2579,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=51703167,tim=1406434400537084
EXEC #3:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=51703167,tim=1406434400558001
WAIT #3: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1406434400558190
WAIT #3: nam='Disk file operations I/O' ela= 82 FileOperation=2 fileno=10 filetype=2 obj#=76193 tim=1406434400558384
WAIT #3: nam='db file sequential read' ela= 15 file#=10 block#=14593 blocks=1 obj#=76193 tim=1406434400558446
WAIT #3: nam='db file sequential read' ela= 9 file#=10 block#=14594 blocks=1 obj#=76193 tim=1406434400558527
WAIT #3: nam='db file sequential read' ela= 7 file#=10 block#=257 blocks=1 obj#=76192 tim=1406434400558557
WAIT #3: nam='db file sequential read' ela= 62 file#=3 block#=208 blocks=1 obj#=0 tim=1406434400558686
WAIT #3: nam='db file sequential read' ela= 8 file#=3 block#=3683 blocks=1 obj#=0 tim=1406434400558743
FETCH #3:c=0,e=542,p=5,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=51703167,tim=1406434400558783
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=76192 op='TABLE ACCESS BY INDEX ROWID T (cr=5 pr=5 pw=0 time=0 us cost=1 size=30 card=1)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=76193 op='INDEX UNIQUE SCAN T_ID_PK (cr=2 pr=2 pw=0 time=0 us cost=1 size=0 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 217 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406434400577021
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=51703167,tim=1406434400577053
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406434400577069
*** 2014-07-27 12:13:26.343
WAIT #3: nam='SQL*Net message from client' ela= 5766213 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406434406343299
CLOSE #3:c=0,e=17,dep=0,type=0,tim=1406434406343380
=====================
PARSING IN CURSOR #4 len=56 dep=0 uid=91 oct=42 lid=91 tim=1406434406343642 hv=3621752648 ad='0' sqlid='5p1y8sgbxz3u8'
ALTER SESSION SET EVENTS '10046 trace name context off'
END OF STMT
PARSE #4:c=0,e=234,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1406434406343641
EXEC #4:c=0,e=243,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1406434406343915
WAIT #3: nam='db file sequential read' ela= 15 file#=10 block#=14593 blocks=1 obj#=76193 tim=1406434400558446
WAIT #3: nam='db file sequential read' ela= 9 file#=10 block#=14594 blocks=1 obj#=76193 tim=1406434400558527
WAIT #3: nam='db file sequential read' ela= 7 file#=10 block#=257 blocks=1 obj#=76192 tim=1406434400558557
WAIT #3: nam='db file sequential read' ela= 62 file#=3 block#=208 blocks=1 obj#=0 tim=1406434400558686
WAIT #3: nam='db file sequential read' ela= 8 file#=3 block#=3683 blocks=1 obj#=0 tim=1406434400558743
其中:
SQL> select object_name from dba_objects where object_id=76193;
OBJECT_NAME
--------------------------------------------------------------------------------
T_ID_PK
SQL> select object_name from dba_objects where object_id=76192;
OBJECT_NAME
--------------------------------------------------------------------------------
T
select segment_name,header_file,header_block from dba_segments where segment_name='_SYSSMU6_2897970769$'SQL>
2 ;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
_SYSSMU6_2897970769$ 3 208
WAIT #3: nam='db file sequential read' ela= 62 file#=3 block#=208 blocks=1 obj#=0 tim=1406434400558686
_SYSSMU6_2897970769$的块头
WAIT #3: nam='db file sequential read' ela= 8 file#=3 block#=3683 blocks=1 obj#=0 tim=1406434400558743
SQL> select owner, segment_name, segment_type
from dba_extents
where file_id = 3
and 3683 between block_id
and block_id + blocks - 1 2 3 4 5 ;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------- ------------------
SYS _SYSSMU6_2897970769$ TYPE2 UNDO
顺序是读索引块14593和14594 读表块257 读UNDO HEADER 208 读UNDO块3683
sreace 跟踪:
[oracle@june2 ~]$ cat 8463.txt
8463 read(13, "\0\352\0\0\6\0\0\0\0\0\21i:\376\377\377\377\1\0\0\0\3\0\0\0\3^;a\200\0\0"..., 8208) = 234
8463 gettimeofday({1406435222, 138141}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 138423}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 138551}, NULL) = 0
8463 gettimeofday({1406435222, 138567}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 138726}, NULL) = 0
8463 gettimeofday({1406435222, 138829}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 gettimeofday({1406435222, 138917}, NULL) = 0
8463 gettimeofday({1406435222, 138932}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 gettimeofday({1406435222, 138989}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 139078}, NULL) = 0
8463 gettimeofday({1406435222, 139099}, NULL) = 0
8463 gettimeofday({1406435222, 139117}, NULL) = 0
8463 gettimeofday({1406435222, 139135}, NULL) = 0
8463 gettimeofday({1406435222, 139168}, NULL) = 0
8463 gettimeofday({1406435222, 139184}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 gettimeofday({1406435222, 139302}, NULL) = 0
8463 gettimeofday({1406435222, 139317}, NULL) = 0
8463 pread64(18, "\6\242\0\0\0019\200\2L\332>\0\0\0\1\4\272\35\0\0\2\0\0\0\241)\1\0K\332>\0"..., 8192, 119545856) = 8192
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 pread64(18, "\6\242\0\0\0029\200\2L\332>\0\0\0\2\4q'\0\0\2\0\0\0\241)\1\0K\332>\0"..., 8192, 119554048) = 8192
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 gettimeofday({1406435222, 139418}, NULL) = 0
8463 pread64(18, "\6\242\0\0\1\1\200\2\252\334>\0\0\0\1\4/3\0\0\1\0\0\0\240)\1\0\252\334>\0"..., 8192, 2105344) = 8192
8463 gettimeofday({1406435222, 139803}, NULL) = 0
8463 gettimeofday({1406435222, 139822}, NULL) = 0
8463 gettimeofday({1406435222, 139855}, NULL) = 0
8463 gettimeofday({1406435222, 139871}, NULL) = 0
8463 pread64(12, "&\242\0\0\320\0\300\0e\332>\0\0\0\1\4\330\345\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1703936) = 8192
8463 gettimeofday({1406435222, 139923}, NULL) = 0
8463 gettimeofday({1406435222, 139940}, NULL) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 140017}, NULL) = 0
8463 gettimeofday({1406435222, 140032}, NULL) = 0
8463 pread64(12, "\2\242\0\0c\16\300\0e\332>\0\0\0\1\4\3\224\0\0\6\0\6\0\226\7\0\0\335\3\r\r"..., 8192, 30171136) = 8192
8463 gettimeofday({1406435222, 140198}, NULL) = 0
8463 gettimeofday({1406435222, 140215}, NULL) = 0
8463 gettimeofday({1406435222, 140292}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 gettimeofday({1406435222, 140343}, NULL) = 0
8463 gettimeofday({1406435222, 140362}, NULL) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 getrusage(RUSAGE_SELF, {ru_utime={0, 249962}, ru_stime={0, 65989}, ...}) = 0
8463 times({tms_utime=24, tms_stime=6, tms_cutime=0, tms_cstime=0}) = 448603871
8463 gettimeofday({1406435222, 140444}, NULL) = 0
8463 write(16, "\1\225\0\0\6\0\0\0\0\0\20\27\0\0\0\336Orv\342\370a\217\10\321\266\270R\354\265\17x"..., 405) = 405
8463 read(13, <unfinished ...>
打开的文件描述符为:
[oracle@june2 ~]$ cat 8463.txt | grep 8192
8463 pread64(18, "\6\242\0\0\0019\200\2L\332>\0\0\0\1\4\272\35\0\0\2\0\0\0\241)\1\0K\332>\0"..., 8192, 119545856) = 8192
8463 pread64(18, "\6\242\0\0\0029\200\2L\332>\0\0\0\2\4q'\0\0\2\0\0\0\241)\1\0K\332>\0"..., 8192, 119554048) = 8192
8463 pread64(18, "\6\242\0\0\1\1\200\2\252\334>\0\0\0\1\4/3\0\0\1\0\0\0\240)\1\0\252\334>\0"..., 8192, 2105344) = 8192
8463 pread64(12, "&\242\0\0\320\0\300\0e\332>\0\0\0\1\4\330\345\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1703936) = 8192
8463 pread64(12, "\2\242\0\0c\16\300\0e\332>\0\0\0\1\4\3\224\0\0\6\0\6\0\226\7\0\0\335\3\r\r"..., 8192, 30171136) = 8192
lrwx------ 1 oracle dba 64 07-27 12:25 18 -> /oradata/june/test2.dbf
访问第一个数据块号为:
SQL> select 119545856/8192 from dual;
119545856/8192
--------------
14593
访问第二个数据块号为:
SQL> select 119554048/8192 from dual;
119554048/8192
--------------
14594
访问的第三个数据块号为;
SQL> select 2105344/8192 from dual;
2105344/8192
------------
257
访问的第四个块号为:
SQL> select 1703936/8192 from dual;
1703936/8192
------------
208
访问的第五个块号为:
SQL> select 30171136/8192 from dual;
30171136/8192
-------------
3683
strace 和10046的结果完全一致
strace和10046 研究一致读
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
MySQL 主从 AUTO_INCREMENT 不一致问题分析
本文介绍了MySQL5.7 中常见的replace into 操作造成的主从auto_increment不一致现象,一旦触发了主从切换,业务的正常插入操作会触发主键冲突的报错提示。
MySQL典型案例 replace into auto_increment -
一致性锁定读
一致性锁定读在默认配置下,即事务的隔离级别为REPEATABLE READ模式下,InnoDB存储引擎的SELECT操作使用一致性非锁
数据库 sql oracle 存储引擎 加锁