查询优化器

在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
dbms_stats

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;