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: