ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 77433|回复: 236

[精华] 一条UPDATE从生到死的整个过程的深入解析

[复制链接]
认证徽章
论坛徽章:
490
红宝石
日期:2014-04-05 19:53:18海蓝宝石
日期:2014-04-05 21:24:30数据库板块每日发贴之星
日期:2013-05-27 22:53:45版主6段
日期:2014-05-27 02:19:57青年奥林匹克运动会-羽毛球
日期:2014-09-24 08:37:59马上有房
日期:2015-01-03 10:23:28喜羊羊
日期:2015-03-04 14:54:422015年新春福章
日期:2015-03-06 11:59:47秀才
日期:2017-04-06 18:09:28嫦娥
日期:2014-03-04 16:46:45
发表于 2013-4-12 10:30 | 显示全部楼层 |阅读模式
本帖最后由 guoyJoe 于 2013-4-12 10:39 编辑

  这是曾经Oracle原厂的一位兄弟问我的一个问题,后来也有些人问起这个问题:
  用ORACLE内部原理描述以下过程:
1、sqlplus gyj/gyj@orcl
2、updatet_gyj set name='gyj1' where id=1;
3、commit;
4、exit

      一看这个问题是不是灰常复杂,阿里系的童鞋特别喜欢用类似这样的题目面试人,从中可以快速的判断你是哪个级别的DBA(初级/中级/高级),在这个问题上我给大家抛砖迎玉,由于能力有限只能做简单解释,欢迎大家一起参与讨论并补充。嘿嘿!这是个很理论的东东希望对大家在处理实际问题的时候有一点点帮助。

1、sqlplus gyj/gyj@orcl
这一块涉及到Oracle的网络连接的知识点。我们先来看一幅描述Oracle客户端与服务器网络连接的图,并给出了连接的整个步骤:

111111.png

(1)客户端SQL Plus请求连接,监听接受客户端的TCP连接,并获取客户端发过来的TNS数据包。

2)监听进程打开用于与子进程通信的管道,同时fork一个子进程,称为“监听子进程1的子进程,然后监听进程一直等待,直到这个“监听子进程1”结束。

3)监听子进程1 Fork出子进程2

4)完成上面一步,子进程1马上退出并结束子进程1

5)子进程2收集本进程所在的主机名、IP地址及进程号等信息,并把子进程2重名成server process(这里我们也把server process叫前台进程或叫服务器进程),申请占用一小块PGA内存。

6)前台进程把主机名、IP地址及进程号发送给监听进程。

7)监听进程收到前台进程的信息,并返回客户端的信息(比如用户密码环境变量等)给前台进程。

8)前台进程查询USER$PROFILE$等数据字典,校验用户名密码是否合法,如果用户密码错误就报错用户名密码无效,否则就与客户端进行交互。

9)客户端收到前台进程的信息与之交互,整个连接创建完成。


2update t_gyj set name='gyj1' where id=1;
当这条sql发出来时,Oracle要做些什么呢?我们先来看一张图(来自DSI405Library cache


2222222.png

在整个SGA中最复杂的就是sharedpool,而shared pool中最复杂就是library cache,这里对它的机制不做详细讨论,简单讲讲sql在里面的运作流程。


首先sql(update t_gyjset name='gyj1' where id=1)的每个字符当然包括空格转化成ASCII码后,再拿这一堆ASCII码通过HASH函数生成一个sql_hash值,Oracle拿着这个sql_hash值去描扫HASH Buckets(看上面的图,这个幅画的不太好,只画了0号的HASH BUCKETS),假如刚好sql_hash值=0,那么Oracle就延着0号HASH Buckets去搜索Object Handle链,在这个Object Handle上存有sql的文本,如果和我们的update t_gyj set name='gyj1' where id=1一模一样对上,那就说明这条sql已被缓存在共享池了,这个过程就是软解析。当然再往下我就不说了,再说下去很复杂了父子游标,最后执行计划是被存放在堆6中。


   好,那么如果通过上面的方式在Object Handle链没搜索到这条sql的文本,那说明sql不在共享池中,这个时侯就要做硬解析(过程大要做语法,语义,权限,查询视图展开、划分小的查询块、sql等价转换、代价估算、最后生成执行计划),这个代价会有点高,如果有大量的硬解析那会消耗CPU和占用共享池。

   其实解析还有:软软解析、无解析。。。。嘻嘻!这里不细说了,先简单说到这里,我们再往下看。

(一)如果ID列上无索引
(1)查询SEG$等数据字典,找到T_GYJ表段头
(2)从段头读出Extent Map,开始全扫描
(3)找到第一个满足条件的行,进行修改
(4)查找同一块中剩下的行,先构造一个CR块,在CR块中继续查找,如果又找到满足条件的行,在Xcur块中修改。

(二)如果ID列上有索引,且版本不是11GR1(10G、11GR2),则不需要构造CR块

(三)ID列无论是否有索引,在11GR1下都需要构造CR块。

(四)如果NAME列上有索引,增加索引维护步骤:
(1)先在原索引块中删除要修改的原值
(2)、再将新值插入

(五)任何块的修改,都有以下步骤(非IMU)
(1)在PGA中生成UNDO段头事务表的后映像(5.2)
(2)在PGA中生成UNDO块的后映像(5.1)
(3)在PGA中生成DataBlock块的后映像(11.9)
(4)将前三个Redo矢量做为一条Redo Recorder写入Log buffer
(5)修改UNDO段头的事务表,事务正式开始。
(6)修改UNDO块,写入DataBlock的前映像。
(7)修改DataBlock,将新值“gyj1”写入Buffer cache。

(六)任何块的修改,都有以下步骤(IMU)
(1)在PGA中生成DataBlock块的后映像(11.9)
(2)在PGA中生成UNDO段头事务表的后映像(5.2)
(3)在PGA中生成UNDO块的后映像(5.1)
(4)将前三个Redo矢量做为一条Redo Recorder写入Shared pool中的Private strand。
(5)将DataBlock中的前映像值,写入Shared pool中的Imu pool。
(6)修改UNDO段头的事务表。
(7)修改UNDO块,写入DataBlock的前映像。
(8)修改DataBlock,将新值“gyj1”写入Buffer cache。

3、commit;
(一)非IMU下(按最常见的快速提交):
(1)在PGA中生成Commit的Redo 信息(编号5.4),另做为一条Redo recorder,写入Log buffer
(2)修改事务表相应Slot,声明事务已提交。
(3)修改DataBlock,在ITL Slot中写入快速提交标志和SCN。每行上的行锁不清0。
(4)通知Lgwr,将Log buffer写入Redo file。
(5)收到Lgwr通知,写入完成。
(6)向用户发收提交完成信息。

(一)IMU下(按最常见的快速提交):
(1)在PGA中生成Commit的Redo 信息(编号5.4),传入Shared pool中的Private strand,追加在事务之前的Redo recorder之后。
(2)修改事务表相应Slot,声明事务已提交。
(3)修改DataBlock,在ITL Slot中写入快速提交标志和SCN。每行上的行锁不清0。
(4)将Private Strand中的Redo数据写入Log buffer。
(5)通知Lgwr,将Log buffer写入Redo file。
(6)收到Lgwr通知,写入完成。
(7)向用户发收提交完成信息。

4、exit
(1)、断开连接,中止服务器进程,释放PGA

最后附:dump的信息(针对update t_gyj set name='gyj1' where id=1;),有兴趣大家可以去dump一下这样可以更深入了解Oracle,使之成为自己的体系结构。


1.      redolog的dump
alter system dump logfile '/u01/app/oracle/oradata/ocm/redo02.log ';

REDO RECORD - Thread:1 RBA:0x0000d4.00000088.015c LEN: 0x00a4 VLD: 0x01
SCN: 0x0000.00f5e121 SUBSCN:  1 04/12/2013 08:45:43
CHANGE #1 TYP:0 CLS:23 AFN:6 DBA:0x018000b0OBJ:4294967295 SCN:0x0000.00f5e120 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0013 sqn: 0x00005a4dsrt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x01800c05.0747.34 ext: 6 spc: 578 fbi:0
CHANGE #2 MEDIA RECOVERY MARKERSCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0

REDORECORD - Thread:1 RBA: 0x0000d4.00000089.0010 LEN: 0x0214 VLD: 0x05
SCN: 0x0000.00f5e123 SUBSCN:  1 04/12/2013 08:45:48
CHANGE #1 TYP:0 CLS:17 AFN:6 DBA:0x01800080OBJ:4294967295 SCN:0x0000.00f5e114 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0018 sqn: 0x000057efflg: 0x0012 siz: 164 fbi: 0
           uba: 0x018021ba.0724.0f   pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:18 AFN:6 DBA:0x018021baOBJ:4294967295 SCN:0x0000.00f5e113 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 6094 flg: 0x0012seq: 0x0724 rec: 0x0f
           xid:  0x0001.018.000057ef
ktubl redo: slt: 24 rci: 0 opc: 11.1 [objn:77365 objd: 77365 tsn: 7]
Undo type: Regular undo        Begintrans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
            0x00000000  prev ctl uba:0x018021ba.0724.0e
prev ctl max cmt scn:  0x0000.00f5dc35  prev tx cmt scn:  0x0000.00f5dc3f
txn start scn:  0xffff.ffffffff  logon user: 91  prev brb: 25174454  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
CHANGE #2 MEDIA RECOVERY MARKERSCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0

REDO RECORD - Thread:1 RBA:0x0000d4.00000089.0010 LEN: 0x0214 VLD: 0x05
SCN: 0x0000.00f5e123 SUBSCN:  1 04/12/2013 08:45:48
CHANGE #1 TYP:0 CLS:17 AFN:6 DBA:0x01800080OBJ:4294967295 SCN:0x0000.00f5e114 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0018 sqn: 0x000057efflg: 0x0012 siz: 164 fbi: 0
           uba: 0x018021ba.0724.0f   pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:18 AFN:6 DBA:0x018021baOBJ:4294967295 SCN:0x0000.00f5e113 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 6094 flg: 0x0012seq: 0x0724 rec: 0x0f
           xid:  0x0001.018.000057ef
ktubl redo: slt: 24 rci: 0 opc: 11.1 [objn:77365 objd: 77365 tsn: 7]
Undo type: Regular undo        Begintrans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
            0x00000000  prev ctl uba:0x018021ba.0724.0e
prev ctl max cmt scn:  0x0000.00f5dc35  prev tx cmt scn:  0x0000.00f5dc3f
txn start scn:  0xffff.ffffffff  logon user: 91  prev brb: 25174454  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000  bdba:0x00c0027d  hdba: 0x00c0027a
itli: 2 ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0ckix: 0
ncol: 2 nnew: 1 size: 2
col 1: [ 6]  41 41 41 41 41 41
CHANGE #3 TYP:2 CLS:1 AFN:3 DBA:0x00c0027dOBJ:77365 SCN:0x0000.00f5e0cc SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid:  0x0001.018.000057ef    uba: 0x018021ba.0724.0f
Block cleanout record, scn:  0x0000.00f5e123 ver: 0x01 opt: 0x02, entriesfollow...
itli: 1  flg: 2  scn: 0x0000.00f5e0cc
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000  bdba:0x00c0027d  hdba: 0x00c0027a
itli: 2 ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2ckix: 0
ncol: 2 nnew: 1 size: -2
col 1: [ 4]  67 79 6a 31
CHANGE #4 MEDIA RECOVERY MARKERSCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number   = 162
serial number   = 7
transaction name =
version 186646784
audit sessionid 1362603
Client Id =

2.      undo的dump
(1)    对应OP=5.2的操作

alter system dump undo header"_SYSSMU1_1918248848$";


index  state cflags wrap#    uel         scn            dba            parent-xid    nub    stmt_num    cmt

------------------------------------------------------------------------------------------------

0x18   10    0x80 0x57ef  0x0010  0x0000.00f5e123  0x018021ba 0x0000.000.00000000 0x00000001   0x00000000  0



(2)    对应OP=5.1的操作,即undo块数据
alter systemdump datafile 6 block 8634;

* Rec #0xf  slt:0x18  objn: 77365(0x00012e35)  objd: 77365 tblspc: 7(0x00000007)

*       Layer: 11 (Row)   opc: 1   rci 0x00  
Undotype:  Regular undo    Begin trans    Last buffer split:  No
TempObject:  No
TablespaceUndo:  No
rdba:0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba:0x018021ba.0724.0e ctl max scn: 0x0000.00f5dc35 prv tx scn: 0x0000.00f5dc3f
txn startscn: scn: 0x0000.00f5e123 logon user: 91
prev brb: 25174454 prev bcl: 0
KDO undorecord:
KTB Redo
op:0x03  ver: 0x01  
compatbit: 4 (post-11) padding: 1
op: Z
KDO Opcode: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00c0027d  hdba: 0x00c0027a
itli:2  ispac: 0  maxfr: 4858
tabn: 0slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2nnew: 1 size: 2
col  1: [ 6]  41 41 41 41 41 41

3.      data的dump
对应OP=11.9的操作
alter system dump datafile 3 block 637

Object idon Block? Y
seg/obj: 0x12e35  csc: 0x00.f5e123  itc: 2 flg: E  typ: 1 - DATA
     brn: 0 bdba: 0xc00278 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl          Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0003.00d.00007825  0x01802b0c.0a53.2d  C---   0  scn 0x0000.00f5e0cc
0x02   0x0001.018.000057ef  0x018021ba.0724.0f  ----   1  fsc 0x0002.00000000
bdba:0x00c0027d
data_block_dump,dataheader at 0xa5d664
===============
tsiz:0x1f98
hsiz:0x14
pbl:0x00a5d664
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f80
avsp=0x1f77
tosp=0x1f79
0xe:pti[0]      nrow=1 offs=0
0x12:pri[0]     offs=0x1f80
block_row_dump:
tab 0,row 0, @0x1f80
tl: 11fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2] c1 02
col  1: [ 4]  67 79 6a 31
  

附OPCODE:
Layer 5 : Transaction Undo -  KCOCOTUN    [ktucts.h]
        Opcode 1 : Undo block or undo segment header - KTURDB
        Opcode 2 : Update rollback segment header - KTURDH
        Opcode 3 : Rollout a transaction begin
        Opcode 4 : Commit transaction (transaction table update)
- no undo record
        Opcode 5 : Create rollback segment (format) - no undo record
        Opcode 6 : Rollback record index in an undo block - KTUIRB
        Opcode 7 : Begin transaction (transaction table update)
        Opcode 8 : Mark transaction as dead
         Opcode 9 : Undo routine to rollback theextend of a rollback segment
        Opcode 10 :Redo to perform the rollback of extend of rollback segment
                    to the segment header.
        Opcode 11 :Rollback DBA in transaction table entry - KTUBRB
        Opcode 12 :Change transaction state (in transaction table entry)
        Opcode 13 :Convert rollback segment format (V6 -> V7)
        Opcode 14 :Change extent allocation parameters in a rollback segment
        Opcode 15 :
         Opcode 16 :
        Opcode 17 :
        Opcode 18 :
        Opcode 19 : Transaction start audit log record
        Opcode 20 : Transaction continue audit log record     
        Opcode 24 : Kernel Transaction Undo Relog CHanGe – KTURLGU
Layer 11 : Row Access -  KCOCODRW    [kdocts.h]
        Opcode 1 : Interpret Undo Record (Undo)
        Opcode 2 : Insert Row Piece
        Opcode 3 : Drop Row Piece
        Opcode 4 : Lock Row Piece
        Opcode 5 : Update Row Piece
        Opcode 6 : Overwrite Row Piece
        Opcode 7 : Manipulate First Column (add or delete the 1rst column)
        Opcode 8 : Change Forwarding address
        Opcode 9 : Change the Cluster Key Index
        Opcode 10 :Set Key Links (change the forward & backward key links
                    on a cluster key)
        Opcode 11 :Quick Multi-Insert (ex: insert as select …)
        Opcode 12 :Quick Multi-Delete
        Opcode 13 :Toggle Block Header flags

注:11.19是在11g中才有,是11.5的改进update操作码。

  Oracle的魅力也许就在于任何一个小概念都可以讲出一大堆东西, 这里没有涉及到锁、Latch、Mutex等,要整的东西太实在太多了,这几个步骤完完整整写下来可以出一本书了,涉及到Oracle的方方面面。

论坛徽章:
6
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:592012新春纪念徽章
日期:2012-01-04 11:58:18奥运会纪念徽章:射击
日期:2012-07-02 14:53:30奥运会纪念徽章:自行车
日期:2012-07-02 17:06:22ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00马上有房
日期:2014-12-12 15:03:41
发表于 2013-4-12 10:39 | 显示全部楼层
,学习中。。。

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6329
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:25马上加薪
日期:2017-01-10 16:49:34马上有钱
日期:2017-01-10 16:49:34马上有钱
日期:2014-12-26 15:39:08马上有钱
日期:2014-12-26 15:39:08马上有房
日期:2014-12-26 15:42:55马上有车
日期:2017-01-10 16:49:34马上有钱
日期:2014-12-31 17:16:56
发表于 2013-4-12 10:42 | 显示全部楼层
GD

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2013-4-12 10:43 | 显示全部楼层
牛逼,各种分析

使用道具 举报

回复
论坛徽章:
6
复活蛋
日期:2013-04-02 15:30:42授权会员
日期:2013-09-04 15:41:58本田
日期:2013-09-26 12:27:372014年新春福章
日期:2014-02-18 16:49:31马上有钱
日期:2014-02-18 16:49:31玉石琵琶
日期:2014-03-04 16:46:07
发表于 2013-4-12 10:49 | 显示全部楼层
说起补充,也就能写出来一本书了,呵呵。。总结的不错。

使用道具 举报

回复
认证徽章
论坛徽章:
2
2013年新春福章
日期:2013-02-25 14:51:24ITPUB社区OCM联盟徽章
日期:2014-06-09 10:24:39
发表于 2013-4-12 10:59 | 显示全部楼层
very well..

使用道具 举报

回复
论坛徽章:
1
2013年新春福章
日期:2013-02-25 14:51:24
发表于 2013-4-12 11:08 | 显示全部楼层
mark

使用道具 举报

回复
论坛徽章:
0
发表于 2013-4-12 11:09 | 显示全部楼层
mark

使用道具 举报

回复
论坛徽章:
1
鲜花蛋
日期:2012-05-24 10:00:17
发表于 2013-4-12 11:11 | 显示全部楼层
看的有点头大了!要学的东西真多

使用道具 举报

回复
论坛徽章:
9
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-03-06 17:43:59鲜花蛋
日期:2013-04-26 22:57:09蛋疼蛋
日期:2013-06-05 15:38:56林肯
日期:2013-08-16 16:46:322015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2013-4-12 11:14 | 显示全部楼层
perfect

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表