logical_reads

最新推荐文章于 2021-04-07 17:49:04 发布

cuian7084

最新推荐文章于 2021-04-07 17:49:04 发布

阅读量234

点赞数

文章标签：数据库

1,逻辑读统计信息
SELECT *
FROM v$sysstat
WHERE NAME IN ('session logical reads', 'db block gets', 'consistent gets',
        'consistent gets direct', 'consistent gets from cache',
        'db block gets direct', 'db block gets from cache')
ORDER BY NAME;
STATISTIC# NAME                            CLASS      VALUE    STAT_ID
---------- -------------------------- ---------- ---------- ----------
        50 consistent gets                     8   49212917 4162191256
        53 consistent gets direct              8     131300 420374750
        51 consistent gets from cache          8   49081617 2839918855
        47 db block gets                       8     850707 1480709069
        49 db block gets direct                8       1099   95128520
        48 db block gets from cache            8     849608 4017839461
         9 session logical reads               1   50063624 3143187968

根据上面的输出，可以发现：
session logical reads = consistent gets + db block gets
consistent gets = consistent gets direct + consistent gets from cache
db block gets = db block gets direct + db block gets from cache
一般理解，db block gets包括以current mode读取的数据，consistent gets则为其他读取。

２，逻辑读计数规则
在执行sql时，会读取各种数据块，包括数据段头、数据块、索引块、undo段头、undo块等。在读取每个块时，需要首先pin该数据块（无论是否涉及物理读）；
不同模块：操作pin操作会产生不同模式的逻辑读(consistent gets or db block gets)，而是否物理读则取决于所需数据块是否在ｃａｃｈｅ　ｂｕｆｆｅｒ中存在。

下面几个操作表示select操作时，了获取数据consistent gets:
pin kdswh01:kteinicnt dba 140e64b:4 time 549996100 --&gt获取扩展段数据
pin ktewh26:kteinpscan dba 140e64b:4 time 549996100 --&gt获取段头信息
pin kdswh01:kdstgr dba 140e64c:1 time 550014316 --&gt读取表信息

而下面几个操作表示在update操作时，以current mode获取数据的pin操作，对应db block gets，都需要pin release操作．
pin kduwh01:kdusru dba 140e64f:1 time 3828486616 --&gt当前模式读入数据块，用于修改
pin ktuwh01:ktugus dba 800009:17 time 3828495628 --&gt获取回滚段头信息，获取事务槽
pin kcbwh2:kcbchg1 dba 801c3b:18 time 3828499816 --&gtundo block,记录before image
pin release 4305 ktuwh01:ktugus dba 800009:17
pin release 176 kcbwh2:kcbchg1 dba 801c3b:18
pin release 13432 kduwh01:kdusru dba 140e64f:1

３，跟踪
使用隐藏参数_trace_pin_time(trace how long a current pin is held)可以跟踪pin操作，结合10046事件，10200/10201事件可以跟踪分析各种sql操作产生的pin操作，物理读操作．

sqlplus / as sysdba
sql>alter system set "_trace_pin_time"=1 scope=spfile;
System altered.

sql>startup force;
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  1219088 bytes
Variable Size             184550896 bytes
Database Buffers         1006632960 bytes
Redo Buffers               15556608 bytes
Database mounted.
Database opened.

sql>alter session set events '10046 trace name context forever,level 8';
Session altered.

SQL> create table tt(x int);

Table created.

SQL> insert into tt values(1);

1 row created.

SQL> insert into tt values(2);

1 row created.

SQL> commit;

Commit complete.

sql>alter system flush buffer_cache;
System altered.

sql>set autotrace trace stat

sql>select * from tt;
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          2 physical reads
          0 redo size
        443 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL> alter system flush buffer_cache;

System altered.

SQL> update tt set x=5;

2 rows updated.

Statistics
----------------------------------------------------------
          4 recursive calls
          4 db block gets
          7 consistent gets
          4 physical reads
        876 redo size
        660 bytes sent via SQL*Net to client
        553 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL> commit;

Commit complete.

跟踪文件为:
/oracle/admin/ora10ctg/udump/ora10ctg_ora_1526.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/10g
System name: Linux
Node name: rhel5
Release: 2.6.18-8.el5xen
Version: #1 SMP Thu Mar 15 21:02:53 EDT 2007
Machine: i686
Instance name: ora10ctg
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1526, image: oracle@rhel5 (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2019-03-30 10:13:33.030
*** SESSION ID:(159.6) 2019-03-30 10:13:33.030
pin ktewh25: kteinicnt dba 4002d1:4 time 2945136722   --&gtdba:4002d1=>TABLE:SYS.PROPS$:0@SYSTEM(1,721)
pin ktewh26: kteinpscan dba 4002d1:4 time 2945137269
pin kdswh01: kdstgr dba 4002d2:1 time 2945137552      --&gtdba:4002d2=>TABLE:SYS.PROPS$:0@SYSTEM(1,722)
pin kdswh01: kdstgr dba 4002d2:1 time 2945137663
pin ktewh25: kteinicnt dba 401671:4 time 2945138782   --&gtdba:401671=>TABLE:SYS.SERVICE$:0@SYSTEM(1,5745)
pin ktewh26: kteinpscan dba 401671:4 time 2945138842
pin kdswh01: kdstgr dba 401672:1 time 2945138919      --&gtdba:401672=>TABLE:SYS.SERVICE$:0@SYSTEM(1,5746)
=====================
PARSING IN CURSOR #1 len=67 dep=0 uid=0 ct=42 lid=0 tim=1517492206693008 hv=2866737153 ad='6696ce28'
alter session set events '10046 trace name context forever,level 8'
END OF STMT
EXEC #1:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492206693000
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492206693247
WAIT #1: nam='SQL*Net message from client' ela= 647 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492206693972
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492206694181
*** 2019-03-30 10:13:48.845
WAIT #0: nam='SQL*Net message from client' ela= 8975310 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492215669536
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=0 ct=49 lid=0 tim=1517492215669782 hv=4227365542 ad='6650da80'
alter system flush buffer_cache
END OF STMT
PARSE #1:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492215669777
WAIT #1: nam='rdbms ipc reply' ela= 13852 from_process=7 timeout=21474836 p3=0 obj#=-1 tim=1517492215684055
EXEC #1:c=0,e=14261,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492215684176
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492215684255
WAIT #1: nam='SQL*Net message from client' ela= 323 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492215684624
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492215684748
WAIT #0: nam='SQL*Net message from client' ela= 7157558 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222842351
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=0 ct=3 lid=0 tim=1517492222842576 hv=3933222116 ad='6666fed0'
SELECT DECODE('A','A','1','2') FROM DUAL
END OF STMT
PARSE #1:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222842570
EXEC #1:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222842764
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222842829
FETCH #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1517492222842911
WAIT #1: nam='SQL*Net message from client' ela= 263 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222843320
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='FAST DUAL (cr=0 pr=0 pw=0 time=11 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222843493
WAIT #0: nam='SQL*Net message from client' ela= 143 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222843671
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=0 ct=3 lid=0 tim=1517492222843855 hv=2185052098 ad='664c6c20'
SELECT DISTINCT SID FROM V$MYSTAT                   --&gtsid
END OF STMT
PARSE #1:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222843850
EXEC #1:c=0,e=197,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222844176
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222844222
FETCH #1:c=4000,e=1642,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1517492222845907
WAIT #1: nam='SQL*Net message from client' ela= 159 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222846163
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='HASH UNIQUE (cr=0 pr=0 pw=0 time=1794 us)'
STAT #1 id=2 cnt=363 pid=1 pos=1 bj=0 p='FIXED TABLE FULL X$KSUMYSTA (cr=0 pr=0 pw=0 time=1492 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=0 p='FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=12 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222846419
WAIT #0: nam='SQL*Net message from client' ela= 494 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222846951
*** SERVICE NAME:(SYS$USERS) 2019-03-30 10:13:56.197
*** SESSION ID:(146.46) 2019-03-30 10:13:56.197
pin ktewh25: kteinicnt dba 4002d1:4 time 2967760727   --&gtdba:4002d1=>TABLE:SYS.PROPS$:0@SYSTEM(1,721)
pin ktewh26: kteinpscan dba 4002d1:4 time 2967760832
pin kdswh01: kdstgr dba 4002d2:1 time 2967761104      --&gtdba:4002d2=>TABLE:SYS.PROPS$:0@SYSTEM(1,722)
pin kdswh01: kdstgr dba 4002d2:1 time 2967761167
pin ktewh25: kteinicnt dba 401671:4 time 2967761790   --&gtdba:401671=>TABLE:SYS.SERVICE$:0@SYSTEM(1,5745)
pin ktewh26: kteinpscan dba 401671:4 time 2967761836
pin kdswh01: kdstgr dba 401672:1 time 2967761975      --&gtdba:401672=>TABLE:SYS.SERVICE$:0@SYSTEM(1,5746)
*** SESSION ID:(159.6) 2019-03-30 10:13:56.199
=====================
PARSING IN CURSOR #1 len=298 dep=0 uid=0 ct=3 lid=0 tim=1517492222851144 hv=3566218912 ad='664c25e8'
SELECT statistic# s, NAME
FROM sys.v_$statname
WHERE NAME IN
       ('recursive calls', 'db block gets', 'consistent gets',
        'physical reads', 'redo size', 'bytes sent via SQL*Net to client',
        'bytes received via SQL*Net from client',
        'SQL*Net roundtrips to/from client', 'sorts (memory)',
        'sorts (disk)')
ORDER BY s                                     --&gtauto trace sql
END OF STMT
PARSE #1:c=0,e=168,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222851137
EXEC #1:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492222851342
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222851390
FETCH #1:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1517492222851526
WAIT #1: nam='SQL*Net message from client' ela= 209 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222851796
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222851887
FETCH #1:c=0,e=253,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,tim=1517492222852101
WAIT #1: nam='SQL*Net message from client' ela= 123 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222852302
STAT #1 id=1 cnt=10 pid=0 pos=1 bj=0 p='FIXED TABLE FULL X$KSUSD (cr=0 pr=0 pw=0 time=59 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492222852437
*** 2019-03-30 10:14:02.655
WAIT #0: nam='SQL*Net message from client' ela= 6303678 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492229156156
*** SESSION ID:(159.6) 2019-03-30 10:14:02.657
=====================
PARSING IN CURSOR #1 len=16 dep=0 uid=0 ct=3 lid=0 tim=1517492229157813 hv=1245498784 ad='66671a38'
select * from tt
END OF STMT
PARSE #1:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492229157807
EXEC #1:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517492229158009
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517492229158053

WAIT #1: nam='db file sequential read' ela= 44 file#=1 block#=62329 blocks=1 obj#=62713 tim=1517492229158200
pin ktewh25: kteinicnt dba 40f379:4 time 2974070125 --&gtdba:40f379=>TABLE:SYS.TT:0@SYSTEM(1,62329)
pin ktewh26: kteinpscan dba 40f379:4 time 2974070166

WAIT #1: nam='db file sequential read' ela= 100 file#=1 block#=62330 blocks=1 obj#=62713 tim=1517492229158491
pin kdswh01: kdstgr dba 40f37a:1 time 2974070401 --&gtdba:40f37a=>TABLE:SYS.TT:0@SYSTEM(1,62330)
FETCH #1:c=0,e=480,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1517492229158576 --&gtconsistent read:3 physical read:1

WAIT #1: nam='SQL*Net message from client' ela= 276 driver id=1650815232 #bytes=1 p3=0 obj#=62713 tim=1517492229158909
pin kdswh01: kdstgr dba 40f37a:1 time 2974070847
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62713 tim=1517492229159009
FETCH #1:c=0,e=83,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1517492229159047

WAIT #1: nam='SQL*Net message from client' ela= 269 driver id=1650815232 #bytes=1 p3=0 obj#=62713 tim=1517492229159390
*** SESSION ID:(159.6) 2019-03-30 10:14:02.660
STAT #1 id=1 cnt=2 pid=0 pos=1 bj=62713 p='TABLE ACCESS FULL TT (cr=4 pr=2 pw=0 time=471 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=62713 tim=1517492229160577
*** 2019-03-30 10:15:40.257
WAIT #0: nam='SQL*Net message from client' ela= 95309536 driver id=1650815232 #bytes=1 p3=0 obj#=62713 tim=1517492324470156
*** SESSION ID:(159.6) 2019-03-30 10:15:40.263

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*两个“db file sequential read”表示了2次物理读，分别是对段头和数据块的读取
*四次pin操作，即四次“consistent gets”，前两次为对段头的pin操作,后两次是对数据块的pin操作
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
=====================

=====================
PARSING IN CURSOR #2 len=37 dep=1 uid=0 ct=3 lid=0 tim=1517516464332586 hv=1398610540 ad='66ab65cc'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1517516464332582
EXEC #2:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1517516464332806
WAIT #2: nam='db file sequential read' ela= 83 file#=1 block#=461 blocks=1 obj#=-1 tim=1517516464333375
pin kdswh05: kdsgrp dba 4001cd:1 time 1439441543 --&gtdba:4001cd=>TABLE:SYS.VIEW$:0@SYSTEM(1,461)
pin kdswh05: kdsgrp dba 4001cd:1 time 1439441596
FETCH #2:c=0,e=688,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1517516464333538
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=63 p='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=643 us)'
EXEC #1:c=4000,e=3985,p=1,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1517516464336337
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464336415
FETCH #1:c=0,e=387,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1517516464336854
WAIT #1: nam='SQL*Net message from client' ela= 163 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464337106
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464337168
FETCH #1:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,tim=1517516464337276
WAIT #1: nam='SQL*Net message from client' ela= 101 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464337445
STAT #1 id=1 cnt=10 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=0 pr=0 pw=0 time=393 us)'
STAT #1 id=2 cnt=10 pid=1 pos=1 bj=0 p='FIXED TABLE FIXED INDEX X$KSUSESTA (ind:1) (cr=0 pr=0 pw=0 time=359 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=0 p='FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=9 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464337639
WAIT #0: nam='SQL*Net message from client' ela= 97 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1517516464337769

WAIT #2: nam='db file sequential read' ela= 55 file#=1 block#=62329 blocks=1 obj#=62713 tim=1517516464338835
pin ktewh25: kteinicnt dba 40f379:4 time 1439447005   --&gtdba:40f379=>TABLE:SYS.TT:0@SYSTEM(1,62329)
=====================
PARSING IN CURSOR #1 len=321 dep=1 uid=0 ct=3 lid=0 tim=1517516464339214 hv=1542133372 ad='66671638'
SELECT /* OPT_DYN_SAMP */
/*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
nvl(SUM(c1), 0), nvl(SUM(c2), 0)
FROM (SELECT /*+ NO_PARALLEL("TT") FULL("TT") NO_PARALLEL_INDEX("TT") */
         1 AS c1, 1 AS c2
          FROM "TT" "TT") samplesub
--&gt2    2   --没有统计信息,通过动态采样获取数据统计信息
END OF STMT
PARSE #1:c=0,e=187,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1517516464339208
EXEC #1:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1517516464339410
pin ktewh25: kteinicnt dba 40f379:4 time 1439447560      --&gtsegment header
pin ktewh26: kteinpscan dba 40f379:4 time 1439447596

WAIT #1: nam='db file sequential read' ela= 46 file#=1 block#=62330 blocks=1 obj#=62713 tim=1517516464339649
pin kdswh01: kdstgr dba 40f37a:1 time 1439447787 --&gtdata block

FETCH #1:c=0,e=314,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1517516464339761
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=3 pr=1 pw=0 time=328 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 bj=62713 p='TABLE ACCESS FULL TT (cr=3 pr=1 pw=0 time=271 us)'
=====================
PARSING IN CURSOR #2 len=17 dep=0 uid=0 ct=6 lid=0 tim=1517516464340599 hv=3576997614 ad='664107a8'
update tt set x=5
END OF STMT
PARSE #2:c=4000,e=2775,p=2,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=1517516464340593

pin ktewh25: kteinicnt dba 40f379:4 time 1439448956 --&gtsegment header
pin ktewh26: kteinpscan dba 40f379:4 time 1439448993
pin kdswh01: kdstgr dba 40f37a:1 time 1439449051 --&gtdata block

--更新第1行
pin kduwh01: kdusru dba 40f37a:1 time 1439449199
WAIT #2: nam='db file sequential read' ela= 55 file#=2 block#=25 blocks=1 obj#=0 tim=1517516464341551
pin ktuwh01: ktugus dba 800019:19 time 1439449688 --&gtdba:800019=>TYPE2 UNDO:SYS._SYSSMU2$:0@UNDOTBS1(2,25)
WAIT #2: nam='db file sequential read' ela= 43 file#=2 block#=3085 blocks=1 obj#=0 tim=1517516464341752
pin kcbwh2: kcbchg1 dba 800c0d:20 time 1439449882 --&gtdba:800c0d=>TYPE2 UNDO:SYS._SYSSMU2$:10@UNDOTBS1(2,3085)
pin release       427 ktuwh01: ktugus dba 800019:19
pin release       262 kcbwh2: kcbchg1 dba 800c0d:20
pin release       969 kduwh01: kdusru dba 40f37a:1

--更新第2行
pin kduwh01: kdusru dba 40f37a:1 time 1439450225       --&gtdata block
pin kcbwh5: kcbchg1 dba 800c0d:20 time 1439450262      --&gtundo data block
pin release        42 kcbwh5: kcbchg1 dba 800c0d:20
pin release       105 kduwh01: kdusru dba 40f37a:1
EXEC #2:c=0,e=1559,p=2,cr=3,cu=4,mis=0,r=2,dep=0,og=1,tim=1517516464342292
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464342375
WAIT #2: nam='SQL*Net message from client' ela= 333 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464342756
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*4个“db file sequential read”表示了4次物理读，分别是对段头和数据块以及回滚段头/数据块的读取
*4次pin操作，即4次“consistent gets”，前两次为对段头的pin操作,后两次是对数据块的pin操作
*存在release的pin共5次,对800c0d的pin操作记录一次,共4次db block gets
*其他pin操作共7次,依次是:
--pin ktewh25: kteinicnt dba 40f379:4 time 1439447005   --&gtsegment header
--pin ktewh25: kteinicnt dba 40f379:4 time 1439447560      --&gtsegment header
--pin ktewh26: kteinpscan dba 40f379:4 time 1439447596
--pin kdswh01: kdstgr dba 40f37a:1 time 1439447787         --&gtdata block
--pin ktewh25: kteinicnt dba 40f379:4 time 1439448956     --&gtsegment header
--pin ktewh26: kteinpscan dba 40f379:4 time 1439448993
--pin kdswh01: kdstgr dba 40f37a:1 time 1439449051        --&gtdata block
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
=====================
PARSING IN CURSOR #1 len=136 dep=0 uid=0 ct=3 lid=0 tim=1517516464342952 hv=1633362223 ad='664c0ab0'
SELECT pt.VALUE
FROM sys.v_$sesstat pt
WHERE pt.sid = :1
   AND pt.statistic# IN (7, 47, 50, 54, 134, 335, 336, 337, 341, 342)
ORDER BY pt.statistic#               --&gtautotrace stat
END OF STMT
PARSE #1:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517516464342947
EXEC #1:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1517516464343138
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464343187
FETCH #1:c=0,e=369,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1517516464343601
WAIT #1: nam='SQL*Net message from client' ela= 102 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464343762
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464343868
FETCH #1:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,tim=1517516464343917
WAIT #1: nam='SQL*Net message from client' ela= 92 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464344074
STAT #1 id=1 cnt=10 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=0 pr=0 pw=0 time=368 us)'
STAT #1 id=2 cnt=10 pid=1 pos=1 bj=0 p='FIXED TABLE FIXED INDEX X$KSUSESTA (ind:1) (cr=0 pr=0 pw=0 time=357 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=0 p='FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=9 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464344265
WAIT #0: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464344374
STAT #2 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE TT (cr=3 pr=2 pw=0 time=1425 us)'
STAT #2 id=2 cnt=2 pid=1 pos=1 bj=62713 p='TABLE ACCESS FULL TT (cr=3 pr=0 pw=0 time=146 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516464344522
WAIT #0: nam='SQL*Net message from client' ela= 4354744 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516468699306
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=0 ct=44 lid=0 tim=1517516468699490 hv=3480936638 ad='0'
commit
END OF STMT
PARSE #1:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1517516468699485
XCTEND rlbk=0, rd_only=0
pin ktuwh02: ktugus dba 800019:19 time 1443807788
pin release       119 ktuwh02: ktugus dba 800019:19
EXEC #1:c=0,e=316,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=1517516468699934
WAIT #1: nam='log file sync' ela= 314 buffer#=14476 p2=0 p3=0 obj#=0 tim=1517516468700348
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516468700412
WAIT #1: nam='SQL*Net message from client' ela= 285 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516468700753
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1517516468700857

附录:
CREATE or REPLACE FUNCTION get_tab_info_by_block_addr(p_block_addr in VARCHAR2)
return varchar2 as
l_block_addr INTEGER;
l_file_id    INTEGER;
l_block_id   INTEGER;
l_object_id INTEGER;
l_return     VARCHAR2(100);
BEGIN
l_block_addr := to_number(p_block_addr, 'xxxxxxxxxxxx');
l_file_id    := dbms_utility.data_block_address_file(l_block_addr);
l_block_id   := dbms_utility.data_block_address_block(l_block_addr);
SELECT /*+ rule */segment_type || ':' || owner || '.' || segment_name || ':' ||
         extent_id || '@' || tablespace_name
    INTO l_return
    FROM dba_extents
   WHERE file_id = l_file_id
     AND l_block_id BETWEEN block_id AND block_id + blocks - 1;
RETURN 'dba:'||p_block_addr||'=>'||l_return||'('||l_file_id||','||l_block_id||')';
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-710351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-710351/

cuian7084

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
logical_reads

1,逻辑读统计信息SELECT * FROM v$sysstatWHERE NAME IN ('session logical reads', 'db block gets', 'consistent gets',...
复制链接

扫一扫