Oracle与sql简单优化与锁机制

Oracle与sql简单优化与锁机制浅析系统运营二部徐海涛oracle数据库的基本概念与原理对象的存储，segment、extent、blockSGA、PGA内存域，内存与存储的关系事务、undo、redo与ORA-01555关于锁机制2023/1/18对象的存储oracle中的对象以segment的形式存储。我们可以在dba_segment这张视图中查询到所有我们创建的表和索引。segment由extent组成。其扩展是以extent为单位。一张表在初始化时会首先产生至少一个设定大小的extent，以后如果记录数逐渐增多，则需要扩展segment的空间，每次以设定大小扩展一个extent(即增加一个设定大小的extent到segment中)。extent由block组成。block是oracle存储中最基本的单位。一个block上会存储一条或多条数据记录，读取一条数据记录时至少需要读取出这条记录所在block。在blockheader上记录了一些非常重要的信息，包含块的类型(表还是索引)、关于块上活动和过时的事务信息、块在磁盘上的位置等等。一个segment属于一个唯一的tablespace，而一个tablespace则可以包含一个或多个数据文件。2023/1/18oracle的内存结构SGA内存域ORACLE使用的所有共享内存空间被称为SGA(systemglobalarea)的内存结构SGA主要包含下面的内存域：databuffer：用于放置datablock，ORACLE中所有的数据操作(增、删、查、改)都需要在databuffer中完成，读数据时需先将数据块从存储读到databuffer，修改数据的操作需在databuffer中完成修改然后在回写存储。优化物理读的一个办法就是增大databuffer，使数据在databuffer的停留时间变长，提高buffer的命中率，减少物理读，也就减小了I/O，不过这是不推荐的办法，最重要的还是要优化应用。sharedpool：用于放置缓存的sql语句、sql语句的执行计划、数据字典视图等，sql语句执行过程中需要保持在sharedpool中的语句本身和其执行计划，dll操作也需要在sharedpool中锁住相关的数据字典。javapool：用于存放java对象。largepool：用于分配一些大块的内存给进程应对一些特殊的需要，如语句的并行执行和备份会用到largepool，weblogicconnectionpool连接ORACLE数据库也是使用largepool存放connection的相关信息。redologbuffer：用于缓存redolog，redolog会先缓存到redologbuffer然后再写到日志组中。2023/1/18oracle的内存结构在oracle中几乎所有操作都是SGA完成的。不论增、删、查、改都是将需要的数据取到SGA中，在SGA中完成相关的操作。oracle通过后台进程(DBWn)将SGA中产生的变化同步到储存中，本身并不直接在存储上进行增、删、查、改的操作。PGA内存域针对每个oracle进程(process)分配的独占内存空间被称为PGA(processglobalarea)的内存结构，是在SGA之外独立分配的，一般情况下，session越多也就耗用越多的PGA。总体而言，PGA中需要关注的地方不是太多，在9i以上的版本，使用自动内存管理，用于hash和排序的内存空间从SGA挪到了PGA，为PGA的上限值(pga_aggregate_target)配置一个合理的值对sql语句的效率有较大影响。(oracle中另一部分非常重要的机制就是oracle中的后台进程，这里我们不作讨论，大家可以参看《oracleexpertone-on-one》等相关的书籍)2023/1/18事务、undo、redo事务事务：单个逻辑工作单元执行的一系列操作。事务遵循如下的特性：原子性：一个事务要么完全发生，要么完全不发生一致性：事务把数据库从一个一致状态转变到另一个状态隔离性：在事务提交以前，其他事务察觉不到事务的影响持久性：一旦事务提交，它是永久的oracle的事务是隐式开始的，从第一条dml语句开始(第一条取得TX锁的语句开始的，后面我们将讨论oracle的锁机制，锁也是保证事务性的重要机制，通过锁保证了不同事务不能同时修改同一资源)，到显式以commit或者rollback结束。oracle缺省的事务隔离级别：readcommitted：只能读到其他事务已提交的变更，事务中的每一条语句都遵从语句级的读一致性(即只能读到每条语句开始时其他事务已提交的变更，执行过程中其他事务提交的变更不被体现)，保证不会脏读。2023/1/18事务、undo、redo事务需要注意的是完整性约束检查的点是在语句执行结束的时候开始的，也就是说只要有一行的修改违反完整性约束，则整体条语句失败。在oracle中频繁的commit并不是一个良好的习惯：oracle的所有变化都是在SGA中完成的，然后通过后台进程同步到存储中；但这一同步过程并不是只在commit的时候才发生，而是有一定量的数据被修改就会发生；实际上每次commit的消耗都是比较小的，因为大量修改的数据其实已经写到存储中了；过于频繁的commit反而带来冗余的checkpoint(简单来讲，检查内存和存储中的信息是否完全一致，不一致则调用相关的同步操作)的消耗；只需要在应该commit时候(需要被其他事务可见的时候)commit。2023/1/18事务、undo、redoredo所谓重做，顾名思义，就是重新做已经做过的动作。redolog(重做日志)对于oracle数据库是至关重要的，数据库中的所有的改变都会记录到redolog(比如dml、ddl操作等)，一旦数据库出现故障，oracle能够根据redolog“重做”，恢复到故障前的情况。

由于重做基本上是不能避免的、也不是浪费，需要注意数据库过于频繁的dml操作会带来大量记录重做日志的消耗。当然这通常只能增加redolog的日志组或者提高archivelog的效率来满足应用的需要。2023/1/18事务、undo、redoundo撤销：也就是取消之前的操作，回滚到操作前的情况。oracle对于每次数据的修改，都会记录变化前的数据，这个数据会记录在rollbacksegment(回滚段)中。对应的dml操作会在改变的datablock和记录变更前数据的rollbackblock产生一个相对应的transactionslot，记录事务的相关信息。如果要回滚一个事务所做的dml操作，oracle根据该事务产生的所有transactionslot中的信息，在rollbacksegment中找到变更前的数据并回写到对应的datablock即可。(注意这个过程仍是首先在内存中完成，然后通过后台进程同步到存储上)如果事务没有结束，那么这个事务产生的回滚信息就不能被清理。但是如果事务已经提交或者回滚，那么这个事务产生的回滚信息就能够被清理重用。2023/1/18事务、undo、redoORA-01555由于存在回滚段的循环使用和读一致性的关系，这就使得open过长时间的cursor可能产生ORA-01555:snapshottooold的问题。ORA-01555产生的原因是因为不能读取到查询开始时的数据引起。由于读一致性，sql语句读取的数据必须是查询开始时的数据，在查询过程中产生的变更不能被这个查询所读取。对于cursor而言，就是opencursor的时候为查询开始的时候，close是查询结束。如果在查询执行或者opencursorfetch的过程中，原来查询的数据有被更改，则这个查询必须到回滚段中取相关修改前的数据。但因为回滚段是循环使用的，假设这个查询执行的时间过长或者opencursor的时间过长，就可能导致查询过程中被修改的数据的回滚信息已经被重用(因为更改这些数据的事务已经提交了，显然也不会被查询阻塞)，不能找到需要的修改前的数据，从而发生ORA-01555。更详细可以参见文档《关于ORA-01555的成因和应对措施.doc》或者其他相关的资料。2023/1/18关于于锁锁机机制制锁(lock)：：oracle中用用于于保保护护资资源源的的共共享享机机制制，，对对于于任任何何资资源源、、对对象象的的访访问问都都需需要要对对其其进进行行加加锁锁，，用用以以保保护护对对资资源源的的并并发发访访问问时时用用户户在在存存取取同同一一数数据据库库对对象象时时的的正正确确性性(即即无无丢丢失失修修改改、、可可重重复复读读、、不不读读““脏脏””数数据据)；；锁锁也也是是保保证证oracle事务务特特性性的的重重要要机机制制，，通通过过锁锁机机制制保保证证了了不不同同的的事事务务不不能能同同时时发发起起对对同同一一资资源源的的并并发发修修改改。。在oracle中，，锁锁简简单单来来讲讲有有两两个个维维度度：：一个个是是锁锁的的类类别别(lock_type)，，这个个维维度度表表示示了了是是在在哪哪种种资资源源、、对对象象上上的的锁锁，，比比如如JQ表示示在在job对象象上上的的锁锁、、TM表示示对对象象锁锁(表表锁锁)、、TX表示示事事务务锁锁(行行锁锁)、、TS表示示表表空空间间(tablespace)的的锁锁等等等等。。另一一个个是是锁锁的的模模式式(mode)，，包含含0-6。。2023/1/4关于于锁锁机机制制锁的的模模式式(mode)：：0：：None1：：null2：：rowshare，，即RS、、行级级共共享享锁锁3：：rowexclusive，，即RX、、行级级排排它它锁锁4：：share，，即S、、共享享锁锁5：：sharerowexclusive，，即SRX、、共享享行行级级排排它它锁锁6：：exclusive，，即X、、排它它锁锁2023/1/4关于于锁锁机机制制不同同的的锁锁模模式式(lockmode)的相相容容列列表表见见下下：：2023/1/4关于锁机机制oracle中的不同同操作需需要对不不同的对对象加不不同模式式的锁；；通过锁锁的类别别来表示示对某种种对象加加锁；而而通过不不同的锁锁的模式式的相容容规则，，来控制制哪些操操作可以以并行，，哪些操操作是互互斥的；；通过这这样的锁锁机制来来保证每每个用户户访问对对象的正正确性。。一个操作作可能需需要对多多种对象象加锁(需要申申请一种种以上type的锁)，，同时根根据操作作的不同同申请不不同的锁锁模式(lockmode)。比如：selectforupdate操作需要要对表申申请mode=3(即RX)的TM锁(locktype=TM)，然后对选选到的行行申请mode=6(即X)的TX锁(locktype=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操作和selectforupdate操作是不不阻塞的的(同时时对一张张表增加加mode=3的TM锁是相容容的)；但如果涉涉及到相相同的行行则会阻阻塞一方方，直到到另一方方事务完完成(同同时对一一行增加加mode=6的TX锁是不相相容的)。2023/1/4关于锁机机制通过这个个过程，，我们可可以简单单理解oracle的锁机制制是如何何控制不不同操作作的相容容和互斥斥。实际际上，oracle的每种操操作都有有不同的的锁策略略(需要要申请什什么类型型的锁、、什么模模式的锁锁)，这些复杂杂的锁策策略随着着不同的的数据库库版本也也有所变变化；通通过这些些复杂的的机制，，来保证证用户访访问对象象的正确确性和一一致性。。oracle的dml锁所有锁机机制中，，最为常常见也最最为常用用的就是是进行各各种增、、删、查查、改操操作中的的dml锁机制。。dml锁，顾名名思义，，就是在在各种dml操作中产产生的锁锁，这里里主要是是出现TX、TM两种类型型锁。在dml锁机制中，TX锁会出现在实实际发生改变变的部分用于于保证dml操作的正确性性。也就是我我们通常讲的的事务锁(实实际上这个事事务所真正改改变的部分)或者行锁，用于锁定发生生改变的行，，从而保证修修改的正确性性(不同时被被其他session修改)；就像像我们之前看看到的是用了了mode=6的锁从而阻塞塞了其他的修修改操作。TM锁在这里则是是一种意向锁锁，也就是说说需要修改某某一个对象时时，对其上层层对象增加一一个锁，表明明修改其下级级对象意愿，，可以理解为为一种操作的的入队；就像像我们之前看看到的，会增增加mode=3的TM锁锁定做dml操作的表；这这个锁不会阻阻塞其他session对这张表同时时进行的增删删查改操作，，但会阻塞对对这张表的ddl操作(大部分分，会使用独独占的ddl锁定，比如addcolumn等等)，保证证对象的正确确性。2023/1/4关于锁机制v$lock视图v$lock视图记录了每每个session取得锁或者等等待锁的情况况：ID1和ID2标识了锁定的的对象，在TM和TX锁中的含义如如下：2023/1/4关于锁机制制通过v$lock视图我们就就能查到session之间持有和和等待锁以以及相互阻阻塞的情况况。更详细的有有关dml锁机制的说说明可以参参看转引网网文《oracle多粒度封锁锁机制研究究（论坛））.doc》或其他相关关资料。本文大量内内容引自该该文档和《《oracleexpertone-on-one》》相关内容。。关于死锁需要注意的的是，就一一般而言oracle中并不会长长期存在真真正意义上上的死锁。。oracle会以一个很很短的时间间去轮循，，检查是否否有死锁，，如果发现现有死锁出出现，则会会中断掉其其中一个session以解除死锁锁，并抛出出ORA-00060错误。2023/1/4关于锁机制制一个关于外外键关联在在dml操作中锁机机制的案例例oracle的dml锁中，比较较复杂的情情况之一就就是涉及到到外键关联联的情况，，由于存在在完整性约约束检查，，这里不仅仅仅会对发发生dml的表本身产产生锁，也也会对有外外键关联的的表产生锁锁。案例：locksample1.doc2023/1/4简单的sql优化sql语句的执行行过程关于索引与与表扫描关于表连接接关于排序2023/1/4sql语句句的的执执行行过过程程sql语句句的的执执行行步步骤骤hardparse与softparsesoftparse也会会有有消消耗耗2023/1/4sql语句句的的执执行行步步骤骤1、、语法法分分析析，，分分析析语语句句的的语语法法是是否否符符合合规规范范，，衡衡量量语语句句中中各各表表达达式式的的意意义义。。2、、语语义义分分析析，，检检查查语语句句中中涉涉及及的的所所有有数数据据库库对对象象是是否否存存在在，，且且用用户户有有相相应应的的权权限限。。3、、视视图图转转换换，，将将涉涉及及视视图图的的查查询询语语句句转转换换为为相相应应的的对对基基表表查查询询语语句句。。4、、表表达达式式转转换换，，将将复复杂杂的的SQL表达达式式转转换换为为较较简简单单的的等等效效连连接接表表达达式式。。5、、选选择择优优化化器器，，不不同同的的优优化化器器一一般般产产生生不不同同的的"执执行行计计划划"6、、选选择择连连接接方方式式，，ORACLE有三三种种连连接接方方式式，，对对多多表表连连接接ORACLE可选选择择适适当当的的连连接接方方式式。。7、、选选择择连连接接顺顺序序，，对对多多表表连连接接ORACLE选择择哪哪一一对对表表先先连连接接，，选选择择这这两两表表中中哪哪个个表表做做为为源源数数据据表表。。8、、选选择择数数据据的的搜搜索索路路径径，，根根据据以以上上条条件件选选择择合合适适的的数数据据搜搜索索路路径径，，如如是是选选用用全全表表搜搜索索还还是是利利用用索索引引或或是是其其他他的的方方式式。。9、、运运行行"执执行行计计划划"。。2023/1/4hardparse与softparse1-8的步骤也也就是我我们通常常所说的的parse，通过parse得到一条条语句的的执行计计划，可可以看出出parse的过程是是一个比比较昂贵贵的消费费，显然然如果每每次执行行sql都需要进进行一次次完整的的parse，那么将是是非常大大的消耗耗。因此，大大部分数数据库都都提供了了sql的共享的的机制。。一条sql语句如果果做一次次完整的的parse并生成全全新的执执行计划划，这个个过程被被称为hardparse；；如果已已经parse过并仍仍然存存在于于缓存存中的的sql语句，，再次次执行行时则则直接接使用用已经经在缓缓存中中的执执行计计划，，不需需要再再重新新生成成执行行计划划，这这个过过程称称为softparse。。正是因因为这这样，，我们们大量量使用用绑定定变量量，使使得只只是参参数不不同的的同构构sql语句在在oracle为同一一条sql语句(只是是具体体执行行时使使用的的参数数不一一样)，由由此使使得sql语句的的执行行计划划可以以得到到复用用，减减少hardparse，，尽量用用到softparse，，从而减减少parse带来的的消耗耗。2023/1/4softparse也会有有消耗耗尽管如如此，，softparse也并非非全无无消耗耗，softparse同样需需要在在sharedpool中取得得相关关内存存空间间的latch(锁住存存储sql语句、、执行行计划划以及及需要要锁住住的相相关数数据字字典的的内存存空间间)；；而对对latch的分配配和操操作本本身就就是一一个比比较耗耗cpu的动作作，latch的数量量也是是有限限的，，因此此过量量的并并发执执行，，即使使都是是softparse依然会会造成成很大大的消消耗。。案例：：实际上上如果果能够够在pga空间中中的cursorcache找到同同样的的语句句，则则不需需要再再到sharedpool中查找找，这这个过过程是是消耗耗最小小的。。默认认情况况下，，oracle并不会会去为为session缓存存cursor，，需要要我我们们去去设设置置session_cashed_cursor来指指定定oracle为session缓存存的的cursor数量量(当当然然这这会会消消耗耗pga内存存空空间间)。2023/1/4sql语句的的执行过程接下来，运运行“执行行计划”，，就是通常常sql性能最重要要的部分；；选择了怎怎样的执行行计划、如如何做表连连接、如何何进行表的的扫描、是是否使用索索引、使用用什么索引引，等等问问题。应该选择什什么样的执执行计划，，一个比较较基本的看看法，首先先应关注那那些直接的的查询条件件(也就是是表的列直直接和带入入参数进行行比较的查查询条件)，这些查查询条件中中哪些能够够首先筛选选掉较多的的记录从而而有效的降降低结果集集，那么应应当优先执执行这些查查询条件，，降低整个个sql执行过程中中需要处理理的结果集集。当然实实际上sql的执行计划划必须全盘盘考虑整个个查询过程程怎样才是是较优的查查询路径，，包括每个个环节步骤骤选择什么么索引、什什么扫描方方式、什么么表连接方方式。下面我们依依次看看这这些问题。。2023/1/4关于索引与与表扫描BTree索引的数据据结构判断是否适适合使用索索引索引使用不不合理的常常见问题2023/1/4BTree索引的数据据结构索引，正如如其名称一一样，就好好像字典中中的索引，，通过它数数据库能够够根据一些些特定的信信息很快的的定位到所所需要的数数据而并不不需要察看看全部的数数据才能得得到想要的的结果。BTree索引的数据据结构是一一个根据关关键字排序序的B+树结构(一一个多层的的N叉树)，由由一群(关关键字、值值)对组成成；关键字字就是索引引列的列值值(如果是是复合索引引，则是多多个列值)，值就是是对应记录录的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)，，关键键字字保保存存对对应应记记录录索索引引列列的的列列值值。。2023/1/4BTree索引引的的数数据据结结构构根节节点点和和内内层层节节点点的的关关键键字字表表示示一一个个范范围围，，其其指指针针分分别别指指向向了了小小于于该该关关键键字字或或者者大大于于等等于于该该关关键键字字的的节节点点群群，，如如下下图图：：叶节节点点的的关关键键字字为为对对应应的的记记录录索索引引列列的的列列值值，，除除最最后后一一个个指指针针指指向向下下一一个个叶叶结结点点外外其其余余指指针针则则指指向向了了对对应应的的记记录录(rowid)，，如下下图图：：2023/1/4BTree索引的数数据结构构如上假设设我们要要查找索索引列值值为75的记录录，只需需要在根根节点中中找到57到81这个个范围的的节点群群，然后后依次根根据范围围最终在在叶节点点中找到到索引列列为75的记录录的rowid。2023/1/4判断是否否适合使使用索引引索引之所所以能够够起到优优化查询询的作用用，就在在于它将将查询用用到的条条件(列列)作为为关键字字(其对对应值指指向对应应的记录录)并组组织为一一个排序序的结构构，这样样我们能能在这个个排序结结构中快快速的定定位到要要查找的的记录而而不需要要去遍历历全部的的数据(就好像像查字典典一样，，根据拼拼音或者者笔画就就能很快快的查到到一个字字，而不不需要把把整个字字典翻一一遍)。。相对通过过全表扫扫描找到到一条记记录，通通过索引引避免了了很多冗冗余数据据的扫描描(我们们不需要要把整个个字典中中不是我我们要查查找的字字的页也也翻看一一遍)。。但同时时我们也也看到，，对于单单独的一一条记录录而言通通过索引引扫描在在读取这这条记录录的花费费上增加加了扫描描索引和和通过rowid定位的操操作。因此不是是所有情情况下，，都适合合使用的的索引。。假设一一个字典典记录了了1000个字字，而我我们需要要查找其其中的900个个字，这这种情况况下如果果还先查查索引在在找到对对应的字字就不如如直接把把整个字字典翻看看一遍来来的要快快。同样的道道理，并并不是所所有的字字段都适适合建立立BTree索引，如如果一个个字段的的独立列列值非常常少，比比如100万的的记录却却只有10个独独立列值值，那么么任意查查询其中中一个列列值都会会查询出出10万万条记录录(10%)，，那么这这个索引引就算使使用效率率也很低低，这个个字段不不适合建建立单列列的BTree索引。。2023/1/4判断是是否适适合使使用索索引而实际际上的的经验验数据据，当当通过过索引引扫描描access的记录录数<=总总记录录数的的6%的时时候，，使用用索引引是有有效率率的，，可见见扫描描索引引的数数据结结构本本身和和通过过索引引多次次的去去accesstable也有着着相当当的消消耗。。(实实际上上计算算索引引扫描描的成成本是是用需需要accesstable的block数来计计算access的次数数，也也就是是说，，假设设索引引的顺顺序和和表存存储的的顺序序完全全一致致(比比如sequence作的主主键索索引)，则则这个个比例例可以以扩大大一些些；但但实际际上这这个假假设成成立的的情况况比较较少，，而且且即便便如此此这个个比例例也不不会很很大)另外需需要注注意的的是，，BTree索引并不记记录null值，也就是是说是用isnull或者isnotnull这样的条件件是不可能能用到BTree索引的。2023/1/4判断是否适适合使用索索引判断是否应应该使用索索引或者说说是否使用用到合适的的索引，主主要在于下下面几种情情况：tab.a=:1，，这种情况主主要看:1在整个a的独立列值值中占了多多少百分比比(也就是是a列取值为:1的记录录数占到整整个记录数数的比例)；这个比比例很低的的话则适合合使用a列的索引，，反之oracle就会倾向于于使用全表表扫描。tab.a>=:1andtab.a<=:2，这种情况主主要看:1-:2之之间这个范范围的记录录数占到总总记录数的的比例；范范围太大(比例比较较高)的话话则不适合合使用a列的索引tab.ain(list)，这种情况主主要看inlist中的列值包包含的记录录数占到总总记录数的的百分比，，这个百分分比较大的的话就不适适合使用a的索引。假假设一个列列有10个个独立列值值，而inlist中就有5个个列值，那那么平均计计算可能就就是50%，显然这这里并不适适合使用a的索引。tab.a=table.b，通过表table作为驱动表表与表tab做表连接，，连接条件件是table表的b列=tab表的a列，这里主主要看表table用于表连接接的结果集集其每条记记录的b列值对应在在tab表的a列能够选取取到的记录录数的总和和占tab表记录数的的百分比(这里用tab表a列的索引指指的是使用用nestedloop表连接方式式的情况下下，使用hashjoin或其他的表表连接方式式，这个比比例的计算算并不适用用，关于表表连接的方方式，我们们在后面讨讨论)，如如果表table用于作为驱驱动表的结结果集比较较小、且结结果集中b列的列值对对应tab表中a列的列值能能够选取的的到的记录录数比较低低，则适合合使用tab表上a列的索引。。(这里指指使用nestedloop的情况，涉涉及到表连连接索引的的使用要跟跟表连接的的方式一起起考虑，在在表连接的的部分我们们再做讨论论)2023/1/4索引引使使用用不不合合理理的的常常见见问问题题缺少少合合适适的的索索引引可可用用(选选择择更更加加优优化化的的字字段段或或者者合合理理的的复复合合索索引引首首列列)案例例1：：indexsample1.doc在这这个个案案例例中中：：语句句(1)存存在在一一个个日日期期范范围围查查询询可可以以使使用用在在日日期期字字段段上上的的索索引引，，但但是是如如果果时时间间范范围围跨跨度度过过大大，，这这个个索索引引的的效效率率也也就就不不高高了了；；语句句(2)能能够够有有查查询询条条件件的的字字段段当当中中只只有有一一个个区区分分度度很很低低的的字字段段建建了了索索引引(千千万万条条数数据据只只有有几几十十个个独独立立列列值值)，，这个个字字段段是是不不适适合合建建立立单单列列索索引引的的，，查查询询使使用用这这个个索索引引的的效效率率也也非非常常低低；；这两两个个语语句句我我们们通通过过分分析析语语句句，，都都发发现现了了有有区区分分度度比比较较高高且且适适用用的的查查询询条条件件字字段段，，只只要要在在这这些些字字段段建建立立索索引引，，就就能能优优化化语语句句的的执执行行效效率率。。这里里我我们们看看到到，，过过大大的的范范围围查查询询会会影影响响索索引引的的效效率率；；而而过过低低的的区区分分度度的的列列则则并并不不适适合合建建立立单单列列索索引引。。2023/1/4索引引使使用用不不合合理理的的常常见见问问题题缺少少合合适适的的索索引引可可用用(选选择择更更加加优优化化的的字字段段或或者者合合理理的的复复合合索索引引首首列列)案例例2：：indexsample2.doc在这这个个案案例例中中：：表cjk上原原来来有有一一个个复复合合索索引引(FZJZH,FBMDM,FSCDM,FGDDM,FZQDM,FHTXH,FMMLB,FCJSJ)，，这个个索索引引的的区区分分度度很很高高，，本本来来是是很很好好用用的的。。但但问问题题就就出出来来这这两两个个查查询询语语句句中中，，前前面面几几列列使使用用的的都都是是模模糊糊查查询询，，而而根根据据实实际际情情况况，，往往往往传传入入的的都都是是百百分分号号，，导导致致索索引引扫扫描描的的时时候候无无法法根根据据关关键键字字的的范范围围快快速速的的定定位位到到需需要要的的索索引引结结点点，，在在这这里里反反而而使使用用这这个个索索引引效效率率比比全全表表扫扫描描还还要要低低得得多多(实实际际情情况况是是几几个个小小时时)。。分析析这这条条两两条条语语句句的的查查询询条条件件，，发发现现FCJSJ这个个查查询询条条件件，，实实际际操操作作中中基基本本上上都都是是查查询询一一天天的的数数据据，，这这里里只只需需要要建建立立一一个个以以FCJSJ作为首首列的的复合合索引引(fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)，就可以以优化化语句句的效效率。。由于BTree索引是是关键键字排排序，，如果果复合合索引引的首首列不不能根根据查查询条条件有有效的的筛选选，就就需要要扫描描大量量冗余余的索索引结结点；；在这这个案案例中中由于于前面面几列列都出出现了了%号号的情情况，，导致致几乎乎是将将整个个索引引结点点扫描描了一一遍才才得到到结果果，效效率非非常低低。所所以复复合索索引要要特别别注意意首列列的选选择。。2023/1/4索引使使用不不合理理的常常见问问题不均匀匀分布布的列列值在在bindpeeking和histogram的影响响下，影响响索引的使用用首先解解释下下相关关名词词：bindpeeking：：sql语句中中使用用到绑绑定变变量，，在第第一次次执行行时会会peeking其绑定定变量量的值，，就相相当于于常量量语句句一样样，并并根据据这个个具体体值解解析计计算成成本，，解释释出执执行计计划。。这一一特性性是在在oracle9i以后引引入的的。histogram：直方图图，对对于不不同列列值更更加准准确的的数据据量的的统计计。对对于列列值分分布不不均匀匀的列列来说说，通通过直直方图图，就就能准准确计计算出出不同同列值值的数数据量量，而而不仅仅仅简简单的的根据据(总记记录数数/独立列列值数数)来来计算算其数数据量量(平平均情情况)。2023/1/4索引引使使用用不不合合理理的的常常见见问问题题不均均匀匀分分布布的的列列值值在在bindpeeking和histogram的影影响响下下，影影响响索引引的使使用用以下下引引用用《《ORACLE数据据库库优优化化案案例例简简报报(第第一一期期)》的的相相关关内内容容：：由于8i还没有bindpeeking技术，使使用绑定定变量以以后无法法使用histogram，，所以最好在编程程时对具具有skew值的列不不使用bind变量,这这样,生生成计计划时,其可可根据histogram的值来估估算返回回的数据据量,并并生成成合适的的计划。。9i引入了bindpeeking技术，使使用绑定定变量以以后可以以用到histogram，，但是如果果第一次执行带带入的变变量值失失误，很很可能产产生的执执行计划划对以后后的多次次查询不不适合而而带来性能问题题。2023/1/4索引使用用不合理理的常见见问题不均匀分分布的列列值在bindpeeking和histogram的影响下下，影响索引的使用我们看看看《ORACLE数据库优优化案例例简报(第一期期)》所举的的这个案案例：语句：selectpolicy_cert_no,apply_personnel_numfromacc_policy_certwherepolicy_no=:1andcert_type='1'selectpolicy_cert_nofromacc_policy_certwhereinsurance_card_no=:1andpolicy_no=:22023/1/4索引使用不合合理的常见问问题不均匀分布的的列值在bindpeeking和histogram的影响下，影响索引的使用这两条语句都都是对表acc_policy_cert进行查询，在在policy_no字段上有主键键索引(复合合索引的首列列)，正常的情况下下，应该走这这个索引而不不是全表扫描描。但在policy_no的列值分布并并不均匀，比比如policy_no列共有100个不同的值值，其中为70的占了99％，为其其他值的数据据行仅占1％％（即选择性性很高），则则如果不使用用绑定变量，，借助histogram，oracle能够知道，查查询policy_no=70的时候应该走走全表扫描效效率更高，查查询policy_no为其他值的语语句应该走索索引效率更高高。问题就出现了了，假设第一一次执行时带带入的是70的这个值，，显然执行计计划会走全表表扫描，但是是由于使用了了绑定变量，，以后即使是是带入其他值值，执行计划划依然会走全全表扫描。2023/1/4索引使用不合合理的常见问问题不均匀分布的的列值在bindpeeking和histogram的影响下，影响索引的使用解决办法有有3个：1)不使用用绑定变量量，但是这这样就会导导致大量的的hardparse，对sharedpool也会产生大大量的消耗耗。2)修改程程序，针对对不同情况况使用不同同执行计划划(不同的的语句)3)不收集集直方图(这样就会会按照平均均情况来计计算数据量量)或者使用hint绑定执行计计划，使其其总能使用用到索引，，这样就可可以使大部部分情况得得到较好的的效率，但但对于比如如70这样样的值就会会效率低下下。关于这个问问题详细的的解释和说说明可以参参见《ORACLE数据库优化化案例简报报(第一期期)》中相关内内容。2023/1/4关于表连接接三种主要的的表连接方方式何时使用哪哪种表连接接方式2023/1/4三种主要的的表连接方方式nestedloopjoin循环嵌套连连接：行源源1的每一一条记录，，依次去匹匹配行源2的每条记记录，将符符合连接条条件的记录录放在结果果集中，直直到行源1的所有记记录都完成成这个操作作。循环嵌嵌套连接是是最基本也也是最古老老的表连接接方式。sortmergejoin排序合并连连接：行源源1和行源源2的数据据分别排序序，然后将将两个排序序的源表合合并，符合合连接条件件的记录放放到结果集集中。由于于排序需要要内存空间间，sortmergejoin对内存有比比较大的消消耗，如果果内存空间间(8i为sort_area_size，9i及以上使用用PGA)不足，则会会使用临时时表空间，，这样会降降低排序合合并连接的的效率。排排序合并连连接是最古古老的表连连接方式之之一。hashjoin哈希连接：：将行源1计算成一一张基于连连接键的hash表，行源2的每条记记录依次扫扫描这张hash表，找到匹匹配的记录录放到结果果集。计算算hash表需要内存存空间，hashjoin同样对于内内存有比较较大的消耗耗，如果内内存空间(8i为hash_area_size，9i及以上使用用PGA)不足足，，则则会会使使用用临临时时表表空空间间，，这这样样会会降降低低哈哈希希连连接接的的效效率率。。2023/1/4三种种主主要要的的表表连连接接方方式式nestedloopjoin2023/1/4三种种主主要要的的表表连连接接方方式式sortmergejoin2023/1/4三种主主要的的表连连接方方式hashjoin2023/1/4何时使使用哪哪种表表连接接方式式nestedloopjoin表连接接方式式的适适用情情况nestedloopjoin适合于于：作作为表表连接接的驱驱动表表(也也就是是之前前的行行源1，也也称为为外部部表)记录数数比较较少或或者通通过直直接的的查询询条件件能筛筛选出出比较较少的的记录录数，，被连连接表表(也也就是是之前前的行行源2，也也称为为内部部表)在连连接条条件上上有区区分度度很高高的索索引；；驱动动表上上的每每条记记录通通过被被连接接表在在连接接条件件上的的索引引能快快速的的匹配配到少少量的的记录录；整整体的的结果果集比比较小小，这这样就就比较较适合合使用用nestedloopjoin。。nestedloopjoin选择驱驱动表表时应应优先先选择择记录录数比比较少少的、、通过过直接接查询询条件件能够够筛选选出比比较少少记录录的表表作为为驱动动表，，这样样能够够有效效的减减少匹匹配次次数。。例如如这样样的查查询语语句：：select*froma,bwherea.col1=:1anda.col2=b.col2；这里a表有100条记记录，，通过过col1=:1的条件件能够够筛选选出50条条记录录，b表只有有10条记记录；；这里里如果果以a表作驱驱动表表的话话，则则匹配配次数数是50*10；如如果用用b表作为为驱动动表的的话，，则匹匹配次次数是是10*100；显显然应应该使使用a表作为为驱动动表。。如果连连接条条件没没有很很好的的索引引、或或者作作为表表连接接的两两张表表结果果集都都相当当大，，则并并不适适合使使用nestedloopjoin。。2023/1/4何时使使用哪哪种表表连接接方式式sortmergejoin表连接接方式式的适适用情情况sortmergejoin适用于于：当当表连连接的的两张张表的的结果果集都都比较较大，，或没没有很很好的的条件件可以以筛选选，连连接条条件缺缺少很很好的的索引引时，，可以以选择择使用用sortmergejoin。由于sortmergejoin需要对对作连连接的的两张张表都都作排排序，，实际际上如如果语语句中中没有有排序序需求求，oracle更加倾倾向于于选择择hashjoin。但如果果语句句中本本身就就有排排序的的需求求，sortmergejoin则有可可能省省去单单独的的排序序。sortmergejoin对内存存消耗耗比较较大，，如果果内存存空间间不足足以完完成排排序，，则需需要用用到临临时表表空间间，效效率会会有较较大的的降低低。sortmergejoin只能用用于等等价连连接。。2023/1/4何时使使用哪哪种表表连接接方式式hashjoin表连接接方式式的适适用情情况hashjoin适用于于：当当表连连接的的两张张表的的结果果集都都比较较大，，或没没有很很好的的条件件可以以筛选选，连连接条条件缺缺少很很好的的索引引时，，使用用hashjoin能够取取得比比较好好的效效率。hashjoin虽然也也需要要将一一张表表的所所有记记录依依次和和hash表中的的记录录进行行匹配配，但但扫描描hash表的速速度要要比扫扫描BTree索引快快的多多，所所以在在大结结果集集和缺缺少良良好索索引的的情况况下，，使用用hashjoin能得到到比较较好的的效率率。与sortmergejoin相比，，hashjoin只需要要进行行一次次排序序，因因此大大部分分情况况下，，oracle会更倾倾向于于选择择hashjoin。hashjoin需要计计算一一张hashtable，，与sortmergejoin一样，，需要要消耗耗大量量的内内存空空间，如果内内存空空间不不足则则需要要用到到临时时表空空间，，效率率会有有较大大的降降低。。hashjoin只能CBO优化器器下使使用，，只能能用于于等价价连接接。例如语语句：：select*froma,bwherea.col1=:1andb.col1=:2anda.col2=b.col2在这个个语句句中，，a表通过过col1=:1和b表通过过col1=:2筛选后后的记记录数数依然然比较较大，，虽然然col2在a表和b表上都都是很很不错错的索索引，，但是是因为为作连连接的的结果果集比比较大大，使使用nestedloop效率不不高，，这时时使用用hashjoin就能得得到较较好的的效率率。2023/1/4何时使使用哪哪种表表连接接方式式案例1：《joinsample1.doc》在这个个案例例中，，原来来的执执行计计划，，选择择了首首先扫扫描两两张小小表SELECT_CONDITION_TMP，，然后与与大表表ASSET_COMBINATION进行nestedloopjoin，，两张小小表结结合起起来的的条件件大概概会形形成一一个几几百条条的结结果集集，而而与表表ASSET_COMBINATION进行nestedloopjoin也是一一个具具有一一定区区分度度的索索引，，看起起来似似乎选选择nestedloopjoin并没有有错。。但仔细细分析析：首首先这这里在在nestedloopjoin时使用的的索引是是字段the_date上的索引引，也就就是说连连接条件件curno、cmbno并没有起起到快速速查找定定位的作作用；其其次由于于两张小小表SELECT_CONDITION_TMP只是一些些查询条条件的组组合本身身数据量量非常少少，但依依然会使使得通过过the_date=:1这个条件件扫描ASSET_COMBINATION表会重复复很多次次；再次次，ASSET_COMBINATION这个表上上并没有有以curno或cmbno为首列的的索引，，而以这这两个列列作为条条件区分分度并不不是很高高。分析之下下，作为为直接条条件the_date=:1能够筛过过滤掉大大量的记记录，且且有不错错的索引引，有效的减减小结果果集，因因此这里里应该首首先扫描描ASSET_COMBINATION减少冗余余的扫描描(不必必像原执执行计划划一样重重复扫描描很多次次)；然后与两两张小表表SELECT_CONDITION_TMP进行连接接时，因因为这两两张表的的数据比比ASSET_COMBINATION通过the_date=:1选出来的的结果集集还要小小很多，，这里适适合使用用hashjoin有比较好好的效率率。2023/1/4何时使用用哪种表表连接方方式案例2：：《joinsample2.msg》在这个案案例，原原语句因因为唯一一能够首首先过滤滤掉较多多结果集集的条件件TASK_STATUS_ID=‘12’本身仍然然会筛选选出数量量较大的的结果集集，而PA_TASK_TLR_ADMIN本身是个个小表，，因此优优先使用用直接条条件筛选选掉一些些结果集集，然后后再与小小表使用用hashjoin；；看起来这这似乎并并没有什什么问题题。但仔细分分析：表表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_ID上的索引引nestedloopjoin表PA_CUSTOMER_CAMPAIGN，因为这个个索引的的效率要要好得多多了，反反而能够够取得比比较好的的效率。。2023/1/4关于排排序排序是是数据据库中中一个个比较较常见见的操操作，，使用用orderby是一个个很司司空见见惯的的东西西；但但排序序的发发生并并不只只是在在orderby的时候候，排排序产产生的的消耗耗有时时也会会产生生很大大的影影响，，对于于排序序也有有一些些需要要注意意的地地方。一个关关于排排序的的sql：投资数数据库库有一一个很很简单单的sql，是一个个统计计报表表类的的sql，只是将将一张张数据据表中中某一一段时时间的的数据据做一一个按按照一一些统统计条条件groupby的操作作；通通常会会计算算一年年的数数据，，虽然然数据据量比比较大大，但但因为为是个个统计计操作作，执执行次次数很很低，，在生生产环环境大大概每每次执执行需需要10几几秒。。但在测测试环环境的的一次次测试试中，，这条条语句句发现现执行行得很很慢，，几分分钟才才能跑跑出结结果。。对此跟跟踪之之下，，发现现这条条语句句执行行时有有大量量的directpathread/write等待(后面面我们们会讨讨论)，也也就是是说排排序使使用临临时表表空间间产生生了很很大消消耗。。经检查查，这这张表表有百百万级级的数数据量量，占占了几几百M的空间间。根根据查查询条条件约约会查查询出出1/3的的记录录数来来作统统计，，算下下来大大约是是100M的数据