racle优化器用来确定提交给系统的所有查询的最佳执行方式。
（早期）基于规则的优化器（RBO）
alter session set optimizer_mode=true;
该命令可以用来推翻初始化参数optimizer_mode的系统范围内的设置，不过只是对特定会话而言。
基于成本的优化器（CBO）
CBO基于数据的统计信息来做出决定
在Oracle 10g中，optimizer_mode参数的有效值仅是first_rows_n、first_rows和all_rows。
alter session set optimizer_mode=FIRST_ROWS_100;
帮助CBO作出更好决定的三种基本途径：生成更好的统计量、使用存储大纲或使用提示。
SQL跟踪：
alter session set sql_trace = true;
exec sys.dbms_session.set_sql_trace_in_session(sid, serial#, true);
exec sys.dbms_session.set_sql_trace_in_session(sid, serial#, flase);
在启动会话跟踪前，执行以下命令加上标识符，方便寻找trace文件：
alter session set tracefile_identifier='scotts_traccec';
阅读trace文件：
tkprof ora76492.trc trace_output.txt
Statspack报表：
$ORACLE_HOME/rdbms/admin/spreport
统计量：
1）表的统计量（行数、块数、平均行长度）
2）列的统计量（不同值的个数、NULL的个数、数据分布情况或柱状图）
3）索引统计量（块数、索引高度、集群因素）
4）系统性能统计量
收集统计量的两种方法：
analyze命令：
analyze table emp compute statistics;
analyze table emp estimate statistics sample 20 percent;
analyze table emp estimate statistics sample 1000 rows;
dbms_stats包是CBO计算统计量的首选。在将来的版本中，dbms_stats包将是计算统计量的惟一方法。
自动收集统计量的工作称为gather_stats_job,可以在DBA_SCHEDULER_JOBS视图中看到。
访问路径：
1）全表扫描
2）索引扫描
3）索引范围扫描
4）rowid查找
5）索引跳扫
6）快速全索引扫描
7）索引合并
合并：
1.Cartesian合并（Cartesian Join）：两张表均做全表扫描
2.嵌套循环合并（Nested Loop Join）：外表做全表扫描
3.排序归并合并（Sort Merge Joins）：两张表进行排序
4.散列合并（Hash Join）：小表进行散列函数，大表进行相同的散列函数和小表进行匹配
5.外部合并（Outer Join）
优化器提示：
注解必须紧跟在select、update、merge、insert或delete关键字后面。
select empid,
ename /*+ index(e emp_pk) */
from emp e
where empid in(1001, 1002);
访问路径提示：
/*+ FULL(表名)*/ 全表扫描
/*+ INDEX(表名)*/ 特定索引扫描
/*+ NO_INDEX(表名)*/ 不使用索引
/*+ INDEX_ASC(表名)*/ 在升序模式使用索引
/*+ INDEX_DESC(表名)*/ 在降序模式使用索引
/*+ INDEX_JOIN*/ 索引合并
/*+ INDEX_FFS(表名)*/ 索引快速全扫描
/*+ NO_INDEX_FFS*/ 不使用索引快速全扫描
/*+ INDEX_SS(表名)*/ 索引跳扫
/*+ INDEX_SS_ASC(表名)*/ 在升序模式使用索引跳扫
/*+ INDEX_SS_DESC(表名)*/ 在降序模式使用索引跳扫
/*+ NO_INDEX_SS(表名)*/ 不使用索引跳扫
合并提示：
/*+ USE_NL(表名A 表名B)*/ 使用嵌套循环合并的方法
/*+ NO_USE_NL(表名A 表名B)*/ 不使用嵌套循环合并的方法
/*+ USE_NL_WITH_INDEX(表名A 表名B)*/ 使用带索引的嵌套循环合并的方法
/*+ USE_MERGE(表名A 表名B)*/ 使用排序归并合并的方法
/*+ NO_USE_MERGE (表名A 表名B)*/ 不使用排序归并合并的方法
/*+ USE_HASH(表名A 表名B)*/ 使用散列合并的方法
/*+ NO_USE_HASH (表名A 表名B)*/ 不使用散列合并的方法
并行提示：
/*+ PARALLEL(4)*/ 使用并行
/*+ NO_PARALLEL*/ 不使用并行
/*+ PARALLEL_INDEX(4)*/ 使用并行化索引范围扫描
/*+ NO_PARALLEL_INDEX*/ 不使用并行化索引范围扫描
杂项提示：
/*+ APPEND*/ 启动直接路径插入模式，以使数据插入表末端
/*+ NOAPPEND*/ 不启动直接路径插入模式
/*+ CACHE(表名)*/ 将查询访问的数据块放置在LRU列表最近使用的一端
/*+ NOCACHE(表名)*/ 将查询访问的数据块放置在LRU列表最早使用的一端
/*+ PUSH_SUBQ*/ 在尽可能最早的时间计算子查询
/*+ NO_PUSH_SUBQ*/ 在尽可能最晚的时间计算子查询
/*+ DRIVING_SITE*/ 使分布式查询中另一个数据库成为该查询的驱动者
explain plan命令：
explain plan set statement_id='emp_query_1' for select * from emp where empno=1005;
显示explain plan的输出：
$ORACLE_HOME/rdbms/admin/utlxpls.sql 显示串行查询的计划结果
$ORACLE_HOME/rdbms/admin/utlxplp.sql 显示并行查询的计划结果
dbms_xplan包：
1）explain plan for select * from emp;
select * from table(DBMS_XPLAN.DISPLAY);
2）select * from emp where empno=1001;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR);
3）select * from table(DBMS_XPLAN.DISPLAY_AWR('AWR中的sql_id','plan_hash_value','database_id','详细等级'));
Oracle将大纲存储在表OL$、OL$HINTS和OL$NODES中，可以通过USER_OUTLINES和USER_OUTLINE_HINTS数据字典视图访问到。
创建大纲：
1）create or replace outline emp_outline
for gategory appl_X_outlines
on select empno, ename from emp where empno in(1001, 1002);
2）alter session set create_stored_outlines=appl_X_outlines;
select empno, ename from emp where empno in(1001, 1002);
alter session set create_stored_outlines=FALSE;
改变目录：
alter outline emp_outline change category to appl_ZZ_outln;
重命名：
alter outline emp_outline rename to hr_outline;
删除：
drop outline hr_outline;
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2013-4-11 14:16:02 |
赞(
操作
