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

一条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