Oracle 性能调优学习札记(十一)- SQL 语句优化

SQL 语句优化
?? 判断SQL占用资源的方法
?? 1.OEM
?? 2.Statspack
?? 3.Explain Plan
?? 4.SQL Trace and TKPROF
?? 5.AutoTrace
??
?? 优化器模型
???? 两种优化器:
??基于的规则的RBO:(不推荐)
???1.规则在数据字典存在
???
??基于的代价的CBO:
????? 1.选择路径消耗(IO,CPU)最小的代价
???2.统计指标
??查询优化器工作原理
??1.优化器产生一系列的SQL执行计划.
??2.优化器是基于数据字典中该SQL的表存储信息,索引,数据分布,分区等信息.
?? cost是一个量化值,是一个期望出现的执行需要的资源(访问路径,排序,IO,CPU,内存).
?? 串行中Cost越高,执行时间越长,并行中比较复杂.
??3.优化器采用比较cost,选择最低的cost执行.
??
??优化目标
???默认情况下,查询优化器的目的获取最大的吞吐量.
???最短的响应时间.
???
??优化器行为
???optimizer_mode={
???CHOOSE:
???FIRST_ROWS:不建议使用.
???FIRST_ROWS_N:返回头部多长时间.
???ALL_ROWS:11g缺省值.获取最大的吞吐量.
???}
??系统级别:
???optimizer_mode={CHOOSE|FIRST_ROWS|FIRST_ROWS_N|ALL_ROWS}
??会话级别:
??? alter session set optimizer_mode
?? 语句级别
??? 使用hints;
??存储数据字典的统计数据.
??? 优化器使用的优化器存储在数据字典中,你可以收集统计信息通过dbms_stats.
??? 为了保护优化器的准确性,必须及时更新统计信息.统计信息为查询对象提供唯
??? 一性和分布情况.
??? 当使用查询优化器是没有可用 统计信息,优化器将使用一个简单的参数设置.
??? OPTIMIZER_DYNAMIC_SAMPLING.这将导致很慢的解析.
???
??? SQL使用Hints
???create bitmap index gen_idx on customers(cust_gender);
???? 查询使用hints
???? select /*+ INDEX(customers gen_idx)*/ cust_last_name,cust_street_address,
???? cust_postal_code
????? from sh.customers
????? where upper(cust_gender)='M';
???_rows
????? select /*+ First_rows */ * from emp;
???OPTIMIZER_FEATURES_ENABLE参数启用优化器采用版本行为.
???如果想采用旧版本的特性,需要设置OPTIMIZER_FEATURES_ENABLE为旧本本版本号.
???OPTIMIZER_FEATURES_ENABLE:优化器CBO采用的数据库版本.
???? 如果升级数据库版本等,OPTIMIZER_FEATURES_ENABLE的优化器版本也将跟着改变.
???
???SQL计划的管理(SQL plan management)
??????
????DBMS_SPM包中的LOAD_PLANS_FROM_CURSOR_CACHE或者LOAD_PLANS_FROM_SQLSET.
?????? 备注:在oracle9i中使用Stored outlines.oracle 9i已经过期.
?????? 由低版本迁移到高板本.
???查看执行计划
????? EXPLAIN PLAN:
????? SQL Trace:
????? AWR:
????? v$sql_plan:
????? SQLPLUS Autotrace:
???执行计划使用目的:
????? 1.当前SQL的执行计划
????? 2.index影响
????? 3.执行路径
????? 4.校验index是否有效.
????? 5.检查哪一个执行计划应该执行
?????
???诊断工具
????statspack:
????? 以gets排序.读取次数最多.
????? 以reads排序.从磁盘读的最多的.
????? 以executions排序.
????? 以parse calls排序.
????explain plan:
???????? 需要plan_table,通过utlxplan.sql创建.
????? 使用过程:
?????? explain plan for
???????? select * from scott.emp;
?????? 查看执行计划:
?????? utlxpls.sql:非并行情况.
?????? utlxplp.sql:显示并行情况下的执行计划.
?????? dbms_xplan包:
???????? select * from table (dbms_xplan.display)
?????? 同一缩进,先执行上面,
?????? 不同缩进,下执行下面.
????SQL trace AND TKPROF:
????????? 1.设置初始化参数
??????alter session set sql_trace =true ;
?????? 2.运行程序
??????alter session set sql_trace =false ;
?????? 3.trace文件具有一定格式可以使用TKPROF查看文件内容
???????? tkprof tracefile.trc output.txt [options]
??????备注:tracefile.trc在user_dump_dest目录下.
?????? 数据库实例级别:
??????? sql_true=true|false
???????? alter system set sql_trace =false ;
?????? 会话级别:
???????? alter session set sql_trace =false ;
???????? execute dbms_session.set_sql_trace(true|false);
???????? execute dbms_system.set_sql_trace_in_session(session_id,serial_id,{True|False});
?????TKPROF 统计信息指标
?????Count:执行调用的次数.
?????CPU:CPU使用的毫秒数.
?????Elapsed:
?????Disk:物理读.
?????Query:逻辑读.
?????Current:当前模式下逻辑读
?????Rows:行处理.?
????SQLPLUS autotrace 特性:
????? 1.创建plan_table
????? @?/rdbms/sqlplus/admin/plustrce.sql
????? 2.创建并授权
????? grant plustrace to scott;
????? 3.Auto语法
??????? set autotrace [off|on | traceonly] [Explain | statistics]
?????
????OEM中Oracle SQL Analyze: