备份与恢复

第一部分 Oracle性能优化的基本概念和原理

一、成本的含义

两层含义：

1、从表面看，是诸如SQL的执行计划等工具导致的执行的次数。

2、SQL语句执行的时候导致的实际的消耗。

综上所述，将“成本”定义为指由优化器执行计算而导致的消耗，也就是优化器对执行语句所用的时间的最优估计。

二、优化器的模式

优化器——数学模型，就是一段、一组、一套程序。

1、RBO优化器（优化法则）——执行机制很简单，人为通过既定的规则控制SQL语句的执行，表的内容对执行计划没有影响。从oracle 10g版本开始，RBO优化法则被全面淘汰！

2、CBO优化器（优化法则）——执行SQL的时候，尝试找到最低成本的数据访问的方法，而且考虑表和索引的内容。

说法：基于成本的优化器，基于代价的优化法则、CBO优化器、CBO优化法则、基于代价的优化器＝ CBO

参数：

SQL> show parameter opt

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode string ALL_ROWS

ALL_ROWS:用能返回结果集的所有行来表示最短时间内完成语句的执行计划，默认用CBO

FIRST_ROWS_n:用能返回结果集前n行来表示最短时间内完成语句的执行计划，默认用CBO

CHOOSE——提供了一种时运行选择方式，可以在RBO和CBO之间切换，9i版本默认值，建议在10g中不用。

RULE——基于规则的优化器，默认是RBO。

三、动态采样

在段对象（表、索引、分区）没有分析的前提下，为了使优化器得到足够的信息，以保证做出正确的执行计划而发明的一种技术。

可以看成是分析手段的一个补充，直接从需要分析的对象收集一些数据块（采样）来获得CBO需要的信息。

案例：

SQL> conn scott/scott

Connected.

SQL> create table t as select owner,object_type from all_objects;

Table created.

SQL> select count(1) from t;

COUNT(1)

----------

50273

SQL> set linesize 10000

SQL> set autotrace traceonly explain

SQL> select * from t; －－没有人为分析的情况下，自动选择采样。

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 48855 | 1335K| 35 (3)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 48855 | 1335K| 35 (3)| 00:00:01 |

--------------------------------------------------------------------------

COST＝将所有的消耗转化为单块读的次数。

记录行数和实际并不相符合，因为采样毕竟是有误差的。

Note

-----

- dynamic sampling used for this statement －－用动态采样得到的统计信息

禁用动态采样后执行计划变化

SQL> select /*+ dynamic_sampling(t 0) */ * from t; －－采样级别为0，表示不动态采样

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12007 | 328K| 34 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 12007 | 328K| 34 (0)| 00:00:01 |

--------------------------------------------------------------------------

不用动态才样，估计行数为12007，误差非常离谱。

结论：有动态采样虽然有少许误差，但是也比不采样要好的多。最起码执行计划不会很离谱。

所以默认的动态才样是没有分析的前提下的补救办法，oracle默认使用动态采样。

正常的情况：

1、手工收集统计分析信息

2、当用户执行SQL的时候，会根据收集统计分析信息生成执行计划

3、按照执行计划去执行SQL语句

如果没有找到统计分析的结果，直接用动态才样来完成执行SQL。

尝试手工分析：

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> select * from t; －－有了分析信息之后就不会动态采样了。

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50657 | 692K| 35 (3)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 50657 | 692K| 35 (3)| 00:00:01 |

--------------------------------------------------------------------------

基本接近真实值。但是还是有误差。

SQL> exec dbms_stats.gather_table_stats(user,'t',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50273 | 687K| 35 (3)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 50273 | 687K| 35 (3)| 00:00:01 |

--------------------------------------------------------------------------

当分析的百分比达到100的时候，和真实值一样的。

SQL> conn scott/scott

Connected.

SQL> delete from t;

50273 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.delete_table_stats(user,'t'); －－删除统计分析信息

PL/SQL procedure successfully completed.

SQL> select * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 34 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 1 | 28 | 34 (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> select /*+ dynamic_sampling(t 0) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12007 | 328K| 34 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 12007 | 328K| 34 (0)| 00:00:01 |

--------------------------------------------------------------------------

没有用采样，也没有分析，此时的CBO运算结果和没有删除数据的时候是一样的。

不用动态采样的时候，根据HWM来计算的，delete之后HWM不会下降，oracle简单的根据HWM来估算行数。貌似数据还存在。

SQL> select * from t; －－默认的采样级别，默认3级

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 34 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 1 | 28 | 34 (0)| 00:00:01 |

--------------------------------------------------------------------------

Note －－动态采样是不根据HWM来判断的。

-----

- dynamic sampling used for this statement

此时删除掉数据之后再做统计分析

SQL> exec dbms_stats.gather_table_stats(user,'t',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain

SQL> select * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 34 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 1 | 28 | 34 (0)| 00:00:01 |

--------------------------------------------------------------------------

区别：

1、场景不一样，统计分析结果会存在磁盘里，但是动态采样的结果不存磁盘，所以每次动态采样都会被强制执行（没有分析结果的话）。

存储在磁盘上的数据字典表，每次执行SQL的时候，会根据这些分析结果按照CBO的数学模型组装执行计划，执行计划存储在共享池的库缓存里，执行计划中需要的数据字典中存储的分析信息，存储在数据字典高速缓存。显然，动态采样信息不具有共享性，加大了解析的资源损耗。

2、分析范围指标不一样，动态采样是靠加大级别来加大分析的范围，而统计分析是靠SP中参数estimate_percent来加大分析的范围。

3、分析的程度不一样，动态采样是采样评估，统计分析是对CBO数学模型中的众多指标进行数据运算。从本质上来看，统计分析的到的结果会更加准确。

4、动态采样仅仅是补救措施，而不是得到准确执行计划的专有方法。

动态采样的级别：

SQL> show parameter dynamic

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling integer 3

加大级别方法：

1、改参数

2、加提示/*+ dynamic_sampling(t n) */

动态采样的级别：

0：不做采样

1：oracle对没有分析的表作动态采样，但是要同时以下四个条件

A SQL中至少有一个表没分析过

B 未分析的表出现在关联查询或者子查询中

C 未分析的表没有索引

D 未分析的表占用的数据块大于动态采样默认的数据块（32个）

2：对所有未作分析的表动态采样，动态采样的数据块是默认的数据块（32个）的两倍

3：在满足level2的基础上，同时包括那些谓词（where条件）需要动态采样的表作分析，动态采样按默认的数据块（32个）（分析过的表）。对于没有分析的表，动态采样的数据块是默认的数据块（32个）的两倍

4：在满足level3的基础上，同时还包括一些表，包含一个单表的谓词会引用另外两个或者更多的列。动态采样按默认的数据块（32个）（分析过的表）。对于没有分析的表，动态采样的数据块是默认的数据块（32个）的两倍

5～9：在满足level4的基础上，对于没有分析的表，动态采样的数据块是默认的数据块（32个）的2、4、8、32、128倍

10：在满足level9的基础上，对表所有的块作动态采样。

采样的数据块越多，的到的分析结果越接近于真实，但是资源消耗也会很大。

案例：

首先确认表的统计信息没有，有的话删掉。

SQL> exec dbms_stats.delete_table_stats(user,'t');

PL/SQL procedure successfully completed.

作测试：

SQL> truncate table t;

Table truncated.

SQL> insert into t select owner,object_type from all_objects;

50273 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace trace exp

SQL> select /*+ dynamic_sampling(t 0) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 19930 | 544K| 56 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 19930 | 544K| 56 (2)| 00:00:01 |

--------------------------------------------------------------------------

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 1) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40535 | 1108K| 56 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 40535 | 1108K| 56 (2)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 2) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 55454 | 1516K| 57 (4)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 55454 | 1516K| 57 (4)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 3) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 53152 | 1453K| 57 (4)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 53152 | 1453K| 57 (4)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 4) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50273 | 1374K| 56 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 50273 | 1374K| 56 (2)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 10) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50273 | 1374K| 56 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 50273 | 1374K| 56 (2)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL> create table t1 as select * from t;

Table created.

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+ dynamic_sampling(t 10) dynamic_sampling(t1 10) */ * from t,t1 where t1.object_type=t.object_type;

Execution Plan

----------------------------------------------------------

Plan hash value: 2914261090

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 74M| 3969M| | 1340 (80)| 00:00:17 |

|* 1 | HASH JOIN | | 74M| 3969M| 1968K| 1340 (80)| 00:00:17 |

| 2 | TABLE ACCESS FULL| T | 50273 | 1374K| | 56 (2)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 50273 | 1374K| | 35 (3)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."OBJECT_TYPE"="T"."OBJECT_TYPE")

Note --数据量大，SQL复杂的话，加大采样级别，性能损耗很大。

-----

- dynamic sampling used for this statement

用统计分析

SQL> exec dbms_stats.gather_table_stats(user,'t',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select * from t,t1 where t1.object_type=t.object_type;

Execution Plan

----------------------------------------------------------

Plan hash value: 2914261090

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 702M| 18G| | 10191 (98)| 00:02:03 |

|* 1 | HASH JOIN | | 702M| 18G| 1280K| 10191 (98)| 00:02:03 |

| 2 | TABLE ACCESS FULL| T | 50273 | 687K| | 56 (2)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 50273 | 687K| | 35 (3)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."OBJECT_TYPE"="T"."OBJECT_TYPE")

－－－研究？

SQL> select count(1) from t,t1 where t1.object_type=t.object_type;

COUNT(1)

----------

702567329 --统计分析是比较准确的。

动态采样的作用：

1、从oracle10g开始，RBO彻底退出了历史舞台，CBO要求充分的统计信息，但是不是每个用户都认真去做分析的。为了尽量保证执行计划尽可能的准确，oracle使用动态采样帮助CBO获得尽可能多的信息。

2、当SQL中涉及到全局临时表的时候（临时表空间上创建的表，不存数据，事务中可以插入数据，但是事务结束的时候，表被清空了），同常这种表是不分析，此时要想获得临时表上的统计数据，只能借助动态采样加以补救，来保证执行计划尽可能的准确。

SQL> create global temporary table tmp(x int);

Table created.

SQL> insert into tmp values(1);

1 row created.

SQL> select * from tmp;

----------

SQL> commit;

Commit complete.

SQL> select * from tmp; －事务结束后数据就清掉了

no rows selected

SQL> insert into tmp values(1);

1 row created.

其他的会话，无法查询到临时表的数据。

SQL> conn scott/scott

Connected.

SQL> select * from tmp;

no rows selected

此时也可以对这个表分析，但是毫无意义，因为数据很快就没了：

SQL> exec dbms_stats.gather_table_stats(user,'tmp',estimate_percent=>100);

PL/SQL procedure successfully completed.

一、在OLTP系统上，事务型的数据库，（淘宝、电信BOSS、12306、当当、1号店——每个事务的数据量小，但是并发量大），不建议用动态采样，一定要作统计分析。因为动态采样会强制硬解析（采样）。

经常查话费的SQL需要共享，所以分析是必要的。

select * from t_boss where telphone='123642767987';

二、OLAP数据库（数据仓库、数据立方体、报表系统、数据集市、数据挖掘系统）——每个事务的数据量大，但是并发量小。

1、报表指标数据库。

2、数据分析数据库

3、精准营销、数据营销

建议：不用统计分析，直接动态采样，并发量小，所以采样带来的硬解析可以忽略。将采样级别设为3或者4。

个人看法：建议去分析。如果分析的话，执行计划比较准确的保存在共享池中，我们不能保证每次的动态采样都是准确的，毕竟两种手段是不一样的，这样可以减小误差。

第二部分：解释执行计划的方法

1、autotrace

set autotrace off 默认是关闭

SQL> set autotrace on exp －－显示查询结果，显示执行计划

SQL> select * from t where rownum<=1;

OWNER OBJECT_TYPE

------------------------------ -------------------

SYS TABLE

Execution Plan

----------------------------------------------------------

Plan hash value: 508354683

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| T | 50273 | 1374K| 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

Note

-----

- dynamic sampling used for this statement

SQL> set autotrace on －－显示查询结果，显示执行计划，显示物理统计信息

SQL> select * from t where rownum<=1;

OWNER OBJECT_TYPE

------------------------------ -------------------

SYS TABLE

Execution Plan

----------------------------------------------------------

Plan hash value: 508354683

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| T | 50273 | 1374K| 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

476 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace on stat －－显示查询结果，显示物理统计信息

SQL> select * from t where rownum<=1;

OWNER OBJECT_TYPE

------------------------------ -------------------

SYS TABLE

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

476 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace traceonly －－不显示查询结果，显示物理统计信息和执行计划

SQL> select * from t where rownum<=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 508354683

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| T | 50273 | 1374K| 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

476 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace traceonly exp －－只显示执行计划

SQL> select * from t where rownum<=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 508354683

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| T | 50273 | 1374K| 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

Note

-----

- dynamic sampling used for this statement

SQL> set autotrace traceonly stat －－只显示物理统计信息

SQL> select * from t where rownum<=1;

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

476 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

只要有traceonly，一定不显示查询结果；只要有on，一定会显示查询结果。

2、dbms_xplan --仅仅显示执行计划

SQL> explain plan for select * from t;

Explained.

SQL> set linesize 10000

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50273 | 1374K| 56 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 50273 | 1374K| 56 (2)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

PLAN_TABLE_OUTPUT

- dynamic sampling used for this statement

12 rows selected.

看执行计划

SQL> grant dba to plsql;

Grant succeeded.

SQL> conn plsql/plsql

Connected.

SQL> set autotrace on

select h.hrc_descr,o.org_short_name

from org_tab o,hrc_tab h

where o.hrc_code=h.hrc_code

4 order by 2;

HRC_DESCR ORG_SHORT_NAME

-------------------- ------------------------------

CEO/COO Office of CEO ABC Inc.

CEO/COO Office of CEO DataPro Inc.

CEO/COO Office of CEO XYZ Inc.

VP Office of VP Mktg ABC Inc.

VP Office of VP Sales ABC Inc.

VP Office of VP Tech ABC Inc.

Manager update short 3

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 566430324 －－执行计划的ID，唯一的标识执行计划的ID

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 312 | 8 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 8 | 312 | 8 (25)| 00:00:01 |

|* 2 | HASH JOIN | | 8 | 312 | 7 (15)| 00:00:01 |

| 3 | TABLE ACCESS FULL| ORG_TAB | 8 | 216 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| HRC_TAB | 9 | 108 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Id——每个步骤的唯一标识，带＊号标识下面的谓词部分有一个过滤或者关联条件。

Operation——当前访问数据的方法名称

Name——当前步骤访问的对象

Rows—当前步骤输出的记录行数

Bytes——字节，当前步骤输出的记录大小

Cost——当前节点以及所属子结点的代价之和

Time——执行当前步骤需要的时间

(%CPU)——当前步骤损耗的CPU资源

操作的顺序：后序遍历二叉树

ID排列的顺序：前序遍历二叉树

Predicate Information (identified by operation id): 谓词操作明细

---------------------------------------------------

2 - access("O"."HRC_CODE"="H"."HRC_CODE")

access——改变执行路径（关联条件）

filter——谓词过滤

Statistics 物理统计信息

----------------------------------------------------------

705 recursive calls －－递归调用的次数（其中有一部分在作硬解析），在CBO的数据模型中作相关的运算。缓存中有了共享结果之后，再次执行这个SQL，递归调用为0次

0 db block gets －－当前读，发生数据修改的时候，从磁盘的非UNDO表空间数据文件数据读取到缓存数据块次数＋缓存中已经存在的非UNDO表空间的数据块次，一定是修改数据的时候才会有值。

175 consistent gets 一致性读查询或者修改的时候，从磁盘的UNDO表空间数据文件数据读取到缓存数据块次数＋缓存中已经存在的UNDO表空间的数据块次＋查询涉及到的数据块已经在缓存中的。通常用这个指标来衡量SQL语句的性能优劣。

15 physical reads －－磁盘上读取的数据块次

0 redo size －－SQL引发的数据块量

728 bytes sent via SQL*Net to client 通过客户端从服务器发送到客户端的数据量

400 bytes received via SQL*Net from client 通过客户端从客户端发送到服务器的数据量

2 SQL*Net roundtrips to/from client －－从服务器到客户端的往返次数

14 sorts (memory) －－PGA中的排序操作量

0 sorts (disk) －－临时表空间中的排序操作量

8 rows processed －－返回的总行数

select h.hrc_code,h.hrc_descr,o.org_id,o.org_short_name,o.org_long_name,os.site_no,s.site_descr

from org_site_tab os,org_tab o,site_tab s,hrc_tab h where

3 os.org_id=o.org_id and o.hrc_code=h.hrc_code and os.site_no=s.site_no ;

HRC_CODE HRC_DESCR ORG_ID ORG_SHORT_NAME ORG_LONG_NAME SITE_NO SITE_DESCR

---------- -------------------- ---------- ------------------------------ ------------------------------------------------------------ ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 CEO/COO 1003 Office of CEO DataPro Inc. Office of CEO DataPro Inc. 3 Chicago

1 CEO/COO 1002 Office of CEO XYZ Inc. Office of CEO XYZ Inc. 2 Washington

1 CEO/COO 1001 Office of CEO ABC Inc. Office of CEO ABC Inc. 1 New York

2 VP 1006 Office of VP Tech ABC Inc. Office of VP Tech ABC Inc. 1 New York

2 VP 1005 Office of VP Mktg ABC Inc. Office of VP Mktg ABC Inc. 1 New York

2 VP 1005 Office of VP Mktg ABC Inc. Office of VP Mktg ABC Inc. 4 Dallas

2 VP 1005 Office of VP Mktg ABC Inc. Office of VP Mktg ABC Inc. 5 San Francisco

2 VP 1004 Office of VP Sales ABC Inc. Office of VP Sales ABC Inc. 1 New York

2 VP 1004 Office of VP Sales ABC Inc. Office of VP Sales ABC Inc. 2 Washington

2 VP 1004 Office of VP Sales ABC Inc. Office of VP Sales ABC Inc. 3 Chicago

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1764032742

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 840 | 11 (19)| 00:00:01 |

|* 1 | HASH JOIN | | 10 | 840 | 11 (19)| 00:00:01 |

|* 2 | HASH JOIN | | 10 | 720 | 8 (25)| 00:00:01 |

| 3 | MERGE JOIN | | 10 | 190 | 4 (25)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| SITE_TAB | 5 | 60 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | PK_SITE_TAB | 5 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 10 | 70 | 2 (50)| 00:00:01 |

| 7 | INDEX FULL SCAN | PK_ORG_SITE_TAB | 10 | 70 | 1 (0)| 00:00:01 |

| 8 | TABLE ACCESS FULL | ORG_TAB | 8 | 424 | 3 (0)| 00:00:01 |

| 9 | TABLE ACCESS FULL | HRC_TAB | 9 | 108 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("O"."HRC_CODE"="H"."HRC_CODE")

2 - access("OS"."ORG_ID"="O"."ORG_ID")

6 - access("OS"."SITE_NO"="S"."SITE_NO")

filter("OS"."SITE_NO"="S"."SITE_NO")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

18 consistent gets

0 physical reads

0 redo size

1341 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

Operation的方式：

全表扫描——TABLE ACCESS FULL 访问表的数据，没有访问索引

索引扫描——

A index unique scan 索引唯一扫

B INDEX FULL SCAN 索引全扫

C index range scan 索引范围扫

D index skip scan 索引跳越扫

E index fast full scan快速全索引扫描

F index join索引连接

G bitmap join位图索引连接

CBO选择访问路径：

1、CBO检查where子句中的条件以及from子句，确定哪些访问路径是可用的；

2、CBO使用这些访问路径产生一组可能的执行计划。

3、通过索引、表的统计信息评估每个计划的COST

4、最后优化器选择成本最低的一个。

第三部分分析的过程

表、索引、分区分析——收集索引、表、分区的性能数据，cbo根据这些信息决定SQL最佳的执行计划。通过对段的分析，产生一些统计信息，通过这些信息对SQL语句进行优化。

收集统计信息的方法。

方法1：传统的方法，通过命令

analyze table|index table_name|index_name compute statistics; ——全分析

analyze table|index table_name|index_name estimate statistics (sample 10 percent);－－指定比例分析

analyze table|index table_name|index_name validate structure; --分析数据块的一致性

SQL> analyze table hrc_tab compute statistics;

Table analyzed.

SQL> analyze table hrc_tab estimate statistics sample 10 percent;

Table analyzed.

SQL> analyze table hrc_tab estimate statistics; --默认比例是10％

Table analyzed.

SQL> analyze table hrc_tab estimate statistics sample 80 percent;

Table analyzed.

SQL> analyze table hrc_tab validate structure;

Table analyzed.

SQL> set autotrace on

SQL> select h.hrc_descr,o.org_short_name from org_tab o,hrc_tab h where o.hrc_code=h.hrc_code order by 2;

HRC_DESCR ORG_SHORT_NAME

-------------------- ------------------------------

CEO/COO Office of CEO ABC Inc.

CEO/COO Office of CEO DataPro Inc.

CEO/COO Office of CEO XYZ Inc.

VP Office of VP Mktg ABC Inc.

VP Office of VP Sales ABC Inc.

VP Office of VP Tech ABC Inc.

Manager update short 3

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 566430324

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 304 | 8 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 8 | 304 | 8 (25)| 00:00:01 |

|* 2 | HASH JOIN | | 8 | 304 | 7 (15)| 00:00:01 |

| 3 | TABLE ACCESS FULL| ORG_TAB | 8 | 216 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| HRC_TAB | 9 | 99 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("O"."HRC_CODE"="H"."HRC_CODE")

Statistics

----------------------------------------------------------

464 recursive calls

0 db block gets

128 consistent gets

8 physical reads

0 redo size

728 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

8 rows processed

统计信息删除

analyze table hrc_tab delete statistics;

方法2：oracle10g之后的版本推荐的，使用oracle内部包分析

1、对全库分析

SQL> exec dbms_stats.gather_database_stats(estimate_percent=>50,cascade=>true);

PL/SQL procedure successfully completed.

2、对用户下的全部对象作分析

SQL> conn plsql/plsql

Connected.

SQL> exec dbms_stats.GATHER_SCHEMA_STATS(user,estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> show user

USER is "PLSQL"

3、对表作分析

SQL> exec dbms_stats.GATHER_table_STATS(user,'hrc_tab',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

cascade含义——是否对表上的索引作分析

4、分析索引

SQL> create index ind_sht_nm on org_tab(org_short_name);

Index created.

SQL> exec dbms_stats.gather_index_stats(user,'IND_SHT_NM');

PL/SQL procedure successfully completed.

新项目上线，需要对项目用户下的所有段执行一次分析。

没有做过这个分析，但是性能数据也有，为什么？

自动创建的后台作业在夜间或者双休日，会自动去按照缺省的策略执行统计分析。

select * from dba_scheduler_jobs 查到：GATHER_STATS_JOB 计划作业

执行窗口：

1、从周一到周五的晚上22点到次日凌晨6点执行。

2、周六上午12点开始执行。中国大陆，调休的时候，就会影响性能。

首先检测统计信息缺失和陈旧的对象，然后确定优先级，再开始统计信息，作完后，如果对象的行修改数量达到10％，dbms_stats认为统计信息西是陈旧的。注意参数：

SQL> show parameter level

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL －－如果开启这个自动作业功能，这个参数必须是TYPICAL

GATHER_STATS_PROG －－封装的程序

建议在OLTP库上，关闭这个功能。

SQL> conn /as sysdba

Connected.

SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); －－参数要从select job_name from dba_scheduler_jobs得到

PL/SQL procedure successfully completed.

此时：select enable from dba_scheduler_jobs ＝ false

如果要开启的话：

DBMS_SCHEDULER.ENABLE(name IN VARCHAR2);

四、索引的结构

1、B＋树（平衡树），平衡树的遍历（索引查询的算法）

B树索引——传统的索引、最常用的索引，根据索引提供一行或者多个行的快速访问。通常只需要很少的IO就能找到正确的行。

2、需要掌握的概念：

A 根节点、分支结点、叶子结点（叶子块）

B 树的高度、树的层次（索引中平衡树的高度<＝3）根节点层次0，分支节点层次1，叶子节点层次2 max(level)＋1＝height

C 范围查找的时候涉及到双向链表结构，按照范围遍历双向链表。

D 索引的唯一性：索引叶子结点中的叶子行必须是唯一的。

情况1：如果列值是唯一的，此时叶子行不需要索引rowid

情况2：如果列值不是唯一的，此时叶子行一定需要索引rowid——此时按照列值来排序，再按照rowid升序排列

E NULL值是不会被索引的。

列值＋rowid ＝叶子行

创建索引的语法：

1、普通索引

SQL> create index ind_sht_nm on org_tab(org_short_name);

Index created. －－叶子行有rowid

2、唯一性索引

SQL> create unique index uni_obj_id on t(object_id);

Index created. －－列值必须是唯一的，叶子行没有rowid

SQL> create unique index uni_obj_typ on t(object_type);

create unique index uni_obj_typ on t(object_type)

ERROR at line 1:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

分析索引

SQL> exec dbms_stats.gather_index_statS(user,'UNI_OBJ_ID');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

索引空间

select * from dba_extents where segment_name='UNI_OBJ_ID' --段的形式存在的。

SQL> select sum(bytes)/1024/1024||'MB' from dba_extents where segment_name='UNI_OBJ_ID';

SUM(BYTES)/1024/1024||'MB'

------------------------------------------

.9375MB

SQL> select sum(bytes)/1024/1024||'MB' from dba_extents where segment_name='T';

SUM(BYTES)/1024/1024||'MB'

------------------------------------------

8MB

索引消耗的空间是极为可观的，所以索引的维护是DBA人任务的重中之重！！！

有NULL值是无法建主键索引

SQL> alter table t add constraint UNI_OBJ_ID primary key(object_id);

alter table t add constraint UNI_OBJ_ID primary key(object_id)

ERROR at line 1:

ORA-01449: column contains NULL values; cannot alter to NOT NULL －－创建物理主键的时候不能有NULL值

查询索引的结构：在一个会话中完成下面两个操作：

analyze index UNI_OBJ_ID validate structure;

select * from index_stats; －－找到平衡树的相关值。

index_stats——临时表，会话结束就清空了，所以必须在一个会话中完成。

查询索引的基本信息

select * from dba_indexes where index_name='UNI_OBJ_ID';

创建索引的时候可以指定另外的表空间，以将索引创建到其他的磁盘，分摊I／O

SQL> drop index uni_obj_id;

Index dropped.

SQL> create unique index uni_obj_id on t(object_id) tablespace mytbs1; --指定表空间

Index created.

什么时候用索引比较奏效？

场景：假设数据块的大小为8000字节，返回的数据占到整表数据的20％

1、表有100000行数据，返回20000数据，每行80个字节，1块＝100行，全表有1000块，对应的叶子行有20000个，扫索引的话，要扫20000块次，全表扫，要扫1000次，全表扫描效率高。

2、表有100000行数据，返回20000数据，每行800字节，1块＝10行，全表有10000块，对应的叶子行有20000个，扫索引的话，要扫20000块次，全表扫，要扫10000次，全表扫描效率高。

3、表有100000行数据，返回20000数据，每行1600字节，1块＝5行，全表有20000块，对应的叶子行有20000个，扫索引的话，要扫20000块次，全表扫，要扫20000块次，全表扫描效率＝索引扫描的效率

4、表有100000行数据，返回20000数据，每行8000字节，1块＝1行，全表有100000块，对应的叶子行有20000个，扫索引的话，要扫20000块次，全表扫，要扫100000块次，索引扫描的效率高

归纳：

1、不一定用索引查询效率就会提升，场景1和2反而会下降

2、索引的使用和块和行的比例有关系，1块中含的行越多，扫索引效率越低，1块中含的行越少，扫索引的效率越高。

使用索引的影响因素：

1、块中行的分布密度

2、列值的分布，和SQL的写法有关系，谓词条件对应的列值所占百分比越大，越不易用索引。

3、磁盘的配置有关系，如果表和索引在一个磁盘上的并发争用很严重，超过了磁盘的IO吞吐量。

案例：研究RBO和CBO的性能分析差别

1、创建一个案例表

SQL> conn scott/scott

Connected.

SQL> create table tx as select 1 id,object_name from dba_objects;

Table created.

SQL> update tx set id=99 where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

2、用RBO优化器生成执行计划

SQL> set autotrace traceonly exp

SQL> select /*+ rule*/ * from tx where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 40191160

----------------------------------

| Id | Operation | Name |

----------------------------------

| 0 | SELECT STATEMENT | |

|* 1 | TABLE ACCESS FULL| TX | －－全表扫描

----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=1)

Note

-----

- rule based optimizer used (consider using cbo)

SQL> create index ind_tx on tx(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'tx',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select /*+ rule*/ * from tx where id=99;

Execution Plan

----------------------------------------------------------

Plan hash value: 2614573569

----------------------------------------------

| Id | Operation | Name |

----------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TX |

|* 2 | INDEX RANGE SCAN | IND_TX |

----------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=99)

Note

-----

- rule based optimizer used (consider using cbo)

SQL> select /*+ rule*/ * from tx where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2614573569

----------------------------------------------

| Id | Operation | Name |

----------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TX |

|* 2 | INDEX RANGE SCAN | IND_TX | －－RBO会强制用索引，不管到底是否是最优的策略。

----------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=1)

Note

-----

- rule based optimizer used (consider using cbo)

3、用CBO

SQL> select * from tx where id=1; －－CBO自动选择全表扫描

Execution Plan

----------------------------------------------------------

Plan hash value: 40191160

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50929 | 1342K| 56 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TX | 50929 | 1342K| 56 (4)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=1)

CBO下强制走索引

SQL> select /*+ index(tx ind_tx) */ * from tx where id=1; －－由于列值的分布中，谓词中列的重复值较多，走索引性能更低

Execution Plan

----------------------------------------------------------

Plan hash value: 2614573569

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50929 | 1342K| 331 (1)| 00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| TX | 50929 | 1342K| 331 (1)| 00:00:04 |

|* 2 | INDEX RANGE SCAN | IND_TX | 50929 | | 102 (2)| 00:00:02 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=1)

SQL> select * from tx where id=99;

Execution Plan

----------------------------------------------------------

Plan hash value: 2614573569

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 27 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TX | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=99)

案例：B树索引的性能

SQL> create table t1(id int primary key,name varchar2(10));

Table created. －－此时主键索引的名字是系统自动分配的

SQL> create table t2(id int primary key,name varchar2(10));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into t1 values(i,'T1');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> begin

2 for i in 1..1000000 loop

3 insert into t2 values(i,'T2');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from t1 where id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3739614129

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C006685 | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=100)

SQL> select * from t2 where id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 2276453886

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C006686 | 1 | | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=100)

两次查询的代价基本一样，所以，有了索引之后，查询的损耗不会因为表的数据增加而增加。

SQL> select /*+ full(t1) */ * from t1 where id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=100)

SQL> select /*+ full(t2) */ * from t2 where id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 444 (7)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| T2 | 1 | 7 | 444 (7)| 00:00:06 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=100)

如果没有索引，两次查询的代价是差距很大的。

结论：即使数据量相差很大，当在一个唯一性约束的索引上进行条件查询的时候，消耗的资源几乎没有区别，效率是一样的。

这就是索引发挥的巨大作用。

用SQL_TRACE方式查看

SQL> conn scott/scott

Connected.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from t1 where id=100;

ID NAME

---------- ----------

100 T1

SQL> select * from t2 where id=100;

ID NAME

---------- ----------

100 T2

SQL> alter session set sql_trace=false;

Session altered.

在跟踪文件中看结果：

*** 2013-03-14 15:40:11.973

*** ACTION NAME:() 2013-03-14 15:40:11.973

*** MODULE NAME:(SQL*Plus) 2013-03-14 15:40:11.973

*** SERVICE NAME:(SYS$USERS) 2013-03-14 15:40:11.973

*** SESSION ID:(407.40) 2013-03-14 15:40:11.973

=====================

PARSING IN CURSOR #1 len=29 dep=0 uid=54 oct=3 lid=54 tim=1331295714817824 hv=2732024291 ad='60f695f4'

UID＝用户的ID号 select * from dba_users

dep表示SQL语句的深度，如果没有硬解析的话这个值为0

len表示SQL语句的长度

oct＝oracle command type

lid=私有用户的ID

tim＝时间戳（tim1-tim2）／100＝间隔的秒数

hv＝hash value select * from v$sqlarea where hash_value='2732024291'

ad＝sql的address select * from v$sqlarea

select * from t1 where id=100

END OF STMT

PARSE #1:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1331295714817819

软解析过程：

c——消耗CPU的时间

e——逃离时间——elapse time

p——物理读次数

cr——consitent read 一致性读块次数

cu——db block get——当前读块次数

mis——硬解析次数，mis其实指的是错过缓存的次数。

r——取到的行数

dep——SQL语句的深度，如果没有硬解析的话这个值为0

og——optimizer mode优化器模式，1＝CBO

tim——时间戳

EXEC #1:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1331295714818152

FETCH #1:c=0,e=58,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1331295714818250

FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1331295714818528

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=57131 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=57 us)'

STAT #1 id=2 cnt=1 pid=1 pos=1 obj=57132 op='INDEX UNIQUE SCAN SYS_C006685 (cr=2 pr=0 pw=0 time=34 us)'

id＝执行计划中的ID

cnt＝plan中的rows

pid＝parent id＝父结点

pos——执行计划中的位置，二叉树的左右位置

obj——select * from dba_objects where object_id in(57132,57131) －－对象号

op——行源的数据访问方式

=====================

PARSING IN CURSOR #2 len=29 dep=0 uid=54 oct=3 lid=54 tim=1331295722797732 hv=2555944085 ad='60f68f38'

select * from t2 where id=100

END OF STMT

PARSE #2:c=0,e=187,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1331295722797726

EXEC #2:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1331295722797820

FETCH #2:c=0,e=67,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1331295722797931

FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1331295722798177

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=57133 op='TABLE ACCESS BY INDEX ROWID T2 (cr=4 pr=0 pw=0 time=66 us)'

STAT #2 id=2 cnt=1 pid=1 pos=1 obj=57134 op='INDEX UNIQUE SCAN SYS_C006686 (cr=3 pr=0 pw=0 time=51 us)'

=====================

PARSING IN CURSOR #1 len=33 dep=0 uid=54 oct=42 lid=54 tim=1331295727929487 hv=525901419 ad='0'

alter session set sql_trace=false

END OF STMT

PARSE #1:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1331295727929483

EXEC #1:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1331295727929549

SQL> select * from t1 where id=100;

ID NAME

---------- ----------

100 T1

Execution Plan

----------------------------------------------------------

Plan hash value: 3739614129

select * from v$sqlarea where plan_hash_value='3739614129'

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C006685 | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=100)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

462 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

将跟踪文件转换成tkprof文件

[oracle@oracle253 ~]$ tkprof /u01/oracle/admin/ORCL/udump/orcl_ora_7544.trc /u01/oracle/admin/ORCL/udump/tkprof7544.txt

TKPROF: Release 10.2.0.4.0 - Production on Thu Mar 14 16:29:44 2013

Trace file: /u01/oracle/admin/ORCL/udump/orcl_ora_7544.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

select *

from

t1 where id=100

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 0 3 0 1

cpu和elapsed时间不是为0，而是在微秒单位，在表中被忽略了。

注意：对于多线程处理器，elapsed time可能比cpu时间或者wait时间大，也可能小

对于单线程处理器，elapsed time ＝ wait time ＋ cpu time

query＝一一致性读

currrent＝当前读

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 54

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=57 us)

1 INDEX UNIQUE SCAN SYS_C006685 (cr=2 pr=0 pw=0 time=34 us)(object id 57132)

********************************************************************************

select *

from

t2 where id=100

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 4 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 0 4 0 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 54

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS BY INDEX ROWID T2 (cr=4 pr=0 pw=0 time=66 us)

1 INDEX UNIQUE SCAN SYS_C006686 (cr=3 pr=0 pw=0 time=51 us)(object id 57134)

********************************************************************************

alter session set sql_trace=false

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

Parsing user id: 54

汇总结果

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS －－非递归调用执行的语句

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.00 0.00 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 4 0.00 0.00 0 7 0 2

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 10 0.00 0.00 0 7 0 2

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS －－递归调用执行的语句

由用户SQL衍生出来的其他的SQL

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0

Execute 0 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

3 user SQL statements in session.

0 internal SQL statements in session.

3 SQL statements in session.

********************************************************************************

Trace file: /u01/oracle/admin/ORCL/udump/orcl_ora_7544.trc

Trace file compatibility: 10.01.00

Sort options: default

1 session in tracefile.

3 user SQL statements in trace file.

0 internal SQL statements in trace file.

3 SQL statements in trace file.

3 unique SQL statements in trace file.

45 lines in trace file.

13 elapsed seconds in trace file.

清空缓存后再做上面的过程

SQL> conn scott/scott

Connected.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from t1 where id=100;

ID NAME

---------- ----------

100 T1

SQL> select * from t2 where id=100;

ID NAME

---------- ----------

100 T2

SQL> alter session set sql_trace=false;

Session altered.

[oracle@oracle253 ~]$ tkprof /u01/oracle/admin/ORCL/udump/orcl_ora_8310.trc /u01/oracle/admin/ORCL/udump/tkprof78310.txt

TKPROF: Release 10.2.0.4.0 - Production on Thu Mar 14 16:36:56 2013

********************************************************************************

select *

from

t1 where id=100

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 16 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 16 3 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=16 pw=0 time=551 us)

1 INDEX UNIQUE SCAN SYS_C006685 (cr=2 pr=8 pw=0 time=387 us)(object id 57132)

********************************************************************************

select *

from

t2 where id=100

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 24 4 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 24 4 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS BY INDEX ROWID T2 (cr=4 pr=24 pw=0 time=719 us)

1 INDEX UNIQUE SCAN SYS_C006686 (cr=3 pr=16 pw=0 time=529 us)(object id 57134)

********************************************************************************

alter session set sql_trace=false

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

Parsing user id: 54

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.01 0.01 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 4 0.00 0.00 40 7 0 2

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 10 0.01 0.01 40 7 0 2

Misses in library cache during parse: 2

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS －－递归调用的损耗非常大！

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 30 0.00 0.00 0 0 0 0

Execute 32 0.01 0.01 0 0 0 0

Fetch 44 0.00 0.01 15 102 0 28

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 106 0.03 0.03 15 102 0 28

Misses in library cache during parse: 15

Misses in library cache during execute: 15

3 user SQL statements in session.

32 internal SQL statements in session.

35 SQL statements in session.

********************************************************************************

Trace file: /u01/oracle/admin/ORCL/udump/orcl_ora_8310.trc

Trace file compatibility: 10.01.00

Sort options: default

1 session in tracefile.

3 user SQL statements in trace file.

32 internal SQL statements in trace file.

35 SQL statements in trace file.

18 unique SQL statements in trace file.

375 lines in trace file.

6 elapsed seconds in trace file.

B树索引适用的场合，键值重复率低的字段比较适合用B树索引

SQL> create table t3 as select object_id,object_name from dba_objects;

Table created.

SQL> create table t4 as select mod(object_id,2) object_id,object_name from dba_objects;

Table created.

创建了两个表，T3表的object_id字段键值重复率很低，T4表的键值重复率很高

SQL> create index t3_ind on t3(object_id);

Index created.

SQL> create index t4_ind on t4(object_id);

Index created.

SQL> select count(distinct object_id) from t3;

COUNT(DISTINCTOBJECT_ID)

------------------------

50933

SQL> select count(distinct object_id) from t4;

COUNT(DISTINCTOBJECT_ID)

------------------------

统计分析

SQL> exec dbms_stats.gather_table_stats(user,'t3',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t4',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp stat

SQL> set linesize 10000

SQL> select * from t3 where object_id=1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2495931480

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T3_IND | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

339 bytes sent via SQL*Net to client

389 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> select * from t4 where object_id=1;

25337 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2560505625

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 25468 | 646K| 55 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T4 | 25468 | 646K| 55 (4)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1911 consistent gets

0 physical reads

0 redo size

775705 bytes sent via SQL*Net to client

18979 bytes received via SQL*Net from client

1691 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

25337 rows processed

SQL> select /*+ index(t4 t4_ind) */ * from t4 where object_id=1;

25337 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1930756411

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 25468 | 646K| 275 (1)| 00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 25468 | 646K| 275 (1)| 00:00:04 |

|* 2 | INDEX RANGE SCAN | T4_IND | 25468 | | 50 (2)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3642 consistent gets

0 physical reads

0 redo size

775705 bytes sent via SQL*Net to client

18979 bytes received via SQL*Net from client

1691 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

25337 rows processed

oracle先扫描索引，找到符合条件的键值（"OBJECT_ID"=1），然后根据该键值在表中的位置（ROWID），然后根据ROWID到表中去找数据，如果满足条件的键值很多，oracle先访问索引段，再访问表段，如此循环。

SQL> select object_id from t3 where object_id=1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2377336880

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| T3_IND | 1 | 5 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

278 bytes sent via SQL*Net to client

389 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> select object_id from t4 where object_id=1;

25337 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3986136094

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 25468 | 76404 | 23 (5)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| T4_IND | 25468 | 76404 | 23 (5)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1790 consistent gets

0 physical reads

0 redo size

341626 bytes sent via SQL*Net to client

18979 bytes received via SQL*Net from client

1691 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

25337 rows processed

只取其中的索引列数据，因为索引的叶子行上有要查询列值的数据，所以不需要访问表，即可获得要求的数据。

SQL> select /*+ full(t4) */ object_id from t4 where object_id=1;

25337 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2560505625

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 25468 | 76404 | 55 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T4 | 25468 | 76404 | 55 (4)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1911 consistent gets

0 physical reads

0 redo size

341626 bytes sent via SQL*Net to client

18979 bytes received via SQL*Net from client

1691 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

25337 rows processed

只查索引的时候，即使键值重复率很低，全扫索引效率也是比较高的。

2、位图索引

当设计一个存放海量数据的数据库时，需要注意的是，几乎很少用主键。表都是事实表，存放事实数据为目的，在这些大量的数据中，重复是不可避免的，考虑使用位图索引。

位图索引（bitmap index）从oracle7开始就有，用来解决B树索引在查询的时候遇到的一些问题，重复数据太多导致的查询性能低下，在每一个位图索引的索引键值中，都会保存指向多行的指针，这样，位图索引的键值就会比B树索引少很多

创建位图索引的语法：

select * from emp 上创建job列的位图索引

SQL> create bitmap index emp_bit_ind on emp(job);

Index created.

posted @ 2013-07-03 12:02 muynella 阅读(450) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

muynella

备份与恢复

公告