ORACLE执行计划和SQL调优

面向对象n懂基本数据库常识n有ORACLE开发使用经验n乐于接受性能优化探讨内容安排n第一部分：背景知识n第二部分：SQL调优n第三部分：工具介绍n第四部分 ROWID高级应用n第五部分 附录调优方面n1 商业逻辑n2 优化数据设计n3 优化应用程序设计n4 优化数据库逻辑设计n5 优化数据库操作优化数据库操作n6 优化访问路径n7 优化内存分配 n8优化I/O 和物理结构n9 优化资源争用n10优化所采用的平台*越靠前越重要越靠前越重要第一部分 背景知识nSQL处理流程n优化器n缓冲池n执行计划n变量绑定n索引n表连接SQL 执行的步骤n解析：安全性检查，语法检查；n创建：评估多个执行计划，并选择一个最优的执行计划；n执行：捆绑变量，执行已经创建的执行计划；n获取：获取结果集，进行转换，排序等；SQL处理流程 第1步:Create a Cursor 创建游标 第2步:Parse the Statement 分析语句 第5步:Bind Any Variables 绑定变量 第7步:Run the Statement 运行语句 第9步:Close the Cursor 关闭游标如果使用了并行功能，还会包含下面这个阶段：第6步:Parallelize the Statement 并行执行语句如果是查询语句，则需要以下几个额外的步骤 第3步:Describe Results of a Query 描述查询的结果集 第4步:Define Output of a Query 定义查询的输出数据 第8步:Fetch Rows of a Query 取查询出来的行SQL优化器(Optimizer)n概念：是一个为所有的sql语句创建执行计划的工具。n目的：生成最快的，消耗资源最少的执行计划。n两种优化器 a.RBO,Rule-Based Optimizer b.CBO,Cost-Based OptimizerRBO特性n总是使用索引（不识别位图索引或基于函数的索引）n总是从驱动表开始n只有在不可避免的情况下，才使用全表扫描n索引选择的随机特性*从从ORACLE 10G开始，开始废弃开始，开始废弃RBO优化器。这句话并不是指在优化器。这句话并不是指在ORACLE 10G中不中不能使用能使用RBO，而是从，而是从ORACLE 10G开始开始，不再为开始开始，不再为RBO的的BUG提供修补服务提供修补服务CBO 特性n前提条件：存在表和索引的统计资料；使用analyze table 和 analyze index 命令从表或索引中收集统计资料（表的记录平均长度，记录数等）；如果没有现存的统计资料，将在sql运行时收集资料，会大大降低性能；n影响CBO执行计划成本评估的初始化参数较多（optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size,hash_join_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等）SQL优化器模式n实例级通过对init.ora中OPTIMIZER_MODE参数,2会话级ALTER SESSION SET OPTIMIZER_MODE=;3语句别 hintnCHOOSE 不是优化器,决定用什么优化器的参数:如果表有分析资料,便用CBO,否则用RBOn在缺省情况下,ORACLE采用CHOOSE优化器模式CHOOSERBOCBOFIRST_ROWS_n(OLTP系统)FIRST_ROWSALL_ROWS(DSS 系统)表分析n为了使用CBO,必须经常运行analyze 命令,以增加数据库中的对象统计信息n多表连接时只要有一个表分析过，就用CBOn7i:ANALYZE和DBMS_UTILITY 从815开始有DBMS_STATUS 从10g以后可以自动分析表,有个系统job(GATHER_STATS_JOB)。SQL优化器处理体系结构n解析程序n优化程序n行源产生程序nSQL执行SQL优化器处理流程图-1SQL优化器处理流程图-2SQL优化器处理流程图-3缓冲池nPGA Program Global Area是为每个连接到Oracle database的用户进程保留的内存nSGA System Global Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。缓冲池-SGAn共享池(Shared pool)SQL语句缓冲(Library Cache)也叫库缓冲区 数据字典缓冲区(Data Dictionary Cache)n块缓冲区高速缓存(Database Buffer Cache)n重做日志缓冲区(Redo log buffer)nJava程序缓冲区(Java Pool)n大池(Large Pool)执行计划n概念 就是对一个查询任务，做出一份怎样去完成任务的详细方案.n工具 变量绑定n为什么DBA要求我们变量绑定n预编译概念n怎样处理(?,?Procedure等)变量绑定必须满足的条件n字符级的比较n两个SQL语句中必须使用相同的名字的绑定变量n两个语句所指的对象必须完全相同用户 对象名 如何访问Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table ownerSQL能否共能否共享享原因原因select max(sal_cap)from sal_limit;不能每个用户都有一个private synonym-sal_limit,它们是不同的对象select count(*0 from work_city where sdesc like NEW%;能两个用户访问相同的对象public synonym-work_city select a.sdesc,b.location from work_city a,plant_detail b where a.city_id=b.city_id不能用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.Statement和preparedStatementnPreparedStatement 对象的开销比Statement大，对于一次性操作并不会带来额外的好处npreparedstatement是预编译 npreparedstatement支持批处理nSQL注入，安全,强制类型转换n不同数据库不同IBATIS预编译证据package com.ibatis.sqlmap.engine.execution;-SqlExecutor public int executeUpdate(RequestScope request,Connection conn,String sql,Object parameters)throws SQLException ErrorContext errorContext=request.getErrorContext();errorContext.setActivity(executing update);errorContext.setObjectId(sql);PreparedStatement ps=null;setupResultObjectFactory(request);int rows=0;try errorContext.setMoreInfo(Check the SQL Statement(preparation failed).);ps=prepareStatement(request.getSession(),conn,sql);setStatementTimeout(request.getStatement(),ps);errorContext.setMoreInfo(Check the parameters(set parameters failed).);request.getParameterMap().setParameters(request,ps,parameters);errorContext.setMoreInfo(Check the statement(update failed).);ps.execute();rows=ps.getUpdateCount();finally closeStatement(request.getSession(),ps);return rows何时何时Oracle使用绑定变量性能更差使用绑定变量性能更差nhttp:/ nROWIDn索引及优缺点n其它概念n索引分类n索引扫描方式n索引的创建Oracle访问数据库的存取方式n全表扫描（Full Table Scans）n通过ROWID的表存取（Table Access by ROWID）n索引扫描（Index Scan）ORACLE 的访问路径排序n通过rowid 单行访问n通过 cluster 连接的单行访问n通过 散列键或主键的单行访问n通过主键字的单行访问ncluster 连接n散列簇键n索引簇键n复合键n单列索引n在索引列上的有界搜索n在索引列上的无界搜索n排序（order by）,合并（union）连接n索引列的最大(max)到最小(min)n通过索引列排序n全表扫描n了解SQL语句这些特性后，就应该少用order by 等之类的语句Rowid的概念 nrowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。为什么使用为什么使用ROWIDnrowid对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。我们创建一个索引时，该索引不但存储索引列的值，而且也存储索引值所对应的行的ROWID，这样我们通过索引快速找到相应行的ROWID后，通过该ROWID，就可以迅速将数据查询出来。这也就是我们使用索引查询时，速度比较快的原因索引的物理表现形式n其实就是一张物理表nROWID，索引列n因为如上原因尽量索引表空间与数据表空间分开存放,减少资源竞争索引优点n可以大大加快数据的检索速度，这也是创建索引的最主要的原因。n通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。n可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义n在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间n通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能索引缺点n创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加n索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大n当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度n容易滥建索引要求专业知识n怎样优化索引很难把握*相对于大幅提高速度的优点而言，做这些牺牲都是很划算的相对于大幅提高速度的优点而言，做这些牺牲都是很划算的索引经验n了解了优缺点，到底怎么衡量这个度n一般建议少于5个。n并不是所有的表都是走索引是最佳选择。n如小表常用的基础信息表，通常缓存在内存中 1 .在应用程序端可以缓存数据 2 .ORACLE服务器端亦可ALTER TABLE emp CACHE;alter table emp storage(buffer_pool keep)这样就被缓存到keep池中 了Recursive SQL概念 n有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为recursive calls或recursive SQL statements。如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不比关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句也都可能引起recursive SQL。简单的说，我们可以将触发器视为recursive SQL。Row Source and PredicatenRow Source(行源)：用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。nPredicate(谓词)：一个查询中的WHERE限制条件Driving TablenDriving Table(驱动表)：该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source。索引分类n逻辑上：逻辑上：Single column 单列索引Concatenated 多列索引(连接索引/复合索引/组合索引)Unique 唯一索引NonUnique 非唯一索引Function-based函数索引Domain 域索引 物理上：物理上：Partitioned 分区索引NonPartitioned 非分区索引B-tree：Normal 正常型B树Rever Key 反转型B树 Bitmap 位图索引组合索引(concatenated index)n由多个列构成的索引，如create index idx_emp on emp(col1,col2,col3,)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1=?”，也可以使用”where col1=?and col2=?”，这样的限制条件都会使用索引，但是”where col2=?”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。39树最底层的块称为叶子节点，其中分别包括索引键以及rowid，叶子节点上的块为分支块，用于在结构中实现导航。B*树索引B-tree 及反向及反向 B-treen适合于大量的增、删、改（OLTP）；不能用包含OR操作符的查询；适合高基数的列（唯一值多）典型的树状结构；每个结点都是数据块；大多都是物理上一层、两层或三层不定，逻辑上三层；叶子块数据是排序的，从左向右递增；在分支块和根块中放的是索引的范围；n反向反向 B-tree：适用于 OPS 或 RAC 环境；反转了索引码中每列的字节，降低索引叶块的争用Bitmapn适合于决策支持系统；(OLAP),数据仓库n非常适合OR操作符的查询n加快查询速度是，节省存储空间，位图采用了压缩技术实现磁盘空间缩减n基数比较少的时候才能建位图索引nBITMAP 每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值B-tree vs Bitmapn默认情况下大多使用Btree索引,Btree用在OLTP，加快查询速度;位图索引用在OLAP(联机数据分析)方面，也就是数据仓库方面,目的是在加快查询速度是，节省存储空间.nBtree索引都要耗费比较大的存储空间，位图采用了压缩技术实现磁盘空间缩减nBtree用在高基数(即列的数据相异度大)，位图用在低基数列.n位图索引的基本原理是在索引中使用位图而不是列值。通常在事实表和维表的键之间有很低的集的势(cardinality)，使用位图索引，存储更为有效，与B*Tree索引比较起来，只需要更少的存储空间，这样每次读取可以读到更多的记录.n与B*Tree索引相比,位图索引将比较连接和聚集都变成了位算术运算,大大减少了运行时间，从而得到性能上的极大的提升。合理的使用位图索引n如果要使用位图索引，初始化参数STAR_TRANSFORMATION_ENABLED应该设置为TRUE.n优化模式应该是CBO。对于数据仓库的环境中，总是应该考虑使用CBO(COST-BASEDOPTIMIZER)。n位图索引应该建立在每一个事实表的外键列上。(这只是一个一般的规则.)索引扫描方式n索引唯一扫描（index unique scan）n索引范围扫描（index range scan）n索引全扫描（index full scan）n索引快速扫描（index fast full scan）n索引跳跃式扫描(index skip scan)索引唯一扫描通过唯一索引查找一个数值经常返回单个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经常实现唯一性扫描。使用唯一性约束的例子：SQL explain plan forselect empno,ename from emp where empno=10;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID EMP ANALYZED INDEX UNIQUE SCAN EMP_I1索引范围扫描使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename=JACK and deptno=DEV语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如、=、explain plan for select empno,ename from empwhere empno 7876 order by empno;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID EMP ANALYZED INDEX RANGE SCAN EMP_I1 ANALYZED 在非唯一索引上，谓词col=5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况：(a)在唯一索引列上使用了range操作符(=explain plan for select empno,ename from big_emp order by empno,ename;Query Plan-SELECT STATEMENT CHOOSE Cost=26INDEX FULL SCAN BE_IX ANALYZED 索引快速扫描扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。索引快速扫描的例子：BE_IX索引是一个多列索引：big_emp(empno,ename)SQL explain plan for select empno,ename from big_emp;Query Plan-SELECT STATEMENT CHOOSE Cost=1INDEX FAST FULL SCAN BE_IX ANALYZED只选择多列索引的第2列：SQL explain plan for select ename from big_emp;Query Plan-SELECT STATEMENT CHOOSE Cost=1INDEX FAST FULL SCAN BE_IX ANALYZED 索引跳跃式扫描索引跳跃式扫描（index skip scan）是Oracle9i用来提高性能的新特性，对于使用复合索引的数据库应用程序意义尤为重大在一次跳跃式扫描中，每个复合索引前导字段的独特值（DISTINCT VALUE）只会被搜索一次，ORACLE9i会在复合索引前导字段每个独特值区间结合WHERE子句中的其它复合索引字段搜索符合条件的目标记录，这种做法的结果导致了在索引中的跳跃式扫描。n索引跳跃式扫描主要有两个优点：1、以前版本中的表扫描（TABLE SCAN）可能可能会转变为索引扫描，提高了某些查询的执行效率 2、应用程序使用较少的索引就能达到提高效能的目的，并且既节省存储空间，又能提高DML和维护操作的效率。索引跳跃式扫描范例create index sex_emp_id on emp(sex,emp_id);在Oracle9i版本之前，当SQL查询中包含性别和emp_id时，或者查询指定性别行的时候才可以使用这一索引.Select emp_id from emp where emp_id=123;Oracle9i的索引跳跃式扫描执行规则允许使用连接索引，即使SQL查询中不指定性别。这一特性使得无需在emp_id行中提供第二个索引。Oracle承认索引跳跃式扫描没有直接索引查询速度快，但可以这样说，相比于整个表扫描（table scan），索引跳跃式扫描的速度要快得多.SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)0 SORT(AGGREGATE)1 INDEX(SKIP SCAN)OF SEX_EMP_ID(NON-UNIQUE)在内部里，Oracle生成了两个查询，并连接结果的ROWID表selectemp_name from emp_where sex=F and emp_id=123 UNION select emp_name from emp_where sex=M and emp_id=123;索引跳跃式扫描很多情况是发生在第一列索引有，第二列跳跃的情况居多。如 create index idx_emp on emp(group_id,sex,emp_id);Select emp_id from emp where group_id=guanggao and emp_id=123;对于高顺序键（high order key）中的独特值数目，Oracle的索引跳跃式扫描性能将会降低。如果主列有50个值，Oracle要发出50条查询才能找回结果。索引跳跃式扫描只适用于硬盘空间和存储空间相当紧缺的情况。*前面提到可能会用到啊，这个得看执行计划。和环境及数据量有很大关系。所以关于索引的应用方面一切以执行计划为准，不能想当然。索引可选择性(selectivity)n比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。创建索引准则n在经常需要搜索的列经常需要搜索的列上，可以加快搜索的速度n在作为主键主键的列上，强制该列的唯一性和组织表中数据的排列结构n在经常用在连接的列上，这些列主要是一些外键外键，可以加快连接的速度；n在经常需要根据范围进行搜索的列经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的n在经常需要排序的列经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间n在经常使用在经常使用在WHERE子句中的列子句中的列上面创建索引，加快条件的判断速度表连接表连接方法n嵌套循环连接（nested loop join）：一个小的内部表和一个外部表。比较内部表的每一条记录和外部表的每一条记录，返回满足条件的记录；n散列连接（hash join）：为较小的表在RAM创建散列表（可以用来从较大的那个表读取记录）；n排序合并连接（sort merge join）：使用连接字段将两个记录集排序然后合并；n星型连接（star join）：几个小型表（将组成虚拟表）和一个大型事实表，然后进行嵌套循环连接；嵌套循环连接Nested Loopn1、Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录.n2、NESTED LOOP n嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。n常见的误区是驱动表要选择小表，其实这是不对的.驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的.n嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。嵌套循环连接(续)在这个查询中，优化器选择emp作为驱动表，根据唯一性索引PK_EMP快速返回符合条件empno为7900的记录，然后再与被驱动表dept的 deptno关联查询相应的dname并最终返回结果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查询能够快速地定位deptno 对应dname为SALES的记录并返回。排序合并连接 Sort Mergen排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。n相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果n排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如、=、=等情况下的连接（哈希连接只适用于等值连接）。排序合并连接(续)上述查询首先按dept、emp两张表的deptno先排序，然后排序好的结果集再进行合并连接返回最终的记录。哈希连接 Hash joinn由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大连接的性能很差，于是Oracle在7.3之后时排序合并推出了新的连接方式哈希连接。n1、构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算很快。n探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。n哈希连接比较适用于返回大数据量结果集的连接。n使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true，且只适用于等值连接n从Oracle9i开始，哈希连接由于其良好的性能渐渐取代了原来的排序合并连接。哈希连接(续)在这个查询中优化器首先选择dept这张表为驱动表，对列deptno运算哈希函数构建一张哈希表，然后再对被驱动表emp的deptno列运算同样的哈希函数计算得到的结果进行探测，最终连接得出符合条件的记录。星型连接 STAR JOINn应用于数据仓库，数据集庞大，很方便做多维分析n一般是一个事实表，很大，有很多数据 如一个销售情况表 他下面有几个可以用于多维分析的字段 如地域、时间、销售代表都可以是这个事实表的维 而这几个维又用专门的表来存放并与大表建立关系 就构成了一个简单的星型图 n常建立位图索引表连接类型n等连接：标准连接；from a,b where a.f1=b.f1；oracle提供nested loop、hash join、sort merge三种连接方式；n外部连接:通过在where子句的等式谓词展览馆放置一个（）来实现；from a,b where a.f1=b.f1(+)，将包括b表中不匹配的字段；n自连接：一个表与自己连接的情况；from emp a,emp b where a.f1-b.f1=10；经常调用nested loop连接；表连接类型（续）n反连接：指使用包含not in 或not exists子句的子查询进行的连接；默认使用嵌套循环算法；n半连接：指在子查询中使用in或exists子句时进行的操作；不同表连接方法的相对速度星型连接嵌套循环连接散列连接排序合并连接连接速度连接表中记录的数目少多慢快第二部分 SQL调优nSQL 调整的目标n可能导致全表扫描的操作n调整原则n分而治之SQL 调整的目标n去掉不必要的大表全表扫描；n缓存小表全表扫描；n尽量使用主机变量代替直接量，减少SQL语句的解析时间；n优化索引的使用；n优化表连接方法；n优化子查询；常见可能导致全表扫描的操作n使用null条件的查询：where xxx is null;n对没有索引的字段查询；n带有like条件的查询：where xxx like%x；n带有not equals条件的查询：,!=,not in等n内置函数使索引无效：substr()，to_char()等；n列隐式变量转换何时使用索引n查询返回的记录数n排序表40%n非排序表 7%n表的碎片较多（频繁增加、删除）优化程序操作n优化程序自动简化自动简化语句中的某些常用结构，如果结果简化执行的话，这些语句变得非常简单，如2000/10 简化为 200。也可能变复杂，如将带 OR 的运算语句转换为两个复合的子查询。对于前者可以随时进行，但后者则取决于where 子句的列上是否有索引以及选择哪种优化方法。此外，还有一些其它的转换，包括：n化简算术表达式n将IN算子转化为一系列的OR条件n将一个BETWEEN AND 转换为一对比较表达式n将 OR 转换为复合查询n视图的定义合并到条件语句中n将一个复杂语句转换成连接条件语句调整原则调整SQLn不建议用 IN 用集合表代替与其它表关联，参见tstr2numlist,str2varlist 曾测过:fa in(a,b)fa=a or fa=b 编译成这样执行n用EXISTS替代INn用not exists或者（外联结+判断为空）来代替 NOT INn用表连接替换EXISTSn用EXISTS替换DISTINCTn其它相同功能的操作运算代替;a0 改为 a0 or a0a 改为 ana0 或a代替a is not null nA=2代替A1nLike aaa%可以利用索引 like%aaa肯定不走索引，其它方案全文搜索或利用三方引擎技术如Lucene调整SQL（续）nUNION ALL代替UNIONn查询表顺序的影响;RBOnWHERE后面的条件顺序影响 RBOn 禁止使用连接列,除非有相应该索引n禁止对列使用函数和表达式,除非有相应该索引n显视指定列类型，如 fa=99禁止用 fa=99来对应varchar字段n分页时先将当前页的记录rowid取出来，然后再关联取其它信息n禁止使用select*，因对*进行解析n用TRUNCATE替代DELETE(删除全表，必要时用)n在事物完整时尽量多提交事务。可以释放相当多的资源nWhere子句替换HAVING(后面有例)n减少对表的查询(后面有例)n使用表的别名 计算记录条数计算记录条数n和一般的观点相反，count（*）比count（1）稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT（EMPNO）n 这个问题在各大论坛中，都有过相当热烈的讨论，我也并不十分准确，通过实际的测试，上述三种方法并没有显著的性能差别n按DBA要求做。count（*）用Where子句替换HAVING子句n避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。n 例如：n低效：n SELECT REGION，AVG(LOG_SIZE)n FROM LOCATIONn GROUP BY REGIONn HAVING REGION REGION!=SYDNEYn AND REGION!=PERTHn高效n SELECT REGION，AVG(LOG_SIZE)n FROM LOCATIONn WHERE REGION REGION!=SYDNEYn AND REGION!=PERTHn GROUP BY REGIONnHAVING 中的条件一般用于对一些集合函数的比较，如中的条件一般用于对一些集合函数的比较，如COUNT（）（）等等。等等。除此而外，一般的条件除此而外，一般的条件应该写在应该写在WHERE子句中子句中减少对表的查询减少对表的查询n在含有子查询的SQL语句中，要特别注意减少对表的查询。n 例如：n低效n SELECT TAB_NAMEn FROM TABLESn WHERE TAB_NAME=(SELECT TAB_NAME n FROM TAB_COLUMNSn WHERE VERSION=604)n ANDDB_VER=(SELECT DB_VER n FROM TAB_COLUMNSn WHERE VERSION=604)n 高效n SELECT TAB_NAMEn FROM TABLESn WHERE(TAB_NAME,DB_VER)n=(SELECT TAB_NAME,DB_VER)n FROM TAB_COLUMNSn WHERE VERSION=604)n减少对表的查询减少对表的查询(2)nUpdate 多个Column 例子：n低效：n UPDATE EMPn SET EMP_CAT=(SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),n SAL_RANGE=(SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)n WHERE EMP_DEPT=0020;n高效：n UPDATE EMP SET(EMP_CAT,SAL_RANGE)=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;使用表的别名使用表的别名n当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。用用EXISTS替代替代INn在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。n 低效：nSELECT*nFROM EMP(基础表)nWHERE EMPNO 0nAND DEPTNO IN(SELECT DEPTNO nFROM DEPT nWHERE LOC=MELB)n 高效：nSELECT*nFROM EMP(基础表)nWHERE EMPNO 0nAND EXISTS(SELECT X nFROM DEPT nWHERE DEPT.DEPTNO=EMP.DEPTNOnAND LOC=MELB)用用NOT EXISTS替代替代NOT INn在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS.n例如：nSELECT nFROM EMPnWHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);n为了提高效率。改写为：n（方法一：高效）nSELECT.nFROM EMP A,DEPT BnWHERE A.DEPT_NO=B.DEPT(+)nAND B.DEPT_NO IS NULLnAND B.DEPT_CAT(+)=A（方法二：最高效）nSELECT.nFROM EMP EnWHERE NOT EXISTS(SELECT X n FROM DEPT Dn WHERE D.DEPT_NO=E.DEPT_NOn AND DEPT_CAT=A);用表连接替换EXISTSn通常来说，采用表连接的方式比EXISTS更有效率n SELECT ENAMEn FROM EMP En WHERE EXISTS(SELECT X n FROM DEPTn WHERE DEPT_NO=E.DEPT_NOn AND DEPT_CAT=A);n（更高效）n SELECT ENAMEn FROM DEPT D,EMP En WHERE E.DEPT_NO=D.DEPT_NOn AND DEPT_CAT=A;用EXISTS替换DISTINCTn当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换n例如：n低效：n SELECT DISTINCT DEPT_NO,DEPT_NAMEn FROM DEPT D,EMP En WHERE D.DEPT_NO=E.DEPT_NOn高效：n SELECT DEPT_NO,DEPT_NAMEn FROM DEPT Dn WHERE EXISTS(SELECT Xn FROM EMP En WHERE E.DEPT_NO=D.DEPT_NO);nEXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。基于RBO的调整原则n驱动表的设置：在RBO中，驱动表是from子句的最后一个表；驱动表应该是返回记录最少的那个表；nWhere子句设置：限制性最强的布尔表达式放在最底层；n添加基于成本的提示，来获得更快的执行计划；基于CBO的调整原则n优化器自动执行表连接调整原则nRBO只能调用nested loop和merge sort连接；hash join和star 连接只能在CBO中得到；n尽量不要使用not in反连接子查询，把它替换成标准等连接，用外连接和where column is null子句删除多余的记录；或者尽量替换成not exists子查询，因为它将调用相关联的子查询；n半连接子查询可以重新书写成标准等连接，用select distinct子句删除重复的记录；表连接调整原则（续）n如果驱动表较小，可以完全装入hash_area_size内存中，使用散列连接（hash join）速度比嵌套循环连接（nested loop）快；n在两个表非常大的情况下，经常使用嵌套循环连接（nested loop）；n生成大型结果集的查询、不使用where子句的大表连接或表中无可用索引的查询，经常使用排序合并连接（srot merge)；n对于多个小的维表和一个大的事实表的情况下（数据仓库），经常使用星型连接(star)；SQL子查询调整子查询类型n标准子查询：in 和 exists；n反连接子查询：not in 和 not exists；n关联子查询：指在子查询内部引用外部数据表；from table1 a where (selectfrom table2 b Where a.f1=b.f1)；对于外部数据集的每一条记录，都将重新执行一次内部子查询；n非关联子查询：指在子查询内部不会引用外部的数据表；from table1 a where (select from table2 b)；内部子查询只执行一次；子查询调整原则n只要可能的话，尽可能的避免使用子查询，而用标准的连接操作来代替，这样可以使用提示来更改执行计划；n先考虑子查询的合法性，再考虑进行改写；n使用一个关联子查询时，in与exists子句的子查询的执行计划基本相同；n在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；子查询调整原则（续）n在内部子查询只有少量的记录时，非关联子查询比关联子查询执行得更快；n关联子查询使用in子句是多余的；而非关联子查询使用exists子句是不恰当的。n使用in子句的非关联子查询可以转换为标准连接操作以及使用select distinct来删除重复的记录；n使用exists子句的关联子查询可以转换为标准连接，但子查询最好只能返回一个记录；子查询调整原则（续）n非关联子查询使用not exists子句是没有意义的；n使用not in子句的非关联子查询可以转sql minus子句，性能相对会高一些；n使用not in子句的关联子查询，可以使用带有select distinct子句的外部连接操作改写；各种子查询技术总结标准子查询反连接子查询inexistsNot inNot exists关联子查询多余的自动转换为嵌套的循环连接可以重写为select distinct 外部连接可以重写为select distinct 外部连接非关联子查询自动转换为嵌套的循环连接不适合可以重写为minus操作符的嵌套循环连接不适合例子：使用in子句的非关联子查询可以使用标准连接操作以及使用select distinct来删除重复的记录；原sql语句：Select ename From empWhere empno in(select empno from bad_credit where bad_credit_datesysdate-365);改写后：Select distinct/*+rule*/enameFrom emp,bad_creditWhere a.empno=b.empno and bad_credit_datesysdate-365;使用提示进行调整提示简介n历史：第一次引入是在oracle7，用来弥补CBO的缺陷，oracle8i中工作得较好；n目的：用来更改SQL语句 的执行计划；n格式：select(update,delete)/*+hints*/或 select(update,delete)-+hint使用提示遵循的原则n注意检查语法：select/*+hint*/，/*和+之间不能有空格，必须紧跟在select之后，否则无效n使用表别名：如果指定了表别名，就不能使用表名称；n不能使用模式名称：如果指定了模式所有者，那么提示将被忽略；n检验提示：如果指定不可用的访问路径，如：first_rows优化器模式与order by子句不兼容，那么提示将被忽略；n警告：如果该提示语句书写不正确，则警告：如果该提示语句书写不正确，则Oracle就忽略就忽略掉该语句。掉该语句。使提示无效的条件nCluster，hash：与非簇表一起使用；nMerge_aj,push_subq,Hash_aj：不存在子查询；nIndex：指定的索引不存在；nIndex_combine：不存在位图索引；nParellel：调用的不是全表扫描计划；nStar：事实表中存在不恰当索引；nUse_concat：在where子句中不存在多个索引；nUse_nl：表中不存在索引；常用索引提示n。rule。all_rows。first_rows。use_nl。use_hash。use_merge。index。index_asc。no_index。index_desc（常用于使用max内置函数）。index_combine(强制使用位图索引)。index_ffs（索引快速完全扫描）。use_concat(将查询中所有or条件使用union all)。parallel。noparallel。full。ordered（基于成本）提示之一：优化器提示nRule：使oracle为查询应用基于规则的优化模式。在怀疑CBO使用了非优化的执行计划时，使用rule提示；它将忽略表和索引的统计资料；/*+rule*/nAll_rows：基于成本的优化方法。目的是提供最佳的吞吐量和最小的资源消耗。倾向于全表扫描，不适用于OLTP系统；依赖于表和索引的统计资料；/*+all_rows*/nFirst_rows:基于成本的优化方法。目的是提供最快的反映时间。依赖于表和索引的统计资料；/*+first_rows*/提示之二：表连接提示nUse_hash：对指定的表执行一个散列连接；如果有一个表较小，通常快于嵌套循环连接；在两个表非常大的情况下，散列连接经常与并行查询连接结合使用；select/*+use_hash(a,b)parallel(a,4)parallel(b,4)*/;nUse_merge：强制执行一个排序合并操作；对表执行全表扫描；通常与并行查询结合使用；最适用于生成大型结果集的查询、不使用where子句的大表连接或表中无可用索引的查询；select/*+use_merge(a,b)parallel(a,4)parallel(b,4)*/表连接提示（续）nUse_nl：强制对目标表执行嵌套循环连接；对包含两个大表的连接通常最快；可以不用更改from子句表的顺序来更改驱动表（使用CBO时，from子句的第一个表）；是CBO的默认行为，比较少用；/*+use_nl(a)*/nStar：强制使用星型查询计划；查询中至少存在三个表（一个事实表和几个维表），而且事实表（大表）存在恰当的索引（8i可以使用位图索引）；/*+star*/提示之三：反连接提示n反连接是在SQL语句中包含not in或not exist 子句时执行的操作；如果子查询返回的任何一条记录包含空值，那么该查询将不会返回记录；应尽量避免使用。nHash_aj，Merge_aj：在not in子查询的字段中不存在空值的时候，根据连接的类型，考虑使用这两个提示之一，可以在很大程度上提高not in子查询的性能；/*+hash_aj*/提示之四：索引提示nIndex：优化器将使用指定的索引；如果没有指定索引，优化器将使用表中最佳的索引；/*+index(table,index)*/nIndex_join：要求优化器使用索引连接作为访问路径；nAnd_equal：如果表拥有非唯一的单独字段索引，而且期望使用多个索引服务于该查询，那么使用该提示将合并这些索引；至少两个索引名，但不能超过五个；/*+and_equal(table,index1,in