« 《深度解析Oracle》之《从等待分析开始》 | Blog首页 | 我的新家 我的北京五年 »

resize datafile 与 checkpoint

Resize datafile会触发一个文件级检查点，今天讲课的时候和大家提到过这个，记录一下。

SQL> select file#,checkpoint_change# from v$datafile;

    FILE# CHECKPOINT_CHANGE#
---------- ------------------
        1            695055
        2            695055
        3            695055
        4            695244

SQL> alter database datafile 4 resize 150M;

数据库已更改。

SQL> select file#,checkpoint_change# from v$datafile;

    FILE# CHECKPOINT_CHANGE#
---------- ------------------
        1            695055
        2            695055
        3            695055
        4            695513

通过10046事件的跟踪可以得到如下信息：

PARSING IN CURSOR #1 len=37 dep=0 uid=55 oct=35 lid=55 tim=3564309578 hv=1298948616 ad='1b676e34'
alter database datafile 4 resize 150M
END OF STMT
PARSE #1:c=0,e=575,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3564309572
BINDS #1:
WAIT #1: nam='control file sequential read' ela= 315 file#=0 block#=1 blocks=1 obj#=15 tim=3564313625
WAIT #1: nam='control file sequential read' ela= 12452 file#=1 block#=1 blocks=1 obj#=15 tim=3564326138
WAIT #1: nam='control file sequential read' ela= 306 file#=0 block#=16 blocks=1 obj#=15 tim=3564326477
WAIT #1: nam='control file sequential read' ela= 309 file#=0 block#=18 blocks=1 obj#=15 tim=3564326830
WAIT #1: nam='control file sequential read' ela= 304 file#=0 block#=24 blocks=1 obj#=15 tim=3564327167
WAIT #1: nam='control file sequential read' ela= 272 file#=0 block#=1 blocks=1 obj#=15 tim=3564327531
WAIT #1: nam='control file sequential read' ela= 294 file#=1 block#=1 blocks=1 obj#=15 tim=3564327853
WAIT #1: nam='control file sequential read' ela= 306 file#=0 block#=16 blocks=1 obj#=15 tim=3564328185
WAIT #1: nam='control file sequential read' ela= 302 file#=0 block#=18 blocks=1 obj#=15 tim=3564328516
WAIT #1: nam='control file sequential read' ela= 265 file#=0 block#=24 blocks=1 obj#=15 tim=3564328811
WAIT #1: nam='control file sequential read' ela= 272 file#=0 block#=29 blocks=1 obj#=15 tim=3564329113
WAIT #1: nam='control file sequential read' ela= 287 file#=0 block#=1 blocks=1 obj#=15 tim=3564329537
WAIT #1: nam='control file sequential read' ela= 314 file#=1 block#=1 blocks=1 obj#=15 tim=3564329879
WAIT #1: nam='control file sequential read' ela= 302 file#=0 block#=16 blocks=1 obj#=15 tim=3564330208
WAIT #1: nam='control file sequential read' ela= 265 file#=0 block#=18 blocks=1 obj#=15 tim=3564330500
WAIT #1: nam='control file sequential read' ela= 285 file#=0 block#=24 blocks=1 obj#=15 tim=3564330814
WAIT #1: nam='db file sequential read' ela= 8821 file#=4 block#=1 blocks=1 obj#=15 tim=3564339667
WAIT #1: nam='control file sequential read' ela= 33339 file#=0 block#=1 blocks=1 obj#=15 tim=3564373162
WAIT #1: nam='control file sequential read' ela= 437 file#=1 block#=1 blocks=1 obj#=15 tim=3564373650
WAIT #1: nam='control file sequential read' ela= 24730 file#=0 block#=16 blocks=1 obj#=15 tim=3564398418
WAIT #1: nam='control file sequential read' ela= 23061 file#=0 block#=18 blocks=1 obj#=15 tim=3564421533
WAIT #1: nam='control file sequential read' ela= 399 file#=0 block#=24 blocks=1 obj#=15 tim=3564421972
WAIT #1: nam='control file sequential read' ela= 50820 file#=0 block#=29 blocks=1 obj#=15 tim=3564472834
WAIT #1: nam='db file sequential read' ela= 310 file#=4 block#=1 blocks=1 obj#=15 tim=3564473221
WAIT #1: nam='control file sequential read' ela= 60260 file#=0 block#=1 blocks=1 obj#=15 tim=3564578332
WAIT #1: nam='control file sequential read' ela= 358 file#=1 block#=1 blocks=1 obj#=15 tim=3564578746
WAIT #1: nam='control file sequential read' ela= 1275 file#=0 block#=16 blocks=1 obj#=15 tim=3564580057
WAIT #1: nam='control file sequential read' ela= 27222 file#=0 block#=18 blocks=1 obj#=15 tim=3564607310
WAIT #1: nam='control file sequential read' ela= 44185 file#=0 block#=276 blocks=1 obj#=15 tim=3564651733
WAIT #1: nam='rdbms ipc reply' ela= 156 from_process=7 timeout=21474836 p3=0 obj#=15 tim=3564652138
WAIT #1: nam='rdbms ipc reply' ela= 111237 from_process=7 timeout=21474836 p3=0 obj#=15 tim=3564763405
WAIT #1: nam='enq: TC - contention' ela= 140565 name|mode=1413677062 checkpoint ID=65561 0=0 obj#=15 tim=3564904012
WAIT #1: nam='control file sequential read' ela= 15946 file#=0 block#=1 blocks=1 obj#=15 tim=3564920080
WAIT #1: nam='control file sequential read' ela= 46993 file#=1 block#=1 blocks=1 obj#=15 tim=3564967119
WAIT #1: nam='control file sequential read' ela= 507 file#=0 block#=16 blocks=1 obj#=15 tim=3564967713
WAIT #1: nam='control file sequential read' ela= 41552 file#=0 block#=18 blocks=1 obj#=15 tim=3565009295
WAIT #1: nam='control file sequential read' ela= 34059 file#=0 block#=24 blocks=1 obj#=15 tim=3565043406
WAIT #1: nam='db file sequential read' ela= 8263 file#=4 block#=1 blocks=1 obj#=15 tim=3565051844
WAIT #1: nam='db file single write' ela= 11451 file#=4 block#=1 blocks=1 obj#=15 tim=3565064066
WAIT #1: nam='control file parallel write' ela= 10822 files=2 block#=23 requests=2 obj#=15 tim=3565074960
WAIT #1: nam='control file parallel write' ela= 662 files=2 block#=17 requests=2 obj#=15 tim=3565075670
WAIT #1: nam='control file parallel write' ela= 141511 files=2 block#=15 requests=2 obj#=15 tim=3565217227
WAIT #1: nam='control file parallel write' ela= 29720 files=2 block#=1 requests=2 obj#=15 tim=3565247022
WAIT #1: nam='db file sequential read' ela= 1351 file#=4 block#=1 blocks=1 obj#=15 tim=3565248434
WAIT #1: nam='db file single write' ela= 648 file#=4 block#=1 blocks=1 obj#=15 tim=3565249118
WAIT #1: nam='control file parallel write' ela= 82890 files=2 block#=18 requests=2 obj#=15 tim=3565332047
WAIT #1: nam='control file parallel write' ela= 70058 files=2 block#=16 requests=2 obj#=15 tim=3565402181
WAIT #1: nam='control file parallel write' ela= 10081 files=2 block#=1 requests=2 obj#=15 tim=3565412337
WAIT #1: nam='rdbms ipc reply' ela= 100245 from_process=5 timeout=21474836 p3=0 obj#=15 tim=3565891056
XCTEND rlbk=0, rd_only=1
EXEC #1:c=0,e=1742396,p=0,cr=0,cu=4,mis=0,r=0,dep=0,og=1,tim=3566052040
WAIT #1: nam='log file sync' ela= 17727 buffer#=1501 p2=0 p3=0 obj#=15 tim=3566070054
WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=15 tim=3566070146
WAIT #1: nam='SQL*Net message from client' ela= 4508479 driver id=1111838976 #bytes=1 p3=0 obj#=15 tim=3570578677

=====================
注意这里的几个重要等待：

WAIT #1: nam='enq: TC - contention' ela= 140565 name|mode=1413677062 checkpoint ID=65561 0=0 obj#=15 tim=3564904012
WAIT #1: nam='db file single write' ela= 11451 file#=4 block#=1 blocks=1 obj#=15 tim=3565064066
WAIT #1: nam='db file sequential read' ela= 1351 file#=4 block#=1 blocks=1 obj#=15 tim=3565248434
WAIT #1: nam='db file single write' ela= 648 file#=4 block#=1 blocks=1 obj#=15 tim=3565249118

这里的TC队列就是线程检查点（Thread Checkpoint）的含义，后面同时记录了checkpoin ID。
db file single write 在文档里这样记录：This event is used to wait for the writing of the file headers.
也就是指对于数据文件头的写入操作，这就是和检查点操作对应的写操作。
注意，随后的db file sequential read也是对于数据文件4，block号为1开始，读取的第一个数据块。

-The End-

By eygle on 2008-03-30 21:50 | Comments (2) | Internal | 1861 |

Checkpoint