sql trace与执行计划

627

----执行计划
oracle在执行一个SQL之前，首先要分析一下语句的执行计划，然后再按执行计划去执行。
分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条
SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

--oracle优化器
-------------优化器的优化方式--------------------
1、基于规则的优化方式RBO
  遵循oracle内部预定的一些规则，如当一个where子句中的一列有索引时去走索引。
2、基于代价的优化方式CBO
  代价主要指cpu和内存，优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
--------------------------------------------------

--------------优化器的优化模式--------------------
优化模式包括Rule,Choose,First rows,All rows这四种方式
1、Rule：基于规则的方式
2、Choose：默认，指的是当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,
          表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。
3、First Row：与Choose类似，所不同的是当一个表有统计信息时,它将是以最快的方式返回查询
              的最先的几行,从总体上减少了响应时间。
4、All Rows：基于代价的方式，当一个表有统计信息时,它将以最快的方式返回表的所有的行,从
             总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。
------------------------------------------------------

----------------设定优化模式---------------------
1、instance级别
在init<SID>.ora文件中设置
2、session级别
SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;
3、语句级别
Hint？？？
SQL> SELECT /*+ RULE */ a.userid, b.name, b.depart_name FROM tf_f_yhda a,
tf_f_depart b WHERE a.userid=b.userid;
----------------------------------------------------

----------------不走索引的原因及解决方案-------------
1、原因
  ♀在Instance级别所用的是all_rows的方式
  ♀的表的统计信息让Oracle 认为在CBO方式下不走索引更合理(最可能的原因)
  ♀的表很小,上文提到过的,Oracle的优化器认为不值得走索引。
2、解决方案
  ♀可以修改init<SID>.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或
    Choose,重起数据库。
  ♀删除统计信息
    SQL>analyze table table_name delete statistics;
  ♀表小不走索引是对的,不用调的。
------------------------------------------------------

------------------相关知识补充-----------------------
1、查看表或索引是否是统计信息
SQL>SELECT * FROM user_tables WHERE table_name=<table_name> AND
num_rows is not null;
SQL>SELECT * FROM user_indexes WHERE table_name=<table_name> AND
num_rows is not null;
备注：user_tables 是个视图，自动过滤Schema.

2、用CBO方式，要及时更新表和索引的统计信息，以免生成不切合实际的执行计划
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;
-----------------------------------------------------


--执行计划解读
    1、Oracle用来运行一个语句的步骤就叫做执行计划（execution plan），执行计划包
       含了语句所涉及的每个表的访问路径和连接顺序。
    2、查看执行计划：在Sql window中，输入要执行的sql语句，然后按 F5 可以得到执行计划
    3、解读顺序：按照从里到外,从上到下的次序解读分析的结果

-------------------Full Table Scans（全表扫描）----------------
    Oracle 的I/O 是针对数据块的,通常一个数据块中存储着多条记录，被请
求的记录要么聚集在少数几个块中，要么分散在大量的数据块中。而oracle 对某个表
进行全表扫描时，究竟应该读哪些数据块是根据全表扫描范围的标记-HWM(High
Water Mark) 进行的。
    全表扫描将读取HWM 之下的所有数据块，访问表中的所有行，每一行都要经
WHERE 子句判断是否满足检索条件。当Oracle 执行全表扫描时会按顺序读取每个块
且只读一次，因此如果能够一次读取多个数据块，可以提高扫描效率，初始化参数
DB_FILE_MULTIBLOCK_READ_COUNT 用来设置在一次I/O 中可以读取数据块的
最大数量。
    当一个表被大量删除记录之后，HWM 下面的大量数据块是空的，此时若对此表进
行全表扫描，Oracle 仍然会读到HWM 位置，会对全表扫描的性能产生极坏的影响。

1、无可用索引
SELECT last_name, first_name FROM employees WHERE UPPER(last_name)='TOM'
create index index_name on employees(UPPER(last_name));
2、大量数据
   如果优化器认为查询将访问表中绝大多数的数据块，此时就算索引可用，也会全表扫描
3、小表
   如果一个表HWM 之下的数据块比DB_FILE_MULTIBLOCK_READ_COUNT
要少，只需要一次I/O 就能扫完，则使用全表扫描要比使用索引的成本低，此时会使
用全表扫描。
4、并行
   如果在表一级设置了较高的并行度，如alter table table_name
parallel(degree 10)，通常会使CBO 错误的选择全表扫描。通常不建议在表级的设
置并行。
5、全表扫描hints
-------------------------------------------------------------------

------------------TABLE ACCESS BY INDEX ROWID (ROWID 扫描)----------------
    Rowid 就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位
置，因此rowid 是检索单条记录的最快方式。如果通过rowid 来访问表，Oracle 首
先需要获得被检索记录的rowid，Oracle 可以在WHERE 子句中得到rowid，但更多
的是通过扫描索引来获得，然后Oracle 基于rowid 来定位被检索的每条记录。

select * from employees where last_name='King'

rowid组成(采用64位编码)
  数据对象编号   文件编号   块编号  行编号
  OOOOOO          FFF       BBBBBB   RRR
64位编码
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

查询rowid的详细信息
select dbms_rowid.rowid_object(rowid) object_id,
       dbms_rowid.rowid_relative_fno(rowid) file_id,
       dbms_rowid.rowid_block_number(rowid) block_id,
       dbms_rowid.rowid_row_number(rowid) num
  from employees
where rownum < 5;
----------------------------------------------------------------------------

--------------------INDEX FULL SCAN(索引全扫描)------------------------------
    全索引扫描就是对整个索引进行一次逐条扫描，只需要一次I/O。进行全索引扫描
时因为有些查询条件必须对整个索引进行一次逐条扫描。
----------------------------------------------------------------------------

----------------INDEX UNIQUE SCAN(索引唯一扫描)-----------------------------
    这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时，只有
单行记录被访问。
----------------------------------------------------------------------------

------------------INDEX RANGE SCAN(索引范围扫描)----------------------------
    索引范围扫描通常发生在对一个索引字段指定范围条件时，有多行记录被访问。是
检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同的则按照rowid
升序排列。如果在语句中指定了order by字句，而且排序字段是索引字段时Oracle将
忽略order by子句。
---------------------------------------------------------------------------

INDEX XXX SCAN DESCENDING（索引降序范围扫描）
INDEX SKIP SCAN（索引跳跃式扫描）
INDEX FAST FULL SCAN (索引快速全扫描)
FILTER：In （Sql 子查询） , Exists （Sql 子查询）

-----------------------PARTITION RANGE ALL------------------------------
  如果表是分区表，则对这个表查询的Sql语句的执行计划可能会出现PATITION
RANGE ALL .
------------------------------------------------------------------------

-----------------------NESTED LOOP(嵌套连接)---------------------------
    从执行计划的角度上看，表与表连接方法共有三种，嵌套循环是其中的一种，是执
行计划中看到的最常见的一种连接。在嵌套循环中，内表被外表驱动，外表返回的每
一行都要在内表中检索找到与它匹配的行。
    嵌套循环在小表驱动大表，并且返回结果小的情况下是最快的一种连接方式。对于
嵌套循环来说，整个查询返回的结果集不能太大（大于1万不适合），要把返回子集
较小表的作为外表（CBO默认外表是驱动表），而且在内表的连接字段上一定要有索
引。
----------------------------------------------------------------------

-----------------------HASH JOIN(散列连接)------------------------------
   散列连接，又称哈希连接，是CBO做大数据集连接时的常用方式，优化器使用两个表中较
小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列
表，找出与散列表匹配的行。

  哈希表又称散列表，其定义是这样的：根据设定的哈希函数H(key)和所选中的处理
冲突的方法，将一组关键字映象到一个有限的、地址连续的地址集(区间)上，并以关键
字在地址集中的“象”作为相应记录在表中的存储位置，这种表被称为哈希表。
-------------------------------------------------------------------------

----------------------MERGE JOIN & SORT JOIN (排序合并)------------------
   从执行计划的角度上看，表与表连接方法共有三种，排序合并是其中的一种。一般
我们称排序合并为 SORT MERGE，在执行计划中表现为两个表分别作Sort Join 然后
再在一起做个Merge Jion。
   sort merge join的操作通常分三步：对连接的每个表做table access full；对table
access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性
能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为
其排序成本高，大多为hash join替代了。

    归并排序是一种排序方法，把一组需要排序元素分成两组，先分别排序，然后再归
并。这种排序方法又称为二路归并排序。
----------------------------------------------------------------------------