基于Oracle的sql优化方案简介
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
•
格式化trace文件
Tkprof 统计
• • • • •
•
•
Count:执行调用次数 CPU:所使用的CPU时间 Elapsed:运行时间 Disk:物理读 Query:逻辑读(SQL) Current:逻辑读(DML) Rows:语句处理的行数
SQL语句优化
如何判断SQL所用的资源 Buffer gets (V$SQLAREA.BUFFER_GETS, high CPU) Disk reads (V$SQLAREA.DISK_READS,high I/O) Sorts (V$SQLAREA.SORTS, many sorts) 在定位所需要调优的SQL语句后,需要收集更多的信息来进 行调优。
索引
索引多,DML性能受到影响,索引少,又影响查询 冗余而且从不使用的索引,浪费空间和计算资源 索引是开放人员和DBA之间的桥梁
B树索引
B树索引不存在非唯一性条目,在一个为唯一性索 引中,会把ROWID作为一个额外的列追加到键上,使 得键唯一。 所有叶子块都在树的同一层上,也就是 select index_name,blevel,num_rows from dba_indexes; 索引的高度 = blevel + 1
基于Oracle的SQL 优化简介
编写人
万承书
Oracle 优化器
• Oracle的优化器有两种方式,一种是基于规则的优化方 式(RBO),另外一种是基于代价的优化方式(CBO),Oracle 推荐在oracle8以后的版本强烈推荐使用CBO的方式。 • CBO方式,看语句的代价(CBO),这里的代价一般指CPU和 内存,优化器在判断是否用这种方式时,主要参照的是表 及索引的统计信息。统计信息给出表的大小、有多少行、 每行的长度等信息。这些统计信息起初在库内是没有的, 是做analyze后才出现的,很多的时侯过期统计信息会令 优化器做出一个错误的执行计划,因此应及时更新这些信 息。
行链接
由于某一行变得太大,无法与其他的行一 同放在创建这一行的块中;这就要求这一 行离开原来的块,Oracle不能简单的移动 这一行,而是留下一个“转发地址”。对 于SQL来说,不会知道存在行迁移。但是 遗留的问题就是需要多余的I/O才能读取 该行数据。 建议使用ASSM(自动段空间管理),这样实 际上只需控制一个参数:PRTFREE。
子查询
子查询使用in 和 exists 如何选择in还是exists;一般来说,如果父查询的可 选择 性比 较小的话,选择使用exists;而如果子 查询的可选择姓 比较小的 话,选择使用in。 通俗点来讲,就是如果父查询的条件较多,记录集 较少的 话, 使用exists。如果子查询条件多,记 录集较少的话, 使用in。
Oracle 优化器
• Rule:基于规则的方式。 • Choose:默认的情况下Oracle用的便是这种方式。指的是当一 个表或或索引有统计信息,则走CBO的方式,如果表或索引没 统计信息,表又不是特别的小,而且相应的列有索引时,那么 就走索引,走RBO的方式。 • First Rows:它与Choose方式是类似的,所不同的是当一个表 有统计信息时,它将是以最快的方式返回查询的最先的几行, 从总体上减少了响应时间。 • All Rows:也就是我们所说的Cost的方式,当一个表有统计信 息时,它将以最快的方式返回表的所有的行,从总体上提高查 询的吞吐量。没有统计信息则走RBO的方式。
B树索引的使用
索引用于访问表中的很少一部分的行 通过索引访问表时,会执行大量分散、随机的I/O; 索引可以直接回答一个查询 不访问底层表,只扫描索引结构本身
B树索引例子
表中有100000行,假设SQL要读取表20%的行,也就是 20000行;再做个假设,如果行大小约80字节,那么块 大小为 8KB的的块则大约100行,这说明表大约1000个 块。 那么通过索引读取的话,大约20000个TABLE ACCESS BY ROWID操作来执行查询,那么要处理20000个块,但整个 表才1000个块。 在这种情况下,全表扫描比用索引要高效。
诊断工具
Statspack/AWR Report Explain Plan SQL Trace和TKPROF PLSQL Developer工具
Statspack /AWR Report
• • • •
SQL SQL SQL SQL
Ordered Ordered Ordered Ordered
Oracle 优化器
设定选用哪种优化模式:
Instance级别我们可以通过在initSID.ora文件中设定 OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS, 如果没设定OPTIMIZER_MODE参数则默认用的是Choose方 式。 Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来 设定。 语句级别用Hint(/*+ ... */)来设定
SQL语句优化
避免使用函数在字段上,否则会忽略索引的使用,即 使是唯一性索引。除非创建了基于函数的索引;比如 to_number(char)=number; 尽量使用多段SQL来替代复杂的SQL,比如用plsql package 可以使用hints来控制访问路径和连接顺序(join order) 避免使用复杂视图的连接 如果需要在更新后返回值,可以用DML with returning 可以使用case语句来合并多个查询;
Oracle 连接方式
排序合并连接(Sort Merge)
排序合并连接的方法非常简单。在排序合并 连接中是没有驱动表的概念的,两个互相连 接的表按连接列的值先排序,排序完后形成 的结果集再互相进行合并连接提取符合条件 的记录。相比嵌套循环连接,排序合并连接 比较适用于返回大数据量的结果。
排序合并连接在数据表预先排序好的情况下
临时表
临时表用于保存事务或会话期间的中间结果 集,临时表保存的数据只对当前会话可见, 其实已经commit,其他会话也看不到它的数 据。 临时表比常规表生成的Redo少得多。 临时表会从当前登录用户的临时表空间分配 存储空间。创建临时表不涉及存储空间分配, 不会分配初始区段。当会话在临时表插入数 据,才会创建一个临时段。 缺点之一:优化器不能正常得到临时表的真 实统计,对于CBO,有效地统计对于优化器的 成败至关重要。可以使用
by by by by
gets reads executions parse calls
wenku.baidu.com
执行计划
• •
utlxplan.sql创建plan_table explain plan for select last_name from per_all_peoples_all; 然后查询表plan_table或 select * from table(dbms_xplan.display); 检查V$sql_plan
SQL语句优化
从v$sqltext得到完整的sql语句 从sql语句知道参考了哪些表 是否定义了索引 是否使用了视图 段的统计信息 Sql语句的执行计划
SQL语句优化
避免大表的全表扫描 特别是返回记录只占表记录数的一小部分的情况下, 可以创建一个索引来消除全表扫描。 保证索引的高可选择性 重建索引和数据 使用小表来做驱动表.
Oracle 连接方式
跟表连接有关的几个HINT use_nl(t1,t2):表示对表t1、t2关联时采用嵌套循环 连接。 use_merge(t1,t2):表示对表t1、t2关联时采用排序合 并连接。 use_hash(t1,t2):表示对表t1、t2关联时采用哈希连 接。 leading(t):表示在进行表连接时,选择t为驱动表。 ordred:要求优化器按from列出的表顺序进行连接。 需要注意的是在Oracle使用hint时,如果SQL语句中表用别 名的话,那么hint中必须使用表的别名,否则hint将不会生 效。
Oracle 连接方式
哈希连接(Hash join) 哈希连接分为两个阶段: 1、 构建阶段:优化器首先选择一张小表 做为驱动表,运用哈希函数对连接列进行 计算产生一张哈希表。通常这个步骤是在 内存(hash_area_size)里面进行的, 因此运算很快。 2、 探测阶段:优化器对被驱动表的连接 列运用同样的哈希函数计算得到的结果与 前面形成的哈希表进行探测返回符合条件 的记录。这个阶段中如果被驱动表的连接
表统计数据
行数 数据块和空数据块 可用空间 行链接/迁移 行长度 分析时间
高水位线
SQL进行全表扫描时,读取高水位以下的数据块; 可以通过以下SQL: Select num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size From dba_tables;
PLSQL 工具
SQL Trace & TKPROF
•
•
在实例启用SQL Trace SQL_TRACE = True; 在会话启用SQL Trace
1. alter session set SQL_TRACE = true; 2. execute dbms_session.set_sql_trace(true); 3. execute dbms_system.set_sql_trace_in_sessio n(session_id,serial_id,true);
Oracle 连接方式
嵌套循环连接(Nested Loop) 排序合并连接(Sort Merge) 哈希连接(Hash join)
Oracle 连接方式
嵌套循环连接(Nested Loop) 1、 Oracle首先选择一张表作为连接的驱动表,这张表也称为外 部表(Outer Table)。由驱动表进行驱动连接的表称为内部表 (Inner Table)。 2、 提取驱动表中符合条件的记录,与被驱动表的连接列进行关联 查询符合条件的记录。在这个过程中,Oracle首先提取驱动表中 符合条件的第一条记录,再与内部表的连接列进行关联查询相应的 记录行。在关联查询的过程中,Oracle会持续提取驱动表中其他 符合条件的记录与内部表关联查询。这两个过程是并行进行的,因 此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说 明的是,由于Oracle最小的IO单位为单个数据块,因此在这个过 程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有 行,再与内部表进行关联连接查询的,然后提取下一个数据块中的 记录持续地循环连接下去。当然,如果单行记录跨越多个数据块的 话,就是一次单条记录进行关联查询的。
表和索引
绑定变量
oracle 中,对于一个提交的sql语句,存 在两种解析过程, 一种叫做硬解析,一种 叫做软解析。一个硬解析需要解析,制定 执行路径,优化访问计划等许多的步骤。 硬解释不仅仅耗费大量的cpu,更重要的 是会占据重要的闩(latch)资源,严重 的影响系统的规模的扩大(即限制了系统 的并发)。 在PLSQL中主要是动态SQL的使用。 1.Execute immediate SQL;
Oracle 连接方式
嵌套循环连接适用于查询的选择性强、约束性 高并且仅返回小部分记录的结果集。通常要求 驱动表的记录(符合条件的记录,通常通过高 效的索引访问)较少,且被驱动表连接列有唯 一索引或者选择性强的非唯一索引时,嵌套循 环连接的效率是比较高的。 嵌套循环连接返回前几行的记录是非常快的, 这是因为使用了嵌套循环后,不需要等到全部 循环结束再返回结果集,而是不断地将查询出 来的结果集返回。在这种情况下,终端用户将 会快速地得到返回的首批记录,且同时等待 Oracle内部处理其他记录并返回。如果查询的