oracle体系结构,事务机制,sql优化培训讲义

1、ORACLE培训讲义提要：首先介绍oracle的组成结构，然后讲解oracle事务机制原理，最后介绍针对sql优化的基本理论与注意事项。一ORACLE体系结构1.1物理数据库结构：包括好几种文件结构，这里只列出经常涉及到的几种。1）数据文件 data file 所有数据均存储在数据文件中；一个数据文件只与一个数据库有关，具有自动扩展特性；一个或多个数据文件组成一个逻辑单元表空间；数据文件的数据在正常数据库操作中被读取并被缓存到oracle内存缓冲中。更改或新的数据并不需要马上写入到数据文件中。为了减少磁盘访问提高性能，数据在内存缓冲，由oracle使用DBWR进程将数据一次性写入到数据文件中。

2、相关数据字典：v$tablespace,v$datafile2）控制文件 control file 控制文件是数据库的心脏，它包含以下信息：属于数据库的数据文件和重做日志文件信息、数据库中的数据应该以何种字符集存储的信息、数据库中每个数据文件的状态和版本信息、以及其他的重要信息。包含在控制文件中的大部分参数是在数据库创建过程中设定的，不是经常改变的。控制文件采用二进制格式存储特点：一个数据库最少需要一个控制文件，但一般情况下都会有复用/副本。相关数据字典：v$controlfile3）重做日志文件 redo log file 重作日志文件是存放联机重做日志(Online Redo Log)的文

3、件。重做日志 (Redo Log)也称作事务日志( Transaction Log)，保存针对数据库进行的修改操作或事务。因为所有的处理都记录在联机重做日志中，因此数据库系统可以使用这些事务记录进行恢复操作。重做日志以循环方式工作，对每一个 ORACLE数据库都要求至少具有两个联机重做日志。如果数据库运行在ARCHIVELOG模式下，所有的事务重做日志都将保存。这意味着对数据库进行的所有事务都留有一个备份，尽管重做日志以循环方式工作，但在一个重做日志被覆盖前均将为其建立一个拷贝。在这种方式下，如果在拷贝完成之前，数据库要求交换重做日志(发生Log Switch)，则在重做日志拷贝工作完成之前，

4、ORACLE将停止一切新的操作，在旧的事务记录完成之前ORACLE不对其进行覆盖。有了所有事务的拷贝，数据库就可以从所有类型的失败中恢复，包括用户错误或磁盘崩溃。这是一种最安全的数据库工作方式。相关数据字典：V$log,v$logfileTips:如何开启/关闭归档模式如果开启归档，建议保证参数log_archive_start=true，即开启自动归档，否则只能手工归档，如果是关闭了归档，则设置该参数为false1.开启归档 a. 关闭数据库shutdown immediate b. startup mount c. alter database archivelog d. alter da

5、tabase opne2、禁止归档 a. 关闭数据库shutdown immediate b. startup mount c. alter database noarchivelog d. alter database open注：查询当前数据库是否是位于归档模式，以及详细的归档信息可以通过如下语句查看(需要以dba权限登陆) SQL> archive log list或者通过下列sql语句进行查询：select log_mode from v$database;4）初始化参数文件 parameter file Oracle9i之前，参数文件为文本格式pfile,从Oracle9i开始

6、化引入了spfile。 在 9i以前，Oracle 使用 pfile存储初始化参数设置，这些参数在实例启动时被读取，任何修改需要重起实例才能生效；使用 spfile 你可以使用 ALTER SYSTEM或者 ALTER SESSION来动态修改那些可动态修改的参数，所有更改可以立即生效，你可以选择使更改只应用于当前实例还是同时应用到 spfile。SPFILE 是一个二进制文件。PFILE与SPFILE的相互转换使用pfile创建spfileCREATE SPFILE=SPFILE-NAME FROM PFILE=PFILE-NAME 例： SQL> create spfile from

7、 pfile;使用spfile创建pfileCREATE PFILE=SPFILE-NAME FROM SPFILE 例： SQL> create pfile from spfile;spfile模式下如何修改系统参数语法：alter system set 参数名=值 scope=both/memory/pflie其中看出，scope选项有三个含义，分别表示：MEMORY: 只改变当前实例运行 SPFILE: 只改变 SPFILE 的设置 BOTH: 改变实例及 SPFILE如何判断当前使用的是spfile还是pfile管理模式判断是否使用了 SPFILE，可以使用以下方法： 1查询 v

8、$parameter动态视图，如果以下查询返回空值，那么你在使用 pfile. SELECT name,value FROM v$parameter WHERE name='spfile' 2使用 SHOW 命令 SQL> SHOW PARAMETER spfile 3查询 v$spparameter视图 SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; 5）归档日志文件archived log file 数据库位于归档模式运行时，日志切换时对于已写满的重做日志文件产生的拷贝。1.2逻辑

9、数据库结构数据块空间分配的单位是block，extent与segment。oracle以extent为单位给segment分配空间。由于extent是按需分配，因此段的扩展在磁盘上可能不连续。在表空间内，segment可以跨多个数据文件，但是extent只能在某个数据文件内。1）Oracle数据块,blockOracle在最小的粒度级别上以数据块的形式存储数据。一个数据块的大小由db_block_size决定。数据块的分类主要包括：表块、索引快、簇集块等。不管是table block还是index block，数据块的存储格式是相似的。Tips: PCTFREE,PCTUSED,Freelis

10、t这是三个同数据块空间使用密切相关的3个术语PCTFREE和PCTUSED是应用于段的两个存储参数，Oracle使用PCTFREE和PCTUSED参数的组合确定块有没有足够的空间接受新的记录。 PCTFREE：该参数用于指定在向块中插入新行时应该保留的自由空间的百分数，该保留空间用于修改已包含在该块中的行时使用。比如在建表CREATE TABLE 语句中指定该参数为：PCTFREE=20，则在向该表插入新的数据行时，其每个数据块空间最多只能使用80%，一旦达到80%，就不能再向该块插入数据行。留下的20%空间留作此后修改该块中的行时使用。 PCTUSED：该参数是一个限定值。当通过删除行或更新

11、行（减少了块的存储使用量）而使数据库块的使用百分数低于pctused时，ORACLE又许可向该块插入新的数据行。PCTFREE与PCTUSED两参数之和要小于或等于100。 Freelist维护了当前段中可用块的列表,存放在表或者索引的第一个块中，这个块也被称为段头（segment header）。 pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。从oracle9i开始引入了ASSM(Auto Segment Space Management)自动段空间管理机制后，PCTUSED与FreeList就不再起作用了。ORACLE宣称ASSM可以有效降低因

12、为并发DML等原因引起Freelist争用而引起的性能问题。2）扩展（extents）一个extents是特定数目的连续数据块。3）段（Segment）段是分配给特定对象的extents的集合，主要分为：i).数据段：每一个普通表均有一个数据段，对分区表来说，每个分区有一个数据段ii).索引段：每一索引均有一索引段。对分区索引来说每个分区有一个索引段iii).临时段：iv).回滚段：回滚段（rollback segment）存储在数据库事务中发生改变的原始数据块。它们用于提供数据的已经改变但尚未提交的读一致性视图。当做出数据改变时，原始数据被拷到回滚段中，而且在内存缓冲区中对数据块做出更改。如

13、果其他用户会话要求同样的数据，那么存储在回滚段中的原始数据就会被返回。1.3内存结构ORACLE存在两种类型的内存结构：一种是系统全局区(System Global Area)，也称SGA；另一种是程序全局区(ProgramGlobalArea)，也称PGA。1.3.1 系统全局区SGA系统全局区( SGA )是ORACLE数据库存放系统信息的一块内存区域，作用相当于所有用户进程的一个共享区域或通讯器，所有的用户进程和服务器进程都可以访问这个内存结构SGA组成如下：1) 数据高速缓冲区(Data Buffer Cache)在数据高速缓冲区中存放着 ORACLE系统最近使用过的数据库数据块。换句

14、话说， DataBuffer Cache就是用户的数据高速缓冲区。当把信息放入数据库时，它以数据块的方式存储。Data Buffer Cache是ORACLE放置这些数据块以使用户进程访问可见到它的内存区域。用户进程查看的数据必须首先驻留在 Data Buffer Cache中。Data Buffer Cache的容量受物理容量限制。因此如果ORACLE已将它填满，它将在此高速缓冲区中保留最常用的数据块，去除不常用的数据块。注：如果客户进程需要的信息不在此高速缓冲区中，那么 ORACLE将查找物理磁盘驱动器，读取所要的数据块，然后将它放入 Data Buffer Cache中。这样，所有其他客

15、户和服务器进程均可以访问这些从物理磁盘中读出的数据。DataBufferCache中的缓冲有两个列表管理：写列表与LRU列表。前者保存已修改但还没有写到磁盘上的块；后者保存空闲缓存、pinned缓存与还没有移动到写列表上的缓存。2) 重做日志缓冲区(Redo Log Buffer )重做日志缓冲区用于在内存中存储未被刷新写入联机重做日志文件的重做信息。它是循环使用的缓冲区，这意味着从顶端到底端填充信息，然后又返回到缓冲区的起始点。重做日志缓冲区内容刷新到redo log file的条件：1每3秒2Redo log buffer 1/3满或者已有1MB的重做内容3任何事务发出commit指令3)

16、 共享S Q L池(Shared SQL Pool)共享SQL池(SharedSQLPool)相当于程序高速缓冲区，所有的用户程序都存放在共享SQL池中。这个高速缓冲区中存放所有通过SQL语法分析、准备执行的SQL语句。一下几种情况会将一条sql从共享池中清理出去：1.对象的统计信息发生变化2.语句参考的模式对象被修改（发生了DDL）3.手工清除 alter system flush shared_pool1.3.2 程序全局区PGAPGA(Program Global Area, PGA)是单个ORACLE进程使用的内存区域。程序全局区不能共享，它含有单个进程工作时需要的数据和控制信息,如s

17、ql的绑定变量，排序操作与hash连接使用的内存等。 1.4系统后台进程后台进程有很多，这里只列出必须了解的几个。1） 系统监控和进程监控系统监控和进程监控都是自动解决数据库系统问题的后台进程。进程监控（ PMON）自动清除中断或失败的进程，包括清除非正常中断的进程留下的孤儿会话、回滚未提交事务、释放被断开连接的进程占有的锁、释放被失败进程占有的系统全局区（ SGA）资源，它同时监控服务器和调度进程，如果它们失败则自动重启它们。系统监控（SMON）主要同实例恢复有关。SMON也是管理某些数据库段的进程，收回不再使用的临时段空间，并自动合并在数据文件中相邻的自由空间块。SMON和PMON是两个必

18、需的后台进程。如果它们之中的任意一个在数据库启动时失败，数据库将不能启动。2） 数据库写数据库写进程（DBWR）负责将缓冲区中脏的数据块写入到数据文件中。 DBWR不是在每一数据块被修改后立即写入数据文件，而是一直等待，直到满足一定标准后，才成批地读脏列表，并将在脏列表中发现的所有块刷新写入数据文件。这提供了高级别的性能，并最小化数据库输入/输出约束的范围。当下列情况发生时，数据库刷新脏的块：1）发生一个检查点。2）脏列表的长度达到DB_BLOCK_WRITE_BATCH参数值的一半。3）使用的缓冲区数量达到DB_BLOCK_MAX_SCAN参数值。4）DBWR后台进程发生超时（大约每3秒）。

19、3） 日志写日志写（LGWR）是第四个也是最后一个必须的后台进程。LGWR是将在系统全局区中重做日志缓冲区的重做日志条目写入到联机重做日志文件的进程。前面讲过，LGWR执行写入操作的条件是：发生提交commit、到达LGWR非活动时限(3秒)、重做日志缓冲区满度达到三分之一值得注意的重要一点是：直到ORACLE在LGWR完成将重做信息从重做缓冲区刷新到联机重做日志文件之后，ORACLE才认为一个事务已完成。在LGWR成功地将重做日志项写入联机重做文件时（并不是改变数据文件中的数据时），将认为一个事务已经提交。LGWR进程处理的次要任务是，执行实施数据库检查点所需要的操作。除非检查点进程被激活，

20、否则LGWR进程完成这一任务。Tips:检查点进程的介绍1什么是checkpoint?检查点是一个数据库内部事件。该事件被触发以后，数据库写进程DBWR会将数据库缓冲区Database Buffer Cache中所有脏数据块刷新输出到数据文件中。2Checkpoint的有什么作用？1）.保证数据库的一致性。 意思是，将脏数据库刷新到数据文件后，保证内存与硬盘上的数据一致。2）.缩短实例的恢复时间。 实例恢复的过程中，需要把实例异常关闭时没有写出到硬盘上的脏数据通过日志进行恢复。如果脏块很多则实例恢复的时间也相应变长。检查点的发生有助于减少脏块的数量，从而达到提高实例恢复速度的目的。4） 归档进

21、程归档进程（ARCH）负责将全部联机重做日志复制到归档重做日志文件。这仅在数据库运行在归档模式（ARCHIVELOG）下才发生。当ARCH正在复制归档重做日志时，没有其他进程能够写入这个重做日志。这一点非常重要，因为重做日志是按顺序循环使用的。如果数据库需要转换重做日志，但是ARCH还正在按其顺序复制下一个日志，所有数据库的活动将终止，直到ARCH完成。还要注意如果归档由于某些原因不能完成复制日志，它将等待直到引起不能写入的错误得到解决为止。非常值得注意的是在init.ora文件中ARCHIVE_LOG_START参数必须设置为TRUE，当数据库启动时，才会自动开始归档。设置数据库处于归档模式

22、并不足以导致ARCH自动启动。如果设置了归档模式但不自动启动ARCH，当所有联机重做日志写满时，数据库将会挂起，等待你手工归档联机日志。5） 检查点进程检查点进程（CKPT）是可选的后台进程，执行LGWR进程通常会执行的检查点任务即用当前版本信息更新数据文件和控制文件头。当有经常性的检查点发生、频繁的日志切换或在数据库中有多个数据文件时，启用这个进程来减少LGWR的工作量。小结：由于后台进程与相关的内存结构关系非常紧密，因此以以下图示总体说明一下：后台进程与SGA，data files，redo log files等结构的关系图二并发与多版本,事务机制2.1介绍多版本一致读(MVRC)首先看一

23、个例子.(以下代码全部在sqlplus中执行)Session1>Create table tAs Select * from all_users;Session1>Variable x refcursorSession1>BeginOpen :x for select * from t;End;/Session2>Delete from t;Session2>Commit;Session1>Pint x;对于oracle而言，一个查询的结果集在查询开始时就已经确定了。Oracle内部通过系统改变号SCN，对数据块的数据改变的时候会把该改变锁对应的SCN记录在

24、块中。假设查询开始的时候的SCN为T，则在查询所扫描的数据块中，如果数据的Commit SCN小于等于T，则查询接受该数据。如果COMMIT SCN大于T或者还没有产生COMMIT SCN，则查询会尝试去回滚段中查找数据，这样就保证了数据读取在同一时间点的一致性，所以叫一致读(read-consistent)。而实现一致读的途径，是ORACLE依赖回滚段对同一个存在修改的块同时“物化”了多个版本的数据，这就是多版本(Multi-Version)的含义。2.2介绍Redo与undoDML语句会产生重做(redo)信息与撤销(undo)信息。以insert为例，产生的undo包含足够的信息使新插入

25、信息“消失”，产生的重做信息包含足够的信息使插入“再次发生”。Undo信息存储在回滚段中，并且也受到redo的保护（就是说往回滚段中写入的信息，与象表、索引段中写入的信息一样，都会记录重做日志，这个概念很重要，在系统崩溃时可以看到这样做的作用）。回滚只是将数据库还原的一个逻辑操作。2.3 commit与rollback的内部操作在事务需要Commit之前，困难的工作基本上都已经做了，这些工作包括：1).已经在SGA中产生了undo块2).已经在SGA中产生了已修改数据块3).已经在SGA中产生了对应前两项的缓存redo4).如果事务运行时间较长，或者前三项的数量较多，这些数据可能已经部分或全部

26、刷新输出到了磁盘5).得到了事务修改所需要的全部锁。Commit真正发生时，只需要进行如下工作：1).为本次事务产生一个SCN.2).LGWR将剩余的缓存重做日志条目写至磁盘，并将SCN记录到在线重做日志文件。这一步是真正的commit，如果这一步进行完毕就认为已经提交。事务条目将从v$transaction中删除.3).本次事务持有的锁将(记载在V$lock中)被释放。等待这些锁的其他事务将被唤醒。4).如果事务修改的某些块可能还在缓冲区缓存中，则执行快速块清除(block cleanout)。块清除的含义是清除存储在数据块首部的事务信息。这其中，耗时最长的可能是LGWR执行的步骤，因为刷新

27、重做日志是磁盘IO。因此commit时必须等待尚未刷新到磁盘上的重做日志缓冲全部刷新完毕，才认为commit已经完成，也就是说，对LGWR的调用是同步(Synchronous)的。如果不是commit，而是发生了roolback，则步骤如下：1).撤销所有已做的修改。过程如下：从uodo段读回数据，然后逆向执行前面所做的操作，并将undo条目记为已用。2).会话持有的锁全部释放，所有等待这些锁的队列将被唤醒。Tips：本章参考材料1) 测量redo量的脚本 使用两个脚本mystat.sql与mystat2.sql-mystat.sqlset verify offcolumn value new

28、_val Vdefine S="&1"set autotrace offselect ,b.valuefrom v$statname a,v$mystat bwhere a.statistic#=b.statistic# and lower() like '%'|lower('&S')|'%'/-mystat2.sqlset verify offcol name for a20select ,b.value value2 ,to_char(b.value -&V,&#

29、39;999,999,999,999') difffrom v$statname a,v$mystat bwhere a.statistic#=b.statistic# and lower() like '%'|lower('&S')|'%'/2) 如何减少redo量使用nologging提示以减少以下操作产生的redo量： 索引创建与重建。 使用create table as select 语句创建表。Nologging仅仅在数据库运行在archivelog 模式时才有比较明显的效果。如果数据库运行在noarchiv

30、elog模式下，上述操作使用nologging与否对于redo量的产生并没有多大影响。注：可以使用alter table t nologging;与alter index inx_name nologging;子句来指定表与索引的nologging属性，该属性可以通过dba_tables视图查询得到。在archivelog模式下，对表的nologging不起作用，索引的nologging属性起作用。3) ORA-01555 Snapshot too old介绍当Oracle处理一个查询时，它利用一致读机制来进行查询，当Oracle数据读取一个块来回应你的查询时，它可能还需要读取一些UNDO信息

31、。在你的查询的正常处理中，Oracle读取一些UNDO信息。当回应查询所需要的某些UNDO信息因为已经被重写而不再存在时，就会产生ORA-01555错误。UNDO信息只有在生成它的事务仍然活动时才被保存。一旦该事务被标记为已提交，Oracle便可能重写该UNDO，重新利用该空间。如果你正确设置了你的UNDO表空间（回滚段）的大小，Oracle将不会立即重写这一信息，而是暂时保持一段时间。这使需要使用该UNDO信息的查询能够访问它。ORA-01555错误的出现是因为所设定的UNDO空间的大小不能满足系统所做工作的需要而造成的。经常引起ORA-01555的原因之一是在游标FOR LOOP循环中的错

32、误提交，如下面这样：For x in ( select * from emp )loop . process record . update emp set . where empno = x.empno; commit;End loop;通过这一更新，根据EMP表生成了UNDO。同时，通过在更新后正确提交，相当于告诉Oracle“可以随时重新使用undo空间了”。问题是你EMP的查询需要UNDO信息，以获得EMP表的读一致视图。通过在循环中进行提交，释放了开始代码的cursor所需要的资源(通过使用commit声明不再需要保留undo信息)，此类代码就有可能产生ORA-01555错误。三Sq

33、l优化基本方法介绍前言：系统优化原则，以及与单纯sql优化的关系最根本的原则：对sql优化在整个系统优化中有比较清醒的认识。Sql优化不是万能的，务必避免陷入唯sql优化论的误区。对一个现有系统进行优化是一个全局工程，要考虑的因素可能包括很多方面，sql优化仅仅是其中一个方面。按照重要性以及固定工作量换取的性能提升比来看，一般的工作步骤建议如下：1优化业务规则比如核心平台中确认单据不自动记帐的业务模式。2优化数据库设计数据库设计阶段通常要经历规范化阶段，此时需要对数据进行分析以降低数据冗余。除了主键以外，任何数据元素都应当在数据库中只存储一次。当在数据规范化以后，处于性能考虑，有时又需要打破这

34、种规范化形式。还有一些其他的例子，比如aged系统中用到的si.emp_join中的dfjgbh与yhzh。3优化应用程序设计这一步中包括程序逻辑结构的调整，也可能包括具体sql的调整。具体sql调整的基础知识与常用方法是接下来要讲解的主要内容。4优化数据库的逻辑结构如建立必要的辅助索引来提高某些具体程序的性能。例如有个地方的退休审批程序中要求检测新增加的退休人员所录入的银行帐号是否在系统中已经存在。这个时候，为了避免对si.emp_join表进行全表扫描，在yhzh列上建一个索引是值得的。5优化内存分配，优化I/O和物理结构例如，如果性能问题确实出现在SGA太小，或者DBWR,LGWR写出太

35、慢等，就需要用到这一步骤。对这一步感兴趣的，请多参考oracle管理方面的书籍，在此不作为主要内容讨论。一) SQL优化基本理论0.一些基本术语：1)共享sql语句通过共享sql，可以减少相同类型sql的解析次数。2)Rowid的概念：索引节点中存储了rowid，因此通过索引可以快速检索到数据行。3)Recursive SQL概念为执行一个用户发出的sql，oracle在后台需要额外执行的sql.常见的情况有：DDL语句（要修改数据字典，若数据字典没有在data buffer中缓存，就引发recursive calls将字典信息读入磁盘）。4)Row Source(行源)在一个查询中，由子操作

36、提供的数据集，可能是一个表的全部或部分数据，也可能是表连接后得到的结果集。5)Predicate(谓词)一个查询中的WHERE限制条件6)组合索引(concatenated index)由多个列组成的一个索引。涉及到引导列(leading column)的重要概念7)可选择性(selectivity)：一个列所有不同值的总数/行数 的比率，这个值越接近1说明不同值越多，选择性就越高1 SQL语句的执行过程了解SQL语句的处理过程是基本的要求，每种类型的语句都需要如下阶段：1) Create a Cursor 创建游标这一步一般是自动的。2) Parse the Statement 分析语句*)

37、 翻译SQL语句，验证它是合法的语句，即书写正确*) 实现数据字典的查找，以验证是否符合表和列的定义*) 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象 的定义*) 验证为存取所涉及的模式对象所需的权限是否满足*) 决定此语句最佳的执行计划*) 将它装入共享SQL区*) 对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点以上任何一步出现错误，都将导致语句报错，中止执行。这里涉及到使用共享SQL的问题，即使用绑定变量(bind variable)。3) Describe Results of a Query 描述查询的结果集(Select Only)4)

38、 Define Output of a Query 定义查询的输出数据(Select Only)5) Bind Any Variables 绑定变量6) Parallelize the Statement 并行执行语句(并行查询)7) Run the Statement 运行语句8) Fetch Rows of a Query 取查询出来的行(Select Only)9) Close the Cursor 关闭游标上述步骤中，标记有select only的步骤只有查询语句才会用到（这里的查询语句不仅仅包含一般意义上的select语句，也包括带有where条件的update,insert sel

39、ect,create table as select等语句，因为这些语句中都包含对于数据的查询）2 解读SQL的执行计划执行计划的阅读顺序：一般是按照从里到外,从上到下的次序解读分析的结果.最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.有一个例外是nested loop，对nested loop提供数据的操作中，操作号最小的将被最先处理.Tips 1 优化器介绍Oracle提供了两种优化器：基于规则的优化器Rule-based Optimizer，我们将简写为RBO, 基于代价的优化器 Cost-based Optimizer，我们将简写为CBO基于代价的

40、优化器 The cost-based optimizer CBO 相对于RBO更加地灵活并能更好地适应数据的变化。为了得到一条 语句的最佳执行路径，CBO不是单单使用固化的教条式的规则，而是会利用数据库的很多资源信息，如表的大小，表中的行数，键值的分布情况等等。一旦一个表通过ANALYZE 命令或是使用 DBMS_STATS 工具收集了统计信息，这些统计信息就可以用在CBO对最优执行路径的判断中。如果将对没有收集统计信息的表根据数据字典中的数据推测其统计信息。在如下情况下将默认使用 在1.在实例级别或者Session级别设置了OPTIMIZER_MODE = CHOOSE，而且语句中访问到的表

41、中至少有一个已收集了统计信息。 2.在会话级别中调用了alter session set optimizer_mode=FIRST_ROWS (or ALL_ROWS),且语句中访问的表中至少有一个已经收集了统计信息。3.SQL语句中使用了FIRST_ROWS 或是ALL_ROWS 优化提示。使用CBO时，对于没有收集统计信息的表，ORACLE将根据数据字典中的数据推测其统计信息。Tips 2 三种表连接方式介绍1）Sort-Merge Join(SMJ)在merge join操作中，连接的两个输入(以下分别称为row source 1,row source 2)分别处理、分类和连接。如果ro

42、w source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作，特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。2）Nested Loops(NL)在NESTED LOOPS连接中，Oracle读取row source1中的每一行，然后在row source 2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行。这个过程一直继续，直到row source

43、1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。在这里，称Row source1为驱动表(Driving Table)或外部表。Row Source2被称为被探查表(Probe table)或内部表。如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引或高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实

44、现快速的响应时间。3）Hash Join理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当当被构建的has

45、h table与bitmap能被容纳在内存中时，这种连接方式的效率极高。对三种连接方式的适用总结：1。排序 - 合并连接(Sort Merge Join, SMJ)：1) 如果在关联的列上都有索引，效果更好。2) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。3) 但是如果sort merge返回的row source过大，则因为过多的I/O，效果会下降较多。2。嵌套循环(Nested Loops, NL)：1) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种

46、方法可以得到较好的效率。2) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。3。哈希连接(Hash Join, HJ)：1) 理论上来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。2) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率(只得是driving row source小到可以完全放到内存中时，此时HJ的连接效率极高)。Tip

47、s 3 解读SQL的执行计划时的重点关注事项：一:How do i decide which query is better depending on the values of the each parameter in statistics? 1:Important parameters: db blocks gets,consistent gets :logical reads or memory usage. physical reads is disk I/O Depending on this we can estimate the memory usage. 2:secondly

48、,recursive calls,redo size,sorts(memory),sorts(disk) 3:thirdly:bytes sent via SQL&Net from client ,bytes received via SQL&Net from client,SQL&Net roundtrips to/from client 二:What these parameters really mean? Recursive calls The number of recursive calls to the database. This type of cal

49、l occurs for a few reasons misses in the dictionary cache, dynamic storage extension, and when PL/SQL statements are executed. Generally, if the number of recursive calls is more than 4 per process, you should check the dictionary hit cache ratio. Recursive Calls These occur because of cache misses

50、and dynamic storage extension. If the dictionary data is found in cache, a recursive call is not made and the data is read from cache directly. In general, if recursive calls are greater than four per process, the data dictionary cache should be optimized and segments should be rebuilt with storage

51、clauses to have a few large extents. Segments include tables, indexes, and rollback segments.Recursive calls should be fewer than user calls (less than one-tenth). Where there is an imbalance, the aim should be to reduce parsing. High levels of recursive SQL may also be attributable to significant u

52、se of PL/SQL. For each SQL statement in a PL/SQL block, on each iteration, there are recursive calls to do the equivalent of bind and define. DB Block gets The number of blocks in the buffer cache that were accessed for INSERT, UPDATE, DELETE and SELECT for UPDATE statements. Consistent gets The num

53、ber of blocks accessed in the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as a logical read. Physical Reads the number of data blocks that were read from disks to satisfy a

54、SELECT, SELECT FOR UPDATE, INSERT, UPDATE or DELETE. Data Cache Hit Ratio Hit Ratio = (Logical Reads Physical Reads) / Logical Reads Redo Size the size in bytes of the amount of redo information that was written to the redo logs. This information can be used to help size the redo logs and the LOG_SM

55、ALL_ENTRY_MAX_SIZE parameter in the init.ora. Sorts(memory) the number of sorts that were performed in memory Sorts(disk) the number of sorts that were unable to be performed in memory and therefore required the creation of a temp segment in the temporary tablespace. This statistic divided by the so

56、rts(memory) should not be above the 5 percent. If it is, you should increase the SORT_AREA_SIZE parameter in the init.ora. Tips 4 四种索引扫描类型介绍根据索引的类型与where限制条件的不同，有4种类型的索引扫描：索引唯一扫描(index unique scan)索引范围扫描(index range scan)索引全扫描(index full scan)索引快速扫描(index fast full scan)(1) 索引唯一扫描(index unique scan)

57、通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = JACK and deptno = DEV语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = DEV语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。(2) 索引范围扫描(index range scan)使用index rang scan的3种情况：(a) 在唯一索引列上使用了range操作符(> < <> >= <= be