Oracle与sql简单优化与锁机制浅析 精品资料

1、Oracle与sql简单优化与锁机制浅析系统运营二部徐海涛oracle数据库的基本概念与原理对象的存储，segment、extent、block SGA、PGA内存域，内存与存储的关系 事务、undo、redo与ORA-01555关于锁机制2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯对象的存储oracle中的对象以segment的形式存储。我们可以在dba_segment这张视图中查询到所有我们创建的表和索引。segment由extent组成。其扩展是以extent为单位。一张表在初始化时会首先产生至少一个设定大小的extent，以后如果记录数逐渐增多，则需要扩

2、展segment的空间，每次以设定大小扩展一个extent(即增加一个设定大小的extent到segment中)。extent由block组成。block是oracle存储中最基本的单位。一个block上会存储一条或多条数据记录，读取一条数据记录时至少需要读取出这条记录所在block。在block header上记录了一些非常重要的信息，包含块的类型(表还是索引)、关于块上活动和过时的事务信息、块在磁盘上的位置等等。一个segment属于一个唯一的tablespace，而一个tablespace则可以包含一个或多个数据文件。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不

3、得侵犯oracle的内存结构 SGA内存域ORACLE使用的所有共享内存空间被称为SGA(system global area)的内存结构SGA主要包含下面的内存域：data buffer：用于放置data block，ORACLE中所有的数据操作(增、删、查、改)都需要在data buffer中完成，读数据时需先将数据块从存储读到data buffer，修改数据的操作需在data buffer中完成修改然后在回写存储。优化物理读的一个办法就是增大data buffer，使数据在data buffer的停留时间变长，提高buffer的命中率，减少物理读，也就减小了I/O，不过这是不推荐的办法，

4、最重要的还是要优化应用。shared pool：用于放置缓存的sql语句、sql语句的执行计划、数据字典视图等，sql语句执行过程中需要保持在shared pool中的语句本身和其执行计划，dll操作也需要在shared pool中锁住相关的数据字典。java pool：用于存放java对象。large pool：用于分配一些大块的内存给进程应对一些特殊的需要，如语句的并行执行和备份会用到large pool，weblogic connection pool连接ORACLE数据库也是使用large pool存放connection的相关信息。redo log buffer：用于缓存redo l

5、og，redo log会先缓存到redo log buffer然后再写到日志组中。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯oracle的内存结构在oracle中几乎所有操作都是SGA完成的。不论增、删、查、改都是将需要的数据取到SGA中，在SGA中完成相关的操作。oracle通过后台进程(DBWn)将SGA中产生的变化同步到储存中，本身并不直接在存储上进行增、删、查、改的操作。 PGA内存域针对每个oracle进程(process)分配的独占内存空间被称为PGA(process global area) 的内存结构，是在SGA之外独立分配的，一般情况下，se

6、ssion越多也就耗用越多的PGA。总体而言，PGA中需要关注的地方不是太多，在9i以上的版本，使用自动内存管理，用于hash和排序的内存空间从SGA挪到了PGA，为PGA的上限值(pga_aggregate_target)配置一个合理的值对sql语句的效率有较大影响。(oracle中另一部分非常重要的机制就是oracle中的后台进程，这里我们不作讨论，大家可以参看oracle expert one-on-one等相关的书籍) 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯事务、undo、redo 事务事务：单个逻辑工作单元执行的一系列操作。事务遵循如下的特性：原

7、子性：一个事务要么完全发生，要么完全不发生一致性：事务把数据库从一个一致状态转变到另一个状态隔离性：在事务提交以前，其他事务察觉不到事务的影响持久性：一旦事务提交，它是永久的oracle的事务是隐式开始的，从第一条dml语句开始(第一条取得TX锁的语句开始的，后面我们将讨论oracle的锁机制，锁也是保证事务性的重要机制，通过锁保证了不同事务不能同时修改同一资源)，到显式以commit或者rollback结束。oracle缺省的事务隔离级别：read committed：只能读到其他事务已提交的变更，事务中的每一条语句都遵从语句级的读一致性(即只能读到每条语句开始时其他事务已提交的变更，执行过

8、程中其他事务提交的变更不被体现)，保证不会脏读。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯事务、undo、redo 事务需要注意的是完整性约束检查的点是在语句执行结束的时候开始的，也就是说只要有一行的修改违反完整性约束，则整体条语句失败。在oracle中频繁的commit并不是一个良好的习惯：oracle的所有变化都是在SGA中完成的，然后通过后台进程同步到存储中；但这一同步过程并不是只在commit的时候才发生，而是有一定量的数据被修改就会发生；实际上每次commit的消耗都是比较小的，因为大量修改的数据其实已经写到存储中了；过于频繁的commit反而带来冗

9、余的checkpoint(简单来讲，检查内存和存储中的信息是否完全一致，不一致则调用相关的同步操作)的消耗；只需要在应该commit时候(需要被其他事务可见的时候)commit。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯事务、undo、redo redo所谓重做，顾名思义，就是重新做已经做过的动作。redo log(重做日志)对于oracle数据库是至关重要的，数据库中的所有的改变都会记录到redo log(比如dml、ddl操作等)，一旦数据库出现故障，oracle能够根据redo log“重做”，恢复到故障前的情况。 由于重做基本上是不能避免的、也不是浪费

10、，需要注意数据库过于频繁的dml操作会带来大量记录重做日志的消耗。当然这通常只能增加redo log的日志组或者提高archive log的效率来满足应用的需要。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯事务、undo、redo undo撤销：也就是取消之前的操作，回滚到操作前的情况。oracle对于每次数据的修改，都会记录变化前的数据，这个数据会记录在rollback segment(回滚段)中。对应的dml操作会在改变的data block和记录变更前数据的rollback block产生一个相对应的transaction slot，记录事务的相关信息。如

11、果要回滚一个事务所做的dml操作，oracle根据该事务产生的所有transaction slot中的信息，在rollback segment中找到变更前的数据并回写到对应的data block即可。(注意这个过程仍是首先在内存中完成，然后通过后台进程同步到存储上)如果事务没有结束，那么这个事务产生的回滚信息就不能被清理。但是如果事务已经提交或者回滚，那么这个事务产生的回滚信息就能够被清理重用。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯事务、undo、redo ORA-01555由于存在回滚段的循环使用和读一致性的关系，这就使得open过长时间的cursor可

12、能产生 ORA-01555:snapshot too old 的问题。ORA-01555产生的原因是因为不能读取到查询开始时的数据引起。由于读一致性，sql语句读取的数据必须是查询开始时的数据，在查询过程中产生的变更不能被这个查询所读取。对于cursor而言，就是open cursor的时候为查询开始的时候，close是查询结束。如果在查询执行或者open cursor fetch的过程中，原来查询的数据有被更改，则这个查询必须到回滚段中取相关修改前的数据。但因为回滚段是循环使用的，假设这个查询执行的时间过长或者open cursor的时间过长，就可能导致查询过程中被修改的数据的回滚信息已经被

13、重用(因为更改这些数据的事务已经提交了，显然也不会被查询阻塞)，不能找到需要的修改前的数据，从而发生ORA-01555。更详细可以参见文档关于ORA-01555的成因和应对措施.doc或者其他相关的资料。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制锁(lock)：oracle中用于保护资源的共享机制，对于任何资源、对象的访问都需要对其进行加锁，用以保护对资源的并发访问时用户在存取同一数据库对象时的正确性(即无丢失修改、可重复读、不读“脏”数据)；锁也是保证oracle事务特性的重要机制，通过锁机制保证了不同的事务不能同时发起对同一资源的并发修改。在or

14、acle中，锁简单来讲有两个维度：一个是锁的类别(lock_type)，这个维度表示了是在哪种资源、对象上的锁，比如JQ表示在job对象上的锁、TM表示对象锁(表锁)、TX表示事务锁(行锁)、TS表示表空间(tablespace)的锁等等。另一个是锁的模式(mode)，包含0-6。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制锁的模式(mode)：0：None1：null2：row share，即RS、行级共享锁3：row exclusive，即RX、行级排它锁4：share，即S、共享锁5：share row exclusive，即SRX、共享行级排它

15、锁6：exclusive，即X、排它锁2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制不同的锁模式(lock mode)的相容列表见下：2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制oracle中的不同操作需要对不同的对象加不同模式的锁；通过锁的类别来表示对某种对象加锁；而通过不同的锁的模式的相容规则，来控制哪些操作可以并行，哪些操作是互斥的；通过这样的锁机制来保证每个用户访问对象的正确性。一个操作可能需要对多种对象加锁(需要申请一种以上type的锁)，同时根据操作的不同申请不同的锁模式(lock mode)。比如：

16、select for update操作需要对表申请mode=3(即RX)的TM锁(lock type=TM)，然后对选到的行申请mode=6(即X)的TX锁(lock type=TX)(网上很多文档说是加mode=2的TM锁，是在8i库上，在9i或者10g的库实测加的是mode=3的TM锁，如果有分区则对对应分区增加的是mode=2的TM锁)；执行DML操作也是一样，需要对表增加mode=3的TM锁，对作dml操作的行增加mode=6的TX锁。那么根据锁相容的模式，mode=3的锁是相容的(即RX与RX是相容的)，但mode=6的锁是不相容的(即X与X是不相容的)；因此同时在一张表上执行dml

17、操作和select for update操作是不阻塞的(同时对一张表增加mode=3的TM锁是相容的)；但如果涉及到相同的行则会阻塞一方，直到另一方事务完成(同时对一行增加mode=6的TX锁是不相容的)。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制通过这个过程，我们可以简单理解oracle的锁机制是如何控制不同操作的相容和互斥。实际上，oracle的每种操作都有不同的锁策略(需要申请什么类型的锁、什么模式的锁)，这些复杂的锁策略随着不同的数据库版本也有所变化；通过这些复杂的机制，来保证用户访问对象的正确性和一致性。 oracle的dml锁所有锁机制中

18、，最为常见也最为常用的就是进行各种增、删、查、改操作中的dml锁机制。dml锁，顾名思义，就是在各种dml操作中产生的锁，这里主要是出现TX、TM两种类型锁。在dml锁机制中，TX锁会出现在实际发生改变的部分用于保证dml操作的正确性。也就是我们通常讲的事务锁(实际上这个事务所真正改变的部分)或者行锁，用于锁定发生改变的行，从而保证修改的正确性(不同时被其他session修改)；就像我们之前看到的是用了mode=6的锁从而阻塞了其他的修改操作。TM锁在这里则是一种意向锁，也就是说需要修改某一个对象时，对其上层对象增加一个锁，表明修改其下级对象意愿，可以理解为一种操作的入队；就像我们之前看到的，

19、会增加mode=3的TM锁锁定做dml操作的表；这个锁不会阻塞其他session对这张表同时进行的增删查改操作，但会阻塞对这张表的ddl操作(大部分，会使用独占的ddl锁定，比如add column等等)，保证对象的正确性。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制 v$lock视图v$lock视图记录了每个session取得锁或者等待锁的情况：ID1和ID2标识了锁定的对象，在TM和TX锁中的含义如下：2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制通过v$lock视图我们就能查到session之间持有和等待锁

20、以及相互阻塞的情况。更详细的有关dml锁机制的说明可以参看转引网文oracle多粒度封锁机制研究（论坛）.doc或其他相关资料。本文大量内容引自该文档和oracle expert one-on-one相关内容。 关于死锁需要注意的是，就一般而言oracle中并不会长期存在真正意义上的死锁。oracle会以一个很短的时间去轮循，检查是否有死锁，如果发现有死锁出现，则会中断掉其中一个session以解除死锁，并抛出ORA-00060错误。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于锁机制 一个关于外键关联在dml操作中锁机制的案例oracle的dml锁中，比较复

21、杂的情况之一就是涉及到外键关联的情况，由于存在完整性约束检查，这里不仅仅会对发生dml的表本身产生锁，也会对有外键关联的表产生锁。案例：lock sample1.doc2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯简单的sql优化sql语句的执行过程关于索引与表扫描关于表连接关于排序2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯sql语句的执行过程sql语句的执行步骤hard parse与soft parsesoft parse也会有消耗2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯sql语句的执行步骤1

22、、语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。2、语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。3、视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。4、表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。5、选择优化器，不同的优化器一般产生不同的执行计划6、选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。7、选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。8、选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是

23、利用索引或是其他的方式。9、运行执行计划。 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯hard parse与soft parse1-8的步骤也就是我们通常所说的parse，通过parse得到一条语句的执行计划，可以看出parse的过程是一个比较昂贵的消费，显然如果每次执行sql都需要进行一次完整的parse，那么将是非常大的消耗。因此，大部分数据库都提供了sql的共享的机制。一条sql语句如果做一次完整的parse并生成全新的执行计划，这个过程被称为hard parse；如果已经parse过并仍然存在于缓存中的sql语句，再次执行时则直接使用已经在缓存中的执行

24、计划，不需要再重新生成执行计划，这个过程称为soft parse。正是因为这样，我们大量使用绑定变量，使得只是参数不同的同构sql语句在oracle为同一条sql语句(只是具体执行时使用的参数不一样)，由此使得sql语句的执行计划可以得到复用，减少hard parse，尽量用到soft parse，从而减少parse 带来的消耗。 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯soft parse也会有消耗尽管如此，soft parse也并非全无消耗，soft parse同样需要在shared pool中取得相关内存空间的latch(锁住存储sql语句、执行计划以

25、及需要锁住的相关数据字典的内存空间)；而对latch的分配和操作本身就是一个比较耗cpu的动作，latch的数量也是有限的，因此过量的并发执行，即使都是soft parse依然会造成很大的消耗。 案例：实际上如果能够在pga空间中的cursor cache找到同样的语句，则不需要再到shared pool中查找，这个过程是消耗最小的。默认情况下，oracle并不会去为session缓存cursor，需要我们去设置session_cashed_cursor来指定oracle为session缓存的cursor数量(当然这会消耗pga内存空间)。 2022/9/25中国平安保险（集团）股份有限公司

26、秘密 版权所有，不得侵犯sql语句的执行过程接下来，运行“执行计划”，就是通常sql性能最重要的部分；选择了怎样的执行计划、如何做表连接、如何进行表的扫描、是否使用索引、使用什么索引，等等问题。应该选择什么样的执行计划，一个比较基本的看法，首先应关注那些直接的查询条件(也就是表的列直接和带入参数进行比较的查询条件)，这些查询条件中哪些能够首先筛选掉较多的记录从而有效的降低结果集，那么应当优先执行这些查询条件，降低整个sql执行过程中需要处理的结果集。当然实际上sql的执行计划必须全盘考虑整个查询过程怎样才是较优的查询路径，包括每个环节步骤选择什么索引、什么扫描方式、什么表连接方式。下面我们依次

27、看看这些问题。 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于索引与表扫描B Tree索引的数据结构判断是否适合使用索引索引使用不合理的常见问题2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯B Tree索引的数据结构索引，正如其名称一样，就好像字典中的索引，通过它数据库能够根据一些特定的信息很快的定位到所需要的数据而并不需要察看全部的数据才能得到想要的结果。B Tree索引的数据结构是一个根据关键字排序的B+树结构(一个多层的N叉树)，由一群(关键字、值)对组成；关键字就是索引列的列值(如果是复合索引，则是多个列值)，值就是对应记

28、录的rowid。其中，根节点存储1-N个关键字和2-N+1个指针，其指针指向内层节点或者叶结点(如果索引足够小)；内层节点存储(N+1)/2-1-N个关键字和 (N+1)/2-N+1个指针，其指针指向叶节点或其他内层节点；叶节点存储(N+1)/2-N个关键字和 (N+1)/2-N+1个指针，其最后一个指针指向下一个叶节点；其余的指针指向对应的行记录(也就是上面说的rowid)，关键字保存对应记录索引列的列值。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯B Tree索引的数据结构根节点和内层节点的关键字表示一个范围，其指针分别指向了小于该关键字或者大于等于该关键字

29、的节点群，如下图： 叶节点的关键字为对应的记录索引列的列值，除最后一个指针指向下一个叶结点外其余指针则指向了对应的记录(rowid)，如下图： 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯B Tree索引的数据结构如上假设我们要查找索引列值为75的记录，只需要在根节点中找到57到81这个范围的节点群，然后依次根据范围最终在叶节点中找到索引列为75的记录的rowid。 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯判断是否适合使用索引索引之所以能够起到优化查询的作用，就在于它将查询用到的条件(列)作为关键字(其对应值指向对应的记录)并

30、组织为一个排序的结构，这样我们能在这个排序结构中快速的定位到要查找的记录而不需要去遍历全部的数据(就好像查字典一样，根据拼音或者笔画就能很快的查到一个字，而不需要把整个字典翻一遍)。 相对通过全表扫描找到一条记录，通过索引避免了很多冗余数据的扫描(我们不需要把整个字典中不是我们要查找的字的页也翻看一遍)。但同时我们也看到，对于单独的一条记录而言通过索引扫描在读取这条记录的花费上增加了扫描索引和通过rowid定位的操作。 因此不是所有情况下，都适合使用的索引。假设一个字典记录了1000个字，而我们需要查找其中的900个字，这种情况下如果还先查索引在找到对应的字就不如直接把整个字典翻看一遍来的要快

31、。 同样的道理，并不是所有的字段都适合建立B Tree索引，如果一个字段的独立列值非常少，比如100万的记录却只有10个独立列值，那么任意查询其中一个列值都会查询出10万条记录(10%)，那么这个索引就算使用效率也很低，这个字段不适合建立单列的B Tree索引。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯判断是否适合使用索引而实际上的经验数据，当通过索引扫描access的记录数=:1 and tab.a=:2，这种情况主要看 :1 - :2 之间这个范围的记录数占到总记录数的比例；范围太大(比例比较高)的话则不适合使用a列的索引tab.a in (list)，这

32、种情况主要看in list中的列值包含的记录数占到总记录数的百分比，这个百分比较大的话就不适合使用a的索引。假设一个列有10个独立列值，而in list中就有5个列值，那么平均计算可能就是50%，显然这里并不适合使用a的索引。tab.a=table.b，通过表table作为驱动表与表tab做表连接，连接条件是table表的b列=tab表的a列，这里主要看表table用于表连接的结果集其每条记录的b列值对应在tab表的a列能够选取到的记录数的总和占tab表记录数的百分比(这里用tab表a列的索引指的是使用nested loop表连接方式的情况下，使用hash join或其他的表连接方式，这个比例

33、的计算并不适用，关于表连接的方式，我们在后面讨论)，如果表table用于作为驱动表的结果集比较小、且结果集中b列的列值对应tab表中a列的列值能够选取的到的记录数比较低，则适合使用tab表上a列的索引。(这里指使用nested loop的情况，涉及到表连接索引的使用要跟表连接的方式一起考虑，在表连接的部分我们再做讨论) 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 缺少合适的索引可用(选择更加优化的字段或者合理的复合索引首列) 案例1：index sample1.doc在这个案例中：语句(1)存在一个日期范围查询可以使用在日期字段上的索引

34、，但是如果时间范围跨度过大，这个索引的效率也就不高了；语句(2)能够有查询条件的字段当中只有一个区分度很低的字段建了索引(千万条数据只有几十个独立列值)，这个字段是不适合建立单列索引的，查询使用这个索引的效率也非常低；这两个语句我们通过分析语句，都发现了有区分度比较高且适用的查询条件字段，只要在这些字段建立索引，就能优化语句的执行效率。这里我们看到，过大的范围查询会影响索引的效率；而过低的区分度的列则并不适合建立单列索引。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 缺少合适的索引可用(选择更加优化的字段或者合理的复合索引首列) 案例2：

35、index sample2.doc在这个案例中：表cjk上原来有一个复合索引(FZJZH, FBMDM, FSCDM, FGDDM, FZQDM, FHTXH, FMMLB,FCJSJ)，这个索引的区分度很高，本来是很好用的。但问题就出来这两个查询语句中，前面几列使用的都是模糊查询，而根据实际情况，往往传入的都是百分号，导致索引扫描的时候无法根据关键字的范围快速的定位到需要的索引结点，在这里反而使用这个索引效率比全表扫描还要低得多(实际情况是几个小时)。分析这条两条语句的查询条件，发现FCJSJ这个查询条件，实际操作中基本上都是查询一天的数据，这里只需要建立一个以FCJSJ作为首列的复合索引(

36、fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)，就可以优化语句的效率。由于B Tree索引是关键字排序，如果复合索引的首列不能根据查询条件有效的筛选，就需要扫描大量冗余的索引结点；在这个案例中由于前面几列都出现了%号的情况，导致几乎是将整个索引结点扫描了一遍才得到结果，效率非常低。所以复合索引要特别注意首列的选择。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 不均匀分布的列值在bind peeking和histogram的影响下，影响索引的使用首先解释下相关名词：bind peeking：sql语句中使用到绑定变

37、量，在第一次执行时会peeking其绑定变量的值，就相当于常量语句一样，并根据这个具体值解析计算成本，解释出执行计划。这一特性是在oracle9i以后引入的。histogram：直方图，对于不同列值更加准确的数据量的统计。对于列值分布不均匀的列来说，通过直方图，就能准确计算出不同列值的数据量，而不仅仅简单的根据 (总记录数/独立列值数)来计算其数据量(平均情况)。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 不均匀分布的列值在bind peeking和histogram的影响下，影响索引的使用以下引用ORACLE数据库优化案例简报(第一期

38、)的相关内容：由于8i 还没有bind peeking 技术，使用绑定变量以后无法使用histogram，所以最好在编程时对具有skew 值的列不使用bind 变量,这样, 生成计划时, 其可根据histogram 的值来估算返回的数据量, 并生成合适的计划。9i引入了bind peeking技术，使用绑定变量以后可以用到histogram，但是如果第一次执行带入的变量值失误，很可能产生的执行计划对以后的多次查询不适合而带来性能问题。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 不均匀分布的列值在bind peeking和histogra

39、m的影响下，影响索引的使用我们看看ORACLE数据库优化案例简报(第一期)所举的这个案例：语句：select policy_cert_no, apply_personnel_numfrom acc_policy_certwhere policy_no = :1and cert_type = 1select policy_cert_nofrom acc_policy_certwhere insurance_card_no = :1and policy_no = :22022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 不均匀分布的列值在bind p

40、eeking和histogram的影响下，影响索引的使用这两条语句都是对表acc_policy_cert进行查询，在policy_no字段上有主键索引(复合索引的首列)，正常的情况下，应该走这个索引而不是全表扫描。但在policy_no的列值分布并不均匀，比如policy_no列共有100个不同的值，其中为70的占了99，为其他值的数据行仅占1（即选择性很高），则如果不使用绑定变量，借助histogram，oracle能够知道，查询policy_no=70的时候应该走全表扫描效率更高，查询policy_no为其他值的语句应该走索引效率更高。问题就出现了，假设第一次执行时带入的是70的这个值，显

41、然执行计划会走全表扫描，但是由于使用了绑定变量，以后即使是带入其他值，执行计划依然会走全表扫描。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯索引使用不合理的常见问题 不均匀分布的列值在bind peeking和histogram的影响下，影响索引的使用解决办法有3个：1)不使用绑定变量，但是这样就会导致大量的hard parse，对shared pool也会产生大量的消耗。2)修改程序，针对不同情况使用不同执行计划(不同的语句)3)不收集直方图(这样就会按照平均情况来计算数据量)或者使用hint绑定执行计划，使其总能使用到索引，这样就可以使大部分情况得到较好的效

42、率，但对于比如70这样的值就会效率低下。关于这个问题详细的解释和说明可以参见ORACLE数据库优化案例简报(第一期)中相关内容。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于表连接 三种主要的表连接方式 何时使用哪种表连接方式2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯三种主要的表连接方式 nested loop join循环嵌套连接：行源1的每一条记录，依次去匹配行源2的每条记录，将符合连接条件的记录放在结果集中，直到行源1的所有记录都完成这个操作。循环嵌套连接是最基本也是最古老的表连接方式。 sort merge join排

43、序合并连接：行源1和行源2的数据分别排序，然后将两个排序的源表合并，符合连接条件的记录放到结果集中。由于排序需要内存空间，sort merge join对内存有比较大的消耗，如果内存空间(8i为sort_area_size，9i及以上使用PGA)不足，则会使用临时表空间，这样会降低排序合并连接的效率。排序合并连接是最古老的表连接方式之一。 hash join哈希连接：将行源1计算成一张基于连接键的hash表，行源2的每条记录依次扫描这张hash表，找到匹配的记录放到结果集。计算hash表需要内存空间，hash join同样对于内存有比较大的消耗，如果内存空间(8i为hash_area_size

44、，9i及以上使用PGA)不足，则会使用临时表空间，这样会降低哈希连接的效率。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯三种主要的表连接方式 nested loop join2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯三种主要的表连接方式 sort merge join2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯三种主要的表连接方式 hash join2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯何时使用哪种表连接方式 nested loop join表连接方式的适用情

45、况nested loop join适合于：作为表连接的驱动表(也就是之前的行源1，也称为外部表)记录数比较少或者通过直接的查询条件能筛选出比较少的记录数，被连接表(也就是之前的行源2，也称为内部表)在连接条件上有区分度很高的索引；驱动表上的每条记录通过被连接表在连接条件上的索引能快速的匹配到少量的记录；整体的结果集比较小，这样就比较适合使用nested loop join。nested loop join选择驱动表时应优先选择记录数比较少的、通过直接查询条件能够筛选出比较少记录的表作为驱动表，这样能够有效的减少匹配次数。例如这样的查询语句：select * from a,b where a.c

46、ol1=:1 and a.col2=b.col2；这里a表有100条记录，通过col1=:1的条件能够筛选出50条记录，b表只有10条记录；这里如果以a表作驱动表的话，则匹配次数是50*10；如果用b表作为驱动表的话，则匹配次数是10*100；显然应该使用a表作为驱动表。如果连接条件没有很好的索引、或者作为表连接的两张表结果集都相当大，则并不适合使用nested loop join。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯何时使用哪种表连接方式 sort merge join表连接方式的适用情况sort merge join适用于：当表连接的两张表的结果集都

47、比较大，或没有很好的条件可以筛选，连接条件缺少很好的索引时，可以选择使用sort merge join。由于sort merge join需要对作连接的两张表都作排序，实际上如果语句中没有排序需求，oracle更加倾向于选择hash join。但如果语句中本身就有排序的需求，sort merge join则有可能省去单独的排序。sort merge join对内存消耗比较大，如果内存空间不足以完成排序，则需要用到临时表空间，效率会有较大的降低。sort merge join只能用于等价连接。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯何时使用哪种表连接方式 ha

48、sh join表连接方式的适用情况hash join适用于：当表连接的两张表的结果集都比较大，或没有很好的条件可以筛选，连接条件缺少很好的索引时，使用hash join能够取得比较好的效率。hash join虽然也需要将一张表的所有记录依次和hash表中的记录进行匹配，但扫描hash表的速度要比扫描B Tree索引快的多，所以在大结果集和缺少良好索引的情况下，使用hash join能得到比较好的效率。与sort merge join相比，hash join只需要进行一次排序，因此大部分情况下，oracle会更倾向于选择hash join。hash join需要计算一张hash table，与s

49、ort merge join一样，需要消耗大量的内存空间，如果内存空间不足则需要用到临时表空间，效率会有较大的降低。hash join只能CBO优化器下使用，只能用于等价连接。例如语句：select * from a,b where a.col1=:1 and b.col1=:2 and a.col2=b.col2在这个语句中，a表通过col1=:1和b表通过col1=:2筛选后的记录数依然比较大，虽然col2在a表和b表上都是很不错的索引，但是因为作连接的结果集比较大，使用nested loop效率不高，这时使用hash join就能得到较好的效率。2022/9/25中国平安保险（集团）股份

50、有限公司 秘密 版权所有，不得侵犯何时使用哪种表连接方式案例1：join sample1.doc在这个案例中，原来的执行计划，选择了首先扫描两张小表SELECT_CONDITION_TMP，然后与大表ASSET_COMBINATION进行nested loop join，两张小表结合起来的条件大概会形成一个几百条的结果集，而与表ASSET_COMBINATION进行nested loop join也是一个具有一定区分度的索引，看起来似乎选择nested loop join并没有错。但仔细分析：首先这里在nested loop join时使用的索引是字段the_date上的索引，也就是说连接条件

51、curno、cmbno并没有起到快速查找定位的作用；其次由于两张小表SELECT_CONDITION_TMP只是一些查询条件的组合本身数据量非常少，但依然会使得通过the_date=:1这个条件扫描ASSET_COMBINATION表会重复很多次；再次，ASSET_COMBINATION这个表上并没有以curno或cmbno为首列的索引，而以这两个列作为条件区分度并不是很高。分析之下，作为直接条件the_date=:1能够筛过滤掉大量的记录，且有不错的索引，有效的减小结果集，因此这里应该首先扫描ASSET_COMBINATION减少冗余的扫描(不必像原执行计划一样重复扫描很多次)；然后与两张小

52、表SELECT_CONDITION_TMP进行连接时，因为这两张表的数据比ASSET_COMBINATION通过the_date=:1选出来的结果集还要小很多，这里适合使用hash join有比较好的效率。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯何时使用哪种表连接方式案例2：join sample2.msg在这个案例，原语句因为唯一能够首先过滤掉较多结果集的条件TASK_STATUS_ID = 12本身仍然会筛选出数量较大的结果集，而PA_TASK_TLR_ADMIN本身是个小表，因此优先使用直接条件筛选掉一些结果集，然后再与小表使用hash join；看起

53、来这似乎并没有什么问题。但仔细分析：表PA_CUSTOMER_CAMPAIGN是个大表，而TASK_STATUS_ID又是一个区分度很低条件，首先使用条件TASK_STATUS_ID = 12查询表PA_CUSTOMER_CAMPAIGN就是一个效率不高的动作。发现在表PA_CUSTOMER_CAMPAIGN上字段MGR_QUEUE_ID具有相对好一些的区分度(当然就这个表数据量来讲，不算太好)，而表PA_TASK_TLR_ADMIN又是一个小表，虽然没有首先使用TASK_STATUS_ID = 12过滤掉较多的结果集，但是这里使用小表PA_TASK_TLR_ADMIN通过在MGR_QUEUE

54、_ID上的索引nested loop join表PA_CUSTOMER_CAMPAIGN，因为这个索引的效率要好得多了，反而能够取得比较好的效率。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于排序排序是数据库中一个比较常见的操作，使用order by是一个很司空见惯的东西；但排序的发生并不只是在order by的时候，排序产生的消耗有时也会产生很大的影响，对于排序也有一些需要注意的地方。一个关于排序的sql：投资数据库有一个很简单的sql，是一个统计报表类的sql，只是将一张数据表中某一段时间的数据做一个按照一些统计条件group by的操作；通常会计算一年的

55、数据，虽然数据量比较大，但因为是个统计操作，执行次数很低，在生产环境大概每次执行需要10几秒。但在测试环境的一次测试中，这条语句发现执行得很慢，几分钟才能跑出结果。对此跟踪之下，发现这条语句执行时有大量的direct path read/write等待(后面我们会讨论)，也就是说排序使用临时表空间产生了很大消耗。经检查，这张表有百万级的数据量，占了几百M的空间。根据查询条件约会查询出1/3的记录数来作统计，算下来大约是100M的数据。发现测试环境的pga_aggregate_target设置的是200M(如果没有记错的话，总之是一个比较小的值)左右；由于PGA的可用空间比较小，排序不能完全在内

56、存中完成，因此大量使用了临时表空间，结果排序效率是成倍下降。2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯关于排序可以看到，排序是很消耗内存的操作，而且根据排序的算法，1M的数据排序所需要的空间显然会大于1M；因此要注意控制合理的排序，让排序尽量在内存中就能够完成，过大的排序会带来巨大的消耗。为排序设置合理内存空间：8i是sort_area_size，9i以上则是pga_aggregate_target。避免不必要的排序：除order by以外，group by、distinct、unique、union都会产生排序的操作；如果没有必要的话，不使用distinct

57、、unique，使用union all代替union。减小排序的结果集：尽量只让必要的结果集进行排序而不是排序后再选出需要的结果集；需要根据需求和效率在下面的两种情况作出合理的选择：1 select * from (select * from a order by c1) where rownum =: 12 select * from (select * from a where rownum = :1) order by c12022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯简单的等待事件 什么是等待事件 优化等待事件与sql优化的关系 等待事件相关的视图(v$s

58、ession_wait) 典型的等待事件及其分析2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯什么是等待事件等待事件：oracle wait event 或者 oracle wait interface，ORACLE中对于任何操作、任务、过程都可以使用一个等待事件来描述，描述了当前session对资源的等待和所执行的操作，通过等待事件能够获知当前session在等待某项资源和性能状况。也可以说等待事件是oracle提供的一个用以体现当前运行和性能状况一个接口。等待事件分为空闲等待事件和非空闲等待事件，通常不需要关注空闲等待事件。常见的空闲等待事件：SQL*Net

59、message to client、SQL*Net message from client、SQL*Net break/reset to client、Streams AQ: waiting for messages in the queue、Streams AQ: waiting for time management or cleanup tasks、jobq slave wait；通常不需要关注；但空闲等待事件中有一部分是涉及到dblink的，如：SQL*Net message from dblink、SQL*Net break/reset to dblink，发生这样的等待事件可能表示

60、远端数据库存在效率问题。关注等待事件能够从当前的运行、消耗、性能状况来查知数据库的一些性能问题，分析出现问题的原因，而不仅仅是去分析执行时间较长的sql(执行时间长的sql可能代表着效率问题，但不一定是造成当前性能问题的原因)。 2022/9/25中国平安保险（集团）股份有限公司 秘密 版权所有，不得侵犯优化等待事件与优化sql的关系可以说，当前oracle数据库优化方法已经从优化sql转向优化优化等待事件。这不是说优化等待事件与优化sql冲突，而是因为等待事件能够更直观的反应数据库在效率方面的问题，从而更加准确的发现问题所在，加以针对优化；这里不仅是有sql效率所引起的问题，也包括各种资源、