ORACLE执行SQL过程

转载：用于学习

当我们提交一条sql语句时，oracle会做哪些操作呢？
Oracle会为每个用户进程分配一个服务器进程：service process（实际情况应该区分专用服务器和共享服务器），当service process接收到用户进程提交的sql语句时，服务器进程会对sql语句进行语法和词法分析。
名词解释：
语法分析：语句本身正确性。
词法分析：对照数据字典中检查表，索引，视图和用户权限。
检查通过后，服务器进程会将sql语句转变为ascii码，并通过一个hash函数将ascii码生成出一个hash值，服务器进程会到share pool中查询此hash是否存在，如果存在，服务器进程会从sharepool中读取已经解析好的语句来执行；如果不存在，则需要做以下步骤：生成执行计划和生成执行编码（请理解何为执行计划）。解析完成后，Oracle会将sql语句本身代码、hash值、编译代码、执行计划和所有与该语句相关的统计数据存放到sharepool中。

注意：
1 尽量写相同的sql语句，因为即使是from语句中table顺序的变化、查询字段位置的变化，甚至只是大小写的不同，都会促使oracle重新做一次硬解析。
2 增大share_pool_size可以保留更多的缓存在内存中的sql语句执行计划，也意味着共享sql的可能性的增大。

 

在生成编译代码后，service process会试图从db_buffer中读取是否存在相关的缓存数据。

下面我们分两种情况来说明：
1 db_buffer中不包含内存数据：service process会首先在表的头部请求一些行锁，申请成功后，将这些行所在的第一个block读入db_buffer。此时如果db_buffer空闲空间不足，则会触发写操作—DBWr。如果db_buffer剩余的空间不够存储新数据，就会触发DBWr进程，将db_buffer中脏数据写入数据文件。腾出来的空间写入新数据。
    注意：db_block是oracle最小的逻辑单位，即使我们所要求的数据只是一个block所包含的众多行中的一行或几行，我们仍然需要将整个block读入db_buffer。db_block的大小可以设置为8k的整数倍，并且可以针对不同的表空间设置不同的db_block_size的大小，一般建议在select多的表上将db_block_size设置大一些，而dml操作多的表上设置的小一些。
2 DBWr是写数据进程，触发DBWr进程的事件除了db_buffer空间不够外，ckpt进程也是触发DBWr的事件。

 

补充：
1 段是oracle最小的拓展单位。
2 ckpt进程：检查点进程。将scn写入日志文件，控制文件，数据文件头，数据块头部。触发ckpt进程的事件有alter system checkpoint，alter tablespace offline/begin back up和正常shutdown数据库。
3 scn：,system change number或者使用system commit number。scn号是oracle的逻辑时钟标志，我们可以理解为在commit时才会发生变化。Scn号是维持数据一致的重要标志，oracle实现备份恢复的数据一致性就是通过scn来判断。
block读入db_buffer后，service process会将这个块头部的SCN号和发生变更的行数据写入回滚段。当用户或者oracle回滚数据时就是通过回滚段和当前数据块实现数据的往前回滚。

解释：
回滚段是用来保存修改数据的前映像数据的，作用是保持并发操作时的读一致性，实现回滚等。回滚段过小会引发快照过旧错误。9i提供了专门的undo表空间，显然如果表空间级别的调整大小要比调整回滚段容易的多。

注意：
Insert操作：回滚段只需要记录rowid，如果回退，只需将该记录根据rowid删除即可；
Update操作：回滚段只需要记录发生变化的字段的前映像值，回滚时用前映像值覆盖更新值即可；
Delete操作：回滚段记录整行的数据，回滚时恢复整行数据；
做imp/exp或者大批量事务处理时，需要为当前事务创建一个大的回滚段，并将其他回滚段offline。
接着oracle会生成日志，server process会将被修改的数据的rowid、修改前的值、修改后的值、scn信息和回滚段中的相关信息写入redo log buffer，当发生以下操作时，LGWr会将redo log buffer中的数据写入磁盘上的online redo：时间超过1s、占用redo log buffer空间超过1/3、检查点进程、alter switch logfile和DBWr进程之前。

 

注意：
1 oracle中写数据的顺序为：1 读入db_buffer；2 写回滚段；3 写redo log buffer；4 改写db_buffer；5 写日志文件；6写数据文件；
commit并不会触发DBWr进程，即不会写入数据，commit只会触发写日志操作和写入scn号。但是任何的dml语句都会产生日志。
当一个联机日志文件写满后，LGWr会写入下一个联机日志，请记住联机日志是循环写，而控制文件是并发写。如果设置了为归档模式，归档进程会将前一个联机日志写入归档文件。

2 db_buffer中包含内存数据：首先判断用户执行的操作类型。
Select操作：首先判断db_buffer中的数据块头部是否存在事务，如果有，则说明数据块中的数据正在被事务处理，回滚段中存储着该数据的前映像，server price利用回滚段中的数据进行读一致性重构；如果数据块头部不存在事务，则有可能该数据已经被事务处理完毕但仍然留在db_buffer中，这时会比较select语句中scn号和db_buffer中的数据块头部的scn号，前者小于后者则说明此数据已经发生更改，处理数据同上，如果前者大于等于后者，则该数据为非脏数据，直接读取即可。
Update操作：无论数据块头部是否存在事务，又或者scn号之间孰大孰小，都需要服务器进程到表的头部申请行锁，申请成功则继续操作，不成功则等待加锁直至成功。