如何理解oracle执行sql语句的过程！

Question

站在DBA的角度阐述下！谢谢！

Answer 1

用户执行sql语句
user process把语句送给server process
server process查看shared pool是否有sql的解析
如果有就直接用，如果没有就parse sql，表、列、权限等检验
生成parse tree、执行计划
process 检查要访问的数据是否在data buffer cache中存在
如果不存在就访问数据文件，把对于的数据cache到data buffer cache
server process处理data buffer中的数据
commit后，写redo buffer、redo logfile。
chekpoint后写dirty buffer 到data file
、、、、、
大体上是如此

Answer 2

楼上的DBA解释的不错，我也来学习

Answer 3

当我们提交一条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中脏数据写入数据文件。腾出来的空间写入新数据。
注意：
1 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进程之前。
注意：
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号之间孰大孰小，都需要服务器进程到表的头部申请行锁，申请成功则继续操作，不成功则等待加锁直至成功。

Answer 4

当我们提交一条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中脏数据写入数据文件。腾出来的空间写入新数据。
注意：
1 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进程之前。