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-