一条UPDATE从生到死的整个过程的深入解析
用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要做些什么呢？我们先来看一张图.
在整个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
浙公网安备 33010602011771号