eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 《深度解析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-



历史上的今天...
    >> 2017-03-30文章:
    >> 2012-03-30文章:
    >> 2011-03-30文章:
    >> 2009-03-30文章:
           关于ocssd进程的三言两语
    >> 2006-03-30文章:
           广告: 招聘SQL SERVER DBA
    >> 2005-03-30文章:

无觅

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

2 Comments

咦?大师测试的哪个版本?

我在linx86_10204的测试,并非像文中说的那样~

看这个输出,肯定是10g,你的测试结果和步骤show一下看看


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com