oracle高性能sql调整-笔记1

 

oracle是rdbms关系型数据库 关系型数据库的设计宗旨就是为了减少数据冗余。 sql调整的意义：对于那些每天都要执行上千次的查询来说，sql调整会大大提高数据库的性能，同时可以延长数据库服务器的硬件寿命。 sql调整的目标： 1、确保所有的sql语句在执行之前得到认可   因为书写sql语句的程序员的目标和进行sql调整的操作者目标有很大的不同。程序员考虑以最快的速度得到查询的正确结果，而sql优化的dba目标可能是要达到sql语句消耗资源的最优化。 2 创建有益于所有的sql操作的oracle统计资料和索引   因为oracle得优化器cbo是基于统计信息的。rbo是基于数据字典的。 3 为所有的sql锁定执行计划。    因为添加索引可能会造成一系列的sql语句执行计划的更改。sql调整也要保证这些已经解析的执行计划能够持久化 oracle优化的全局步骤，层次结构 从上到下一个一个按照顺序来： +环境调整 服务器 网络 磁盘 ++oracle实例调整 SGA后台处理 +++oracle对象调整 表 索引 ++++oracle sql调整 原始数据库结构的设计也是影响sql速度的重要的因素之一 --oracle表中数据标准化的程度 --oracle计划冗余度的多少（通过向表中添加冗余的字段 反标准化，可以避免昂贵的sql链接，提高性能） 但是 一旦应用程序进入到生产环境的话，数据库的表设计是不可能再更改的。 sql调整的障碍 1、定位不友好的sql语句  我们需要定位这些语句来保持执行计划的持久化，sql源代码可能存在于不同的位置 2、来自管理方面的地址 sql优化 耗时而且昂贵 dba必须进行成本收益分析来证明在硬件上的节省能够抵消在数据库进行sql调整带来的费用。 3、对特定的sql生成器的调优 例如sap应用程序的产品 可以动态的生成一些sql 而对这些sql的调整是不可能的。 4、来自sql程序员的抵制   很多程序员不愿意承认他们的sql不是未达标准的sql语句 5、调整不可再用的sql语句  很多第三方应用程序 嵌入直接量 会产生很多独一无二的执行计划 对资源造成浪费 6、逐渐减少的边缘收益 dba需要确定并调整高频使用的sql语句   定位这些语句变的很困难，可能会存在一些不经常使用的sql语句，但是他们被调整后可以获得很大的收益，这样定位它们就特别的困难，因为他们在程序库缓存中只是偶尔出现 到最后能得到的收益远远小于dba为此付出的努力。 sql调整过程 --定位高频使用的sql语句 --调整sql语句 --添加索引 --更改优化器模式 --添加提示 --将调整持久化 如何定位使用频繁的sql语句？ --使用statspack  使用stats$sql_summary表来捕捉sql语句 --过滤程序库缓存  通过使用工具对当前库缓存中已经存在的所有sql语句进行解释 -----------------------statpack捕捉sql？----------------------------- 当sql语句的一些行为超过阈值的时候，oracle会向stats$sql_summary中添加记录，当阈值很低的时候数据库会更加繁忙，而且每次statspack请求快照的时候也都是这样，因此如果sql调整不再使用这些内容，那么dba从这个视图中删除它们是非常重要的。向这个视图中插入数据的条件，阈值是stats$sql_parameter表中存储的参数。 execution_th   执行次数的阈值 disk_read_th   磁盘读的阈值 parse_call_th  解析的阈值 buffer_gets_th 内存读的阈值 任何一个阈值被超出都会在stats$sql_summary视图中添加一条记录。每小时进行一次statspack取样。 缺点是 需要根据sql的调整随时调整阈值。想要调整的时候杨堤阈值才能捕捉到不经常执行的sql，但是降低阈值又会导致向该视图添加更多的记录，statspack空间很快被填满。所以调整完后要及时删除该视图中的记录。 ----------------------------过滤程序库缓存-------------------------- 第三方工具 sqlplus脚本 --------------------调整sql的操作----------------------------- 更改优化器模式 添加索引 添加提示 ---------------------------调整持久化---------------------------- 优化器计划稳定性 改变sql来源 1、优化器计划稳定性 优化器计划稳定性可以为相同的sql语句保留相同的执行计划，不用考虑数据库的变化。 如何使用？ 从$ORACLE_HOME/rdbms/admin 下运行dbmsol脚本。 创建一个OUTLN的用户 拥有dba权限，也会安装outln_pkg的软件包，为管理存储框架提供过程。 2 改变sql来源 ----------------sql 调整的一些简单目标--------------------------- 去掉不必要的大型表的全表扫描--》转化成索引扫描 缓存小型表的全表扫描--》常驻内存 到keep cache 检验优化索引的使用--》 使用正确的索引 检验优化的链接技术--》nest loop、hash ---------------sql 调整的工具箱---------------------------------- access.sql        解释程序缓存中的所有sql语句 创建一系列的报告 access_report.sql 总结sql的不同行为的报告 get_SQL.SQL       列出缓存中所有匹配的sql plan.sql   显示任何sql语句的执行计划 根据这些报告我们能得到哪些有价值的信息呢？ 确定高频使用的表和索引 确定要缓存的表  keep pool中 确定要进行记录重新排序的表 对于高频索引范围扫描的大型表 进行记录重新排序减少输入输出 删除未被使用的索引 通过添加新索引禁止全表扫描 在拥有orader by的子句中经常看到全索引扫描oracle优化器经常使用全索引扫描来避免排序。 怎样用全索引扫描来避免排序呢？ 比如 select * from emp order by empno； 方法一：可以执行全表扫描，然后对结果集进行排序，那就需要先得到全表的结果集，然后再对结果集在temp表空间进行排序。这个全表扫描可以通过使用db_file_multiblock_read_count参数很快进行多块读，或者使用并行的提示来多表进行并行化。 方法二：通过索引，按照empno的顺序读取记录，从而避免了排序。 dba_data_file   dba_free_space两个视图 集合查询oracle表空间中可用空间和已用空间的大小 如果没有创建域内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器执行全表扫描，即会出现索引失效的情况。所以，对于sql查询中使用内置函数的情况，dba需要对于表创建相应的基于函数的索引。 access.sql脚本 可以用来解释所有存在于程序库缓存中的sql语句。 通过这个报告，我们可以看到某个表一共有多少块，经历过多少次的全表扫描，这些全表扫描是否合法？这个查询速度是否可以通过使用基于函数的索引来提高？ get-sql。sql脚本是用来得到 某个sql语句的文本。 为了达到这个目的，我们可以运行get-sql.sql脚本，检查执行次数为全表扫描的次数的sql语句 getsql。sql脚本其实是查询sqlarea表 set lines 2000; select sql_text, disk_reads, executions, parse_calls from v$sqlarea where lower(sql_text) like '%tablename%' order by disk_read desc     ; 得到该sql以后就可以使用脚本plan。sql获得执行计划。 所以在表中添加索引是非常危险的操作，因为这会导致许多查询执行计划的变更。但是我们使用基于函数的索引就不会产生这样的问题，因为oracle只有在查询使用了匹配的内置函数的时候 才会使用这种类型的索引。一个匹配的基于函数的索引能够改变执行计划。   内置函数的最重要的sql调整规则：无论什么时候只要在sql语句中使用了内置函数，那么就必须创建基于函数的索引 query_rewrite_enabled 这个初始化参数指导oracle对数据库查询进行重新书写，以防止对大量数据进行重新求和。 cursor_sharing  当这个初始化参数设置为force时，它指导oracle在失去了、语句中使用主机变量替代直接量 SQL> show parameter rewrite; NAME                                 TYPE ------------------------------------ ----------- VALUE ------------------------------ query_rewrite_enabled                string TRUE query_rewrite_integrity              string enforced 关于物化视图： 我们可以对于经常访问的基础表的求和建立一个物化视图sum_sal，比如，对emp表的sal列进行求和，然后当sql访问该求和的时候，select sum(sal) from emp;我们可以设置rewrite的参数为true，那么优化器会对该sql查询进行强制重写，从物化视图中读取数据，而避免了对emp表的数据进行大量的重新求和的过程，但是如果我的sql语句采用了绑定变量的方法，就没有办法用物化视图进行重新书写了。 cursor_sharing 参数 force  强制使那些 除了变量以外完全相同的语句共享一个指针 这个功能可以通过系统生成的捆绑变量替代直接量的方法实现 这样的替代可以增加对直接量sql的共享。 exact 使得完全相同的sql共享一个指针 但是如果设置cursor_sharing=force也是存在一些风险的，如果他们使用同一个执行计划。比如走索引。那么举个例子说 假设一张销售表在local列上有4个值，east，west，north，sourth。其中west的值占了90%的比例，那么当然是where条件以west进行筛选的话，走全表扫描是最优的，但是由于进行了sql共享，使用了同一个走索引的执行计划，那么就会降低sql的性能了，所以在9i以后 oracle做了改进，cbo在对指针的第一次调用时 窥视用户自定义的捆绑变量值，这使得优化器会对where子句操作器再进行选择，那么就会在south出现的时候更改执行计划。 在这种非常不平衡的字段中使用捆绑变量时 会大大增强cursor sharing的性能。 ------------------------减少sql 解析的技术----------------------- sql调整的目标之一是确保所有经过预解析的sql语句是可以多次使用的。这要求输入的sql是完全匹配的，一个微小的变化都会导致oracle对sql语句进行重新解析。 v$sql 视图的 execution字段可以查看sql语句被重新使用的次数。 1、讲所有的sql语句防止在存储过程中 2、在sql语句中避免使用直接量 ---------------------生成执行计划----------------------------- oracle优化器的只能是决定最快最有效的方法为查询服务 对于oracle来说 查询速度和查询效率是完全不同的两个概念 oracle的两个优化器目标： -----最大速度  以最短的时间返回结果集 适用于OLTP 在线事务处理系统 通过使用oracle的first rows优化器模式实现（一遍将已经得到的结果返回给客户一遍进行其余结果的搜索） -----最小的资源占用  使用最少的机器和磁盘资源 它适用于查询实施的速度不作为主要考虑的那些面向批处理的 oracle数据库   这个目标使用oracle的all rows优化器模式实现 优化器默认值参数是 optimizer_mode SQL> show parameter optimizer_mode NAME                                 TYPE ------------------------------------ ------------ VALUE ------------------------------ optimizer_mode                       string ALL_ROWS oracle使用两种类型的优化器 基于规则的优化器 RBO 使用数据字典中索引的信息 基于成本的优化器 CBO     使用analyze和dbms_gather包收集出的统计信息 oracle默认的优化器模式是choose 如果没有统计资料 oracle选择RBO优化器模式 如果有统计资料 oracle选择CBO 在复杂的查询中 三表做关联的情况下，choose非常危险，因为如果只有其中一个表有统计信息，oracle也会选择CBO，并在运行的时候 对于没有统计信息的表进行抽样估计，这是会检查dba_table视图的num-rows字段决定的，num-rows空就会进行抽样，非空说明有统计信息。在运行过程中对表做分析抽样估计的动作是非常消耗资源的，会造成单个查询性能的大大降低。 -----------------------表的访问方式--------------------- 全表扫描 散列获取 rowid访问 1、全表扫描 对表中所有块都进行读取，被删除的空块也会进行扫描； 通用的原则：sql查询要求返回表中大多数的数据块，否则应避免使用全表扫描。 特殊情况： 使用oracle并行查询 P00n 对第n部分扫描 同时进行 多cpu的数据库服务器上使用db-file-multiblock-read-count 全表扫描的条件： --表没有索引 --查询中没有where条件 --四种情况导致的索引失效 函数 数据类型不一致 范围 不等条件 --查询使用like操作符 参数以% 开始时候 --使用基于成本的优化器而且表中的记录很少的时候，小表全表扫描优 --在初始化文件中存在optimizer-mode=all-rows时候 如果一个表经过大量删除，中间有很多空块，那么进行全表扫描，扫描空块没有意义，浪费资源，那么就需要京杭对表进行重新组织。 2、散列访问 主要是针对 散列聚簇表来说的。通过一个哈希关键字和一个哈希函数来做运算直接找到存储数据的数据块和行，所以说可以经过一次io获得数据。 事实上在散列聚簇中数据就是索引，因为数据决定行的物理位置。散列聚簇表中ORACLE根据行的码值，利用内部函数或提供的函 数对聚簇码值进行运算，以决定数据的物理存储位置。散列聚簇 通常意味着如果通过聚码访问的话，一个IO就能够提取到所需的 数据。 散列聚簇要点： 1、 散列聚簇通过散列码查询的时候需要的IO很少。几乎一个IO就可以提取到所需的数据，除非发生了行溢出。而传统索 引至少需要2个IO才能得到数据。 2、 散列聚簇查询CPU开销大。散列聚簇是CPU密集型的，而索引是IO密集型的。 3、 对表中数据量比较有把握，如行数，每行占用空间，有合理的上限，正确设置好HASHKYES和SIZE参数，那么散列聚簇将比较适用。 4、 散列聚簇降低DML性能。 5、 总是经常通过HASHKEY等值访问数据。 3、rowid访问 rowid访问是获得单行数据最快的方法 索引中存储了rowid，我们先从索引中收集rowid，然后使用rowid进行记录的读取。 ------------------------------索引访问方式---------------------- 索引范围扫描 单个索引扫描 降序索引反问扫描 and-euqal过滤器 1、索引范围扫描 索引范围扫描将从索引中读取rowid列表，如果索引的集群因子很高，那么每个rowid就会指向不同的数据块，访问不同的数据块将导致磁盘的输入输出，如果是磁盘的话就会产生磁头新的寻道。我们需要将表中的记录按照主索引的物理顺序进行重新排序，这样可以将索引范围扫描过程中的磁盘输入输出的数量降低很多。 dba_index 视图中的clustering-factor字段提供了集群引资的数量 --当集群因子数与数据块的数量非常接近时，表中的记录就会与索引同步 --当集群因字数与记录数量接近的时候是最差的情况 2、快速全索引扫描 有些sql查询可以只读取索引而不用去读取表中的数据，因为索引本身就可以满足这个查询。 db-file-multiblock-read-count也可以作用于索引全扫描的情况，同时系统也允许对索引全扫描进行并行查询，进一步加快了反应速度。 要求使用索引快速全扫描的情况： --所有要求查询的字段必须都在索引中 --查询返回大于索引中所有记录的10%。百分比试由多块读取的程度和并行度两个参数决定。 --要计算表中符合特定条件的记录的数目。比如使用count（*）的操作。 一般使用并行的快速完全索引扫描作为查询最快的方法，但是这个过程会有很多因素介入，需要对满足快速全索引扫描的任何一个查询进行并行时间的测试，并查看反应速度是否真的有所提高。 --------------------sql  链接------------------------------------- 常见链接方法： 嵌套循环链接 散列链接 排序合并链接 start with 查询链接 connect by 查询链接 1、嵌套循环链接 内部表 驱动表 小表 外部表 大表 oracle比较内部数据集的每一条记录和外部数据集的每一条记录，并返回满足条件的记录。 当中间结果数据集比较小的情况下，嵌套循环链接有最快的反应速度。 而在中间结果数据集非常大的情况下，散列链接会为我们提供最好的总体吞吐量和更快的执行速度。 2、散列链接 散列链接是在驱动表中执行全表扫描，然后在存储上建立一个散列表，散列表可以从最大的那个表中读取数据。 在散列链接中 两个表都通过全表扫描进行读取（通常是多块读+并行查询） 散列反链接  anti-jion not in 子句，使用散列反链接 散列半链接  half-join hash-multiblock-io-count的初始化参数决定由散列链接执行的多块读取的数量。 3、排序合并链接 sort merge join 排序合并链接是指从目标表中读取两个记录数据集，并使用链接字段将两个记录集分别排序。合并过程将来自一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。 三种最受欢迎的链接排序： 嵌套循环链接 &排序合并链接：更大的吞吐量 排序 大记录子集快 不需要索引 散列链接 更短的反应时间 不排序 对于大记录子集慢 需要索引 -----------------------对sql结果集进行排序---------------------- order by join group by aggregate 获取一个单独的记录 是对一组选定记录应用分组函数所得的结果 select unique select distinct create index 在内存中将根据sort-area-size init。ora 参数划分一个全局程序区——排序的空间 对于多线程服务器的链接，那么排序空间将位于large-pool中 dba必须在为较大的排序任务分配足够的排序空间 以避免在磁盘桑排序 也不能为并不需要执行很多排序擦做的任务分配太多空间  要在二者间保持一个平衡 不适合sort-area-size的排序任务将分页到temp表空间进行磁盘排序 磁盘排序执行速度比内存排序的执行速度慢一万多倍 一个专门用来排序的空间的大小决定于 sort-area-size 为每一个单独的排序分配空间是由 sort-area-retained-size 决定 排序无法在指定空间完成，那么将调用临时表空间的磁盘排序。 磁盘排序缺点： 降低单个任务的速度 影响oracle实例其它正在运行的任务 空闲缓冲区等待 分页的代价 磁盘排序很慢