查询优化器

太过爱你忘了你带给我的痛 2021-11-17 03:08 380阅读 0赞

**在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;**

** **

转载于:https://www.cnblogs.com/jzbml/p/6042793.html