1.对于批处理,优化器的目标是获得最大的吞吐量;
2.对于交互式应用,优化器的目标是获得最佳响应时间;
3.优化器的行为受以下三种情况控制:(1)初始化参数OPTIMIZER_MODE;(2)SQL语句中使用的Hints;(3)数据字典中统计数据。
4.初始化参数的设置:(1)ALTER SYSTEM set optimizer_mode={Choose|Rule|First_rows|First_rows_n|All_rows} scope=spfile;
(2) AlTER SESSION set optimizer_mode={Choose|Rule|First_rows|First_rows_n|All_rows} scope=spfile;
5.优化器可以通过统计信息来选择优化方式,可以利用操作系统的定时器在凌晨业务低谷的时候更新统计信息,如采用包DBMS_STATS,以保证统计信息的处于最新状态,
6.保持优化计划的稳定性,参数OPTIMIZER_FEATURES_ENABLE引入的目的是当数据库版本变化时,仍保留原版本上的执行计划。
ALTER SYSTEM optimizer_features_enable=9.2.0.3 scope=spfile
7.对SQL进行调优的工具:(1)Statspack; (2)EXPLAIN PLAN; (3)SQL trace and TKPROF (4)SQL*PLUS autotrace feature;(5)Oracle SQL Analyze
(1) Statspack中包含:执行次数最多的SQL语句的排行榜,解析次数最多的SQL语句的排行榜,SQL语句去读取buffer cache次数最多的排行榜,从磁盘上读到buffer cache中次数最多的排行榜。
(2) 得到SQL的执行计划的方法:使用命令EXPLAIN PLAN;使用SQL Trace;使用Automatic Workload Repository;使用动态性能视图v$sql_plan;使用SQL*Plus AUTOTRACE.
SQL>@?/rdbms/admin/utlxplan.sql //创建plan_table表
SQL>EXPLAIN PLAN FOR
2 SELECT last_name FROM hr.employees;
SQL>@?/rdbms/admin/utlxpls.sql; //第一种方法:把EXECUTION PLAN从plan_table表中显示出来
SQL>SELECT * FROM TABLE(dbms_xplan.display); //第二种方法:把EXECUTION PLAN从plan_table表中显示出来
8.使用 SQL Trace 和 TKPROF,trace 文件的存放位置是由参数user_dump_dest的值决定的。TKPROF是用来解读SQL Trace的,输入是一个trace file,输出是一个报表。
SQL>ALTER SESSION SET sql_trace=True;
SQL>ALTER SESSION SET sql_trace=False;
$cd /u01/admin/pitts/udump
$tkprof pitts_ora_5390.trc myoutput.txt
9.使用SQL*PLUS的autotrace工具:
配置autotrace的步骤:(1)cd [ORACLE_HOME]/rdbms/admin
(2) log into SQL*PLus as SYSTEM
(3) SQL>@utlxplan
(4) SQL>CREATE PUBLIC SYSNONYM PLAN_TABLE FOR PLAN_TABLE;
(5) SQL>GRANT ALL ON PLAN_TABLE TO PUBLIC;
(6) SQL>@?/sqlplus/admin/plustrce;
(7) SQL>grant plustrace to public;
使用autotrace: SQL>set autot[race] {off | on | trace[only]} [exp[lain]] [stat[istics]]
SQL>SELECT ****
SQL>set autot off