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

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

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

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

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

 

 

（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）客户端收到前台进程的信息与之交互，整个连接创建完成。

 

2、update t_gyj set name='gyj1' where id=1;

当这条sql发出来时，Oracle要做些什么呢？我们先来看一张图（来自DSI405的Library cache）

 

 

在整个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的方方面面。