深入简出 sql tuning.pptVIP

* * SQL Statement Processing Phases 理解SQL语句是怎么样执行的有助与你编写性能更高的SQL语句。 在SQL语句执行过程中，存在4个重点的步骤：编译，变量绑定，执行，数据返回。 编译： 1.检查SQL语句的语法、语意 2.检查执行权限 3. Oracle 首先在library cache中匹配SQL语句是否存在，来决定是否分配私有内存区域给SQL语句 说明软解析，硬解析 绑定： 检查语句绑定变量，确定是否分配一个值分配给绑定变量 执行:略 返回记录： 获取记录，排序 * * DML Processing Steps A data manipulation language (DML) statement requires only two phases of processing: Parse is the same as the parse phase used for processing a query. Execute requires additional processing to make data changes. DML Execute Phase To execute a DML statement: 1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache. The server process locks the rows that are to be modified. 2. The server process records the changes to be made to the data buffers as well as the undo changes. These changes are written to the redo log buffer before the in-memory data and rollback buffers are modified. This is called write-ahead logging. 3. The rollback buffers contain values of the data before it is modified. The rollback buffers are used to store the before image of the data so that the DML statements can be rolled back if necessary. The data buffers record the new values of the data. 4. The user gets the feedback from the DML operation (such as how many rows were affected by the operation). * Fast COMMIT The Oracle Database uses a Fast COMMIT mechanism that guarantees the committed changes can be recovered in case of instance failure. System Change Number Whenever a transaction commits, the Oracle Database assigns a commit system change number (SCN) to the transaction. The SCN is monotonically incremented and is unique within the database. It is used by the Oracle Database as an internal time stamp to synchronize data and to provide read consistency when data is retrieved from the data files. Using the SCN enables the Oracle Database to perform consistency checks without depending on the date and time of the operating sy