Oracle与sql简单优化与锁机制

Oracle与sql简单优化与锁机制浅析系统运营二部徐海涛oracle数据库的基本概念与原理对象的存储，segment、extent、blockSGA、PGA内存域，内存与存储的关系事务、undo、redo与ORA-01555关于锁机制2023/1/11对象的存储oracle中的对象以segment的形式存储。我们可以在dba_segment这张视图中查询到所有我们创建的表和索引。segment由extent组成。其扩展是以extent为单位。一张表在初始化时会首先产生至少一个设定大小的extent，以后如果记录数逐渐增多，则需要扩展segment的空间，每次以设定大小扩展一个extent(即增加一个设定大小的extent到segment中)。extent由block组成。block是oracle存储中最基本的单位。一个block上会存储一条或多条数据记录，读取一条数据记录时至少需要读取出这条记录所在block。在blockheader上记录了一些非常重要的信息，包含块的类型(表还是索引)、关于块上活动和过时的事务信息、块在磁盘上的位置等等。一个segment属于一个唯一的tablespace，而一个tablespace则可以包含一个或多个数据文件。2023/1/11oracle的内存结构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/11oracle的内存结构在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/11事务、undo、redo事务事务：单个逻辑工作单元执行的一系列操作。事务遵循如下的特性：原子性：一个事务要么完全发生，要么完全不发生一致性：事务把数据库从一个一致状态转变到另一个状态隔离性：在事务提交以前，其他事务察觉不到事务的影响持久性：一旦事务提交，它是永久的oracle的事务是隐式开始的，从第一条dml语句开始(第一条取得TX锁的语句开始的，后面我们将讨论oracle的锁机制，锁也是保证事务性的重要机制，通过锁保证了不同事务不能同时修改同一资源)，到显式以commit或者rollback结束。oracle缺省的事务隔离级别：readcommitted：只能读到其他事务已提交的变更，事务中的每一条语句都遵从语句级的读一致性(即只能读到每条语句开始时其他事务已提交的变更，执行过程中其他事务提交的变更不被体现)，保证不会脏读。2023/1/11事务、undo、redo事务需要注意的是完整性约束检查的点是在语句执行结束的时候开始的，也就是说只要有一行的修改违反完整性约束，则整体条语句失败。在oracle中频繁的commit并不是一个良好的习惯：oracle的所有变化都是在SGA中完成的，然后通过后台进程同步到存储中；但这一同步过程并不是只在commit的时候才发生，而是有一定量的数据被修改就会发生；实际上每次commit的消耗都是比较小的，因为大量修改的数据其实已经写到存储中了；过于频繁的commit反而带来冗余的checkpoint(简单来讲，检查内存和存储中的信息是否完全一致，不一致则调用相关的同步操作)的消耗；只需要在应该commit时候(需要被其他事务可见的时候)commit。2023/1/11事务、undo、redoredo所谓重做，顾名思义，就是重新做已经做过的动作。redolog(重做日志)对于oracle数据库是至关重要的，数据库中的所有的改变都会记录到redolog(比如dml、ddl操作等)，一旦数据库出现故障，oracle能够根据redolog“重做”，恢复到故障前的情况。

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