月份：2014年1月

朋友的应用程序在年度结转时调用存储过程时hang住了.经过调试存储过程发现执行到下面的语句时被hang住.

UPDATE t_config_info
       SET last_do_time = systimestamp
     WHERE config_id = config_record.config_id;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE error1;
    END IF;

解决这个问题的方法就是找到执行存储过程的会话,并用oradebug来dump进程信息.先执行下面的语句来找到执行存储过程会话对应的spid.

SQL>select p.spid
from v$session s,v$process p,v$sqlarea c
where s.username is not null and s.PADDR=p.ADDR and s.sql_id=c.sql_id
and s.sql_fulltext like'%UPDATE t_config_info%'
SPID
----------
14483524

得到的spid为14483524

在另一个会话中执行下面的语句

SQL> oradebug setospid 14483524
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hygeia/hygeia1/trace/hygeia2_ora_14483524.trc

从得到的跟踪文件中可以看到以下信息:

 SO: 0x700000758606100, type: 4, owner: 0x70000075c4e73e0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x70000075c4e73e0, name=session, file=ksu.h LINE:11467 ID:, pg=0
    (session) sid: 539 ser: 14973 trans: 0x700000753a47aa8, creator: 0x70000075c4e73e0
              flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40008) -/-
              DID: , short-term DID:
              txn branch: 0x0
              oct: 6, prv: 0, sql: 0x7000005cced65d0, psql: 0x70000076ed6dc58, user: 123/ZWJH_MM
    ksuxds FALSE at location: 0
    service name: hygeia
    client details:
      O/S info: user: Administrator, term: LENOVO-JGXROLVS, ospid: 6608:6612
      machine: WORKGROUP\LENOVO-JGXROLVS program: plsqldev.exe
      application name: PL/SQL Developer, hash value=1190136663
      action name: SQL Window - New, hash value=3399691616
    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn< <16 | slot=0x15000b, sequence=0x362616
        wait_id=811 seq_num=812 snap_id=1
        wait times: snap=29.036136 sec, exc=29.036136 sec, total=29.036136 sec
        wait times: max=infinite, heur=29.036136 sec
        wait counts: calls=59 os=59
        in_wait=1 iflags=0x15a0
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 625, ser: 53645
      Dumping final blocker:
        inst: 1, sid: 625, ser: 53645

上面的inst: 1, sid: 625, ser: 53645可以知道造成阻塞的会话是1号实例中的会话sid,serial#为625,53645,被人为的kill掉了,然后后继多次执行这个存储过程当hang住后又kill掉了,找到阻塞的会话也知道问题产生的原因了问题也就解决了.

直方图是一种按数据出现的频率来进行分类存储的方法.在oracle中直方图是用来描述表中列数据的分布情况.每一个sql在被执行前都要经过优化这一步骤那么在优化器给出一个最优执行计划之优化器应该要知道sql语句中所引用的底层对象的详细信息.

直方图描述的对象包括列中不同值的数量和它们出现的频率.现在存储每一个不同值和它出现的频率是不可行的,特别是对于大表来说列中有上万个不同值,oracle使用直方图来存储关于列中数据分布的有用信息而且oracle的CBO使用直方图信息来计算出一个最优的执行计划.

CBO与直方图histograms
从一个行源中评估返回行数所占的比例这就是选择率,选择率在CBO的查询优化中起着重要作用.选择率的取值范围是0到1之间.粗略的讲,如果满足谓词条件的只有少量的行记录那么CBO将更喜欢使用索引扫描,如果谓词条件要从表中获取大量数据那么CBO将更喜欢使用全表扫描.比如下面的查询获取deptno等于10的所有雇员信息如果返回少量的记录查询将会更倾向于使用索引扫描:
select * from emp where deptno=10;

为了评估选择率(或者换句话说计算出最优执行计划),CBO会使用各种形式的统计信息,配置参数等.以表中列的角度来说,CBO会收集以下统计信息:
列中不同值的数量也就是NDV
列中的最小值/最大值
列中null值的数量
数据分布或直方图信息

在没有直方图时优化器使用基表中记录的列中不同值的数量,列中最小值/最大值和列中null值的数量来计算统计信息.使用这些信息优化器假设数据在列中的最小值和最大值之间是均匀分布的或者说列中每一个不同值的出现次数是相同的.
下面举列说明.创建一个测试表t1它有10000行记录,有两个列,列all_distinct包含不同值的范围从1到10000.列skew对于前10行记录的值从1到10,余下的9990行记录都是10000.

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000;

Table created.

SQL> update t1 set skew=all_distinct where rownum< =10;

10 rows updated.

SQL> commit;

Commit complete.

SQL> select skew,count(*) from t1 group by skew order by skew;

      SKEW   COUNT(*)
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
     10000       9990

11 rows selected.

使用dbms_stata.gather_table_stats来收集统计信息是生成直方图是由参数method_opt来控制的method_opt参数的语法是由多个部分组成的.前两个部分是强制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column…]

method_opt语法中的主要部分控制哪此列将收集列的统计信息(min,max,ndv,nulls).缺省是for all columns,它将会对表中所有的列(包括隐藏列)收集基本的列统计信息.

for all indexed columns将只对哪些包含索引的列进收集列统计信息.

for all hidden columns将只会对哪些虚拟列收集列统计信息.这意味着在对表收集统计时真实列是不会生成列统计信息的.这个值不能用于通常的统计信息收集.它只能用在当基表列的统计信息精确收集后在表中创建新的虚拟列.然后对新的虚拟列收集列统计信息时才使用它.

注意如果列不在统计信息收集列表中那么只会收集列的平均长度.

size用来指定直方图的桶数SIZE {integer | REPEAT | AUTO | SKEWONLY}
auto:基于列的使用信息(sys.col_usage$)和是否存在数据倾斜来收集直方图
integer:人为的指定创建直方图的桶数范围是1到254,如果size 1意味着不创建直方图
repeat:只会对已经存在直方图的列重新生成直方图.如果是一个分区表,repeat会确保对在全局级别存在直方图的列重新生成直方图.这是不被推荐的设置的.当前直方图的桶数将会作为重新生成直方图所使用的桶数的最大值.比如,当前直方图的桶数是5,那么生成的直方图最大桶数就是5,说的直白点就是刷新现有直方图的列上的统计信息.
skewonly:对任何数据分布出现倾斜列的自动创建直方图

现在来对表t1收集统计信息但不创建直方图

SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,density from user_tab_col_statistics where
  2  table_name='T1';

COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------ ----------
ALL_DISTINCT                          10000      .0001
SKEW                                     11 .090909091

如果没有直方图,列的density统计信息代表了它的选择率它是通过去时1/num_distinct=1/11=0.09090901来计算出来的.在有直方图的情况下,density的计算依赖于直方图的类型和oracle的版本.density值的范围是0到1之间.当查询使用这个列作谓词条件时优化器将会使用这个列的density统计信息来评估将要返回的行数.所以 cardinality(基数)=selectivity(选择率)* number of rows(表的行数)

下面来检查一下在谓词条件中列的数据分布存在倾斜而没有直方图的情况下其基数评估的情况:

SQL> explain plan for select * from t1 where skew=1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   909 |  6363 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   909 |  6363 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("SKEW"=1)


SQL>  explain plan for select * from t1 where skew=10000;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   909 |  6363 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   909 |  6363 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("SKEW"=10000)

因为oracle假设列skew中的数据是均匀分布的所以基数评估cardinality=density*num_rows=0.09090901*10000=909.09,四舍五入就是909行.但是我们知道skew=1的记录只有1行而skew=10000的记录有9990行.这种假设必然导致错误的执行计划.例如,如果我们在列skew上创建一个B树索引,oracle将使用对谓词skew=10000行使用索引扫描并返回909行记录.

SQL> create index skew_idx on t1(skew);

Index created.

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

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where skew=10000;

Explained.

SQL> select  * from table(dbms_xplan.display);
Plan hash value: 3994350891
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   909 |  6363 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |   909 |  6363 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SKEW_IDX |   909 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SKEW"=10000)

因为我们知道没有给出关于数据分布的额外信息,CBO假设列中的数据在最小值和最大值之间是均匀分布的所以选择了错误的执行计划.

oracle直方图
一旦对列创建直方图后,它将告诉CBO列中数据出现的频率.所以在上面的例子中如果对列skew创建直方图它将告诉优化顺skew=1的值只出现一次,skew=10000的值出现了9990次.因此它能让优化器选择最优的执行计划.

在oracle中有两种类型的直方图.第一种是oracle会选择存储列中每一个不同值以及其出现的频率,称这种为宽度平衡直方图或频率直方图.这对于列有少量的不同值来说是有效和可能的方式.然而当列有大量不同值时要存储每一个不同值以及其出现的频率是不可能的.当然在无限资源(存储空间和计算能力和解析时间)的情况下,可以在任何情况下对每一个不同值存储其出现的频率来对优化器提供最终的信息,但是在真实的环境中这是不可能的.所以oracle使用高度平衡直方图来存储这样的数据.oracle会根据列中不同值的数量来自动判断所要创建直方图的类型,不同类型的直方图所描述的信息是不同的.

频率直方图(frequence histograms)
频率直方图列中的不同值被划到相同数量的桶中.每一个桶中存储的都是相同的值,也就是说频率直方图的桶数等于列的不同值的个数.buckets=ndv

下面的图表代表了列skew的数据分布情况.从图表中可以看出以下信息:
在x轴有11个桶,每一个桶代表了一个不同的值
Y轴显示了每一个不同值出现的频率.skew的1到10的频率是1,值10000的频率是9990
通过查看这样的信息可以很容易的说出一个特定值出现的频率

下面来对列skew创建一个频率直方图并查看数据是怎样存储在数据字典视图中的.现在对参数method_opt使用’for column column_name size n’来创建指定桶数的直方图.

SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns skew size 11');

PL/SQL procedure successfully completed.


SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where
  2    table_name='T1' and column_name='SKEW';

COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
SKEW                       1              1
SKEW                       2              2
SKEW                       3              3
SKEW                       4              4
SKEW                       5              5
SKEW                       6              6
SKEW                       7              7
SKEW                       8              8
SKEW                       9              9
SKEW                      10             10
SKEW                   10000          10000

第一个语句对列skew创建了有11个桶的直方图,因为我们知道列skew有11个不同的值.第二个语句显示了存储在数据字典视图中的直方图数据.直方图中存储的信息依赖于直方图的桶数小于列不同值的个数或者相等会有不同的解释,也就是说直方图中存储的信息依赖于直方图的类型会有不同的解释.下面解释频率直方图所代表的信息.

Endpoint_value显示的是真实的列值,endpoint_number显示的是累积的行数或者是累积的频率.为了计算一个特定列值的频率需使用与它相关的endpoint_number值减去它之前的累积值.
例如,对于endpoint_value为5的值,它的endpoint_number为5,之前的endpoint_number为4,因上skew=5的记录只有5-4=1行.类似的对于endpoint_value为10000的值它的endpoint_number为10000它之前的endpoint_number为10,所以skew=10000的记录有10000=10=9990行.

使用下面的sql来解释说明存储在数据字典中的直方图信息:

SQL> select endpoint_value as column_value,
  2  endpoint_number as cummulative_frequency,
  3  endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency
  4  from user_tab_histograms
  5  where table_name ='T1' and column_name='SKEW';

COLUMN_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY
------------ --------------------- ----------
           1                     1          1
           2                     2          1
           3                     3          1
           4                     4          1
           5                     5          1
           6                     6          1
           7                     7          1
           8                     8          1
           9                     9          1
          10                    10          1
       10000                 10000       9990

存储总的或累积频率来代替单个频率在范围扫描时是特别有用的对于象where skew< =10这样的谓词基数就现成的. 现在因为我们对更skew创建了直方图再来查看之前的查询有什么不同:

SQL> select column_name,num_distinct,density,histogram from user_tab_col_statistics where table_name=’T1′;

COLUMN_NAME                    NUM_DISTINCT    DENSITY HISTOGRAM
—————————— ———— ———- —————
ALL_DISTINCT                          10000      .0001 NONE
SKEW                                     11     .00005 FREQUENCY


SQL> explain plan for select * from t1 where skew=10000;

Explained.

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

PLAN_TABLE_OUTPUT
—————————————————————————
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  9990 | 69930 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  9990 | 69930 |     7  (15)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“SKEW”=10000)

13 rows selected.



SQL> explain plan for select * from t1 where skew=1;

Explained.

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

PLAN_TABLE_OUTPUT
—————————————————————————————-
Plan hash value: 3994350891

—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SKEW_IDX |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   2 – access(“SKEW”=1)

14 rows selected.

现在优化器对于谓词skew=10000选择了全表扫描且能精确计算出它的基数9990.注意现在skew列的density是变成了0.00005也就是1/(2*num_rows)或者0.5/num_rows.

高度平衡直方图(height-balanced histograms)
在频率直方图中oracle给每一个不同值分配一个桶,然而桶的最大个数是254,因此如果表中的列有大量的不同值(超过254),将会创建一个高度平衡的直方图.

在高度平衡直方图中,因为我们的不同值超过了桶的个数,因此oracle首先分对列数据进行排序然后将数据集按桶数进行分类且除了最后一桶可能包含的数据比其它的桶少以外,所有其它的桶包含相同数量的值(这就是为什么叫高度平等直方图的原因).

这是有一个单独的语句用来创建高度平衡直方图.当请求的桶数少于列中不同值的个数时,oracle就会创建一个高度平衡直方图且这意味着endpoint_value和endpoint_number是不相同的.为了解释这种类型直方图的信息先看一个列有23个值且有9个不同值的例子.假设我们指定直方图的桶数是5,下面的图表显示了这些数据是如何存储在直方图中的:
 
基于上面的图表可以得出以下信息:
直方图的桶数比列中的不同值的个数小
因为我们指定了直方图的桶数是5,所以整个数据集除了最后一个桶(在这里只有3个值)其它按相同的大小分配到每一个桶中.
每一个桶中的endpoints和第一个桶中的first point被标记因为它们有特殊意义.
数据3被标记为红色,它是一种特殊情况它的endpoint出现在多个桶中.
下面的图表是直方图的另一种显示方式:
 

使用5个桶且列有23个值这意味着除了最后一个桶只有3个值以外其它每一个桶都有5个值.实际上这是oracle在数据字典视图中存储高度平衡直方图信息的方式.因为bucket 1和2都使用3作为一个endpoint,oracle为了节省空间将不会存储bucket 1.所以当桶被合并时只会存储单个条目.
 

下面我们来对列skew创建一个高度平衡直方图,这一次让桶数小于列的不同值的个数11:
SQL> select  column_name,endpoint_number,endpoint_value from
  2    user_tab_histograms where table_name='T1' and column_name='SKEW';

COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------  --------------
SKEW                         0               1
SKEW                         5           10000

这里buckets 1到5都是用10000作为它的endpoint所以bucket 1到4为了节省空间没有被存储.下面的查询能用来显示桶数和它的endpoinit值
SQL> SELECT bucket_number, max(skew) AS endpoint_value
  2   FROM (
  3   SELECT skew, ntile(5) OVER (ORDER BY skew) AS bucket_number
  4   FROM t1)
  5   GROUP BY bucket_number
  6   ORDER BY bucket_number;

BUCKET_NUMBER ENDPOINT_VALUE
------------- --------------
            1          10000
            2          10000
            3          10000
            4          10000
            5          10000

这里ntile(5)是一个分析函数,它将一个有序的数据集划分到5个桶中.
所以简而言之,在高度平衡直方图中,数据被划分到不同的桶中除了最后一个桶每一个桶包含相同的数据.每一个桶中的最大值被记录为endpoint_value而第一个桶中的最小值也被记录(bucket 0).endpoint_number代表桶数.一旦数据被记录到桶中将会识别为2种类型的数据:

Non popular values和popular values.
Popular values是哪些作为endpoint value出现多次的值.例如在前面的例子中3是一个popular值,在上面的例子中skew 10000是一个popular value.non popular value是哪些没有作为endpoint values出现或者只作为endpoint values出现一次的值.popular value和non popular value不是固定的它依赖于直方图桶的大小,改变桶的大小会出现不同的popular值.
小结:

列中不同值的个数小于直方图的桶数:当不同值的个数小于桶数时,endpoint_value列包含的是不同值本身,endpoint_number列包含是小于列值的累积行数.(频率直方图)
列中不同值的个数大于直方图的桶数:当不同值的个数大于桶数时,endpoint_number包含的是bucekt id且endpoint_value是显示的每一个桶中的最大值.bucket 0是一个特殊它显示的是列中的最小值(高度平衡直方图).
		

选择率selectivity是Oracle的CBO中比较重要的一个概念。和selectivity经常一起提到的一个概念是cardinality，其实这两个概念的本质上是一样的。selectivity是指一个SQL操作的得出结果集占原来结果集的百分比，而cardinality就是指一个SQL操作的得出结果集的行数，CBO是通过selectivity来计算cardinality的，也就是说cardinality=selectivity*原结果集行数。
创建一个测试表

create table t1 as
select
trunc(dbms_random.value(1,13)) month_no
from all_objects
where rownum< =1200;


sys@JINGYONG> select high_value,low_value,num_distinct,num_nulls,density
  2  from dba_tab_col_statistics where owner='SYS' and table_name='t1'
  3  and column_name='MONTH_NO';

HIGH_VALUE  LOW_VALUE    NUM_DISTINCT  NUM_NULLS    DENSITY
C10D        C102         12            0            .083333333

sys@JINGYONG> select blocks,num_rows from user_tables where table_name='t1
';

    BLOCKS   NUM_ROWS
---------- ----------
         2       1200

sys@JINGYONG> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     40

sys@JINGYONG> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

选择率=所需的空间 除以 总可用空间
通过计算(user_tab_col_statistics.high_value – user_tab_col_statistics.low_value)可以得出,上面的例子中整个区间大小为11,当计算得到的是11,那么我们就知道其中肯定有错误.因为我们知道,上例中有12个离散的月份,但优化器采用的算法却将这些数据看作连续的变化数据,并且总区间大小为11.
单列谓词的选择率
示例1
select count(*) from t1 where month_no>8

sys@JINGYONG> select count(*) from t1 where month_no>8;

  COUNT(*)
----------
       396

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6u62fruy6276s, child number 0
-------------------------------------
select count(*) from t1 where month_no>8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL|       T1 |   436 |  1308 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO">8)

当where month_no>8:这是一个无边界(一侧没有边界)的开(不包含8)区间.
选择率=(high_value-limit)/(high_value-low_value)=(12-8)/(12-1)=4/11
基数=1200*4/11=436.363636

成本是2
全表扫描的成本计算公式:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
where
#SRDs – number of single block reads
#MRDs – number of multi block reads
#CPUCycles – number of CPU Cycles
sreadtim – single block read time
mreadtim – multi block read time
cpuspeed – CPU cycles per second

成本指的是花费在块读取上的时间,加上花在多块读取上的时间,再加上所需的cpu处理的时间,然后将总和除以单块读取所花费的 时间,也就是说,成本是语句的预计执行时间的总和,以单块读取时间单元的形式来表示.
如果oracle收集了操作系统统计信息，那么CBO采用工作量统计模式计算代价
如果oracle没有收集操作系统统计信息，那么CBO采用非工作量统计模式计算代价我们现在处于“非工作量统计模式”

sys@JINGYONG> select pname,pval1 from aux_stats$;

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
CPUSPEEDNW                       1149.062
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

#SRds=0,因为是全表扫描，单块读为0，全都使用的是多块读
#MRds=表的块数/多块读参数=2/40=0.05
mreadtim=ioseektim+(db_file_multiblock_count*db_block_size/iotftspeed)=10+(40*8192/4096)=90
sreadtim=ioseektim+(db_block_size/iotfrspeed)=10+(8192/4096)=12
CPUCycles 等于 PLAN_TABLE里面的CPU_COST

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>8;

已解释。

sys@JINGYONG> select cpu_cost from plan_table;

  CPU_COST
----------
    254243

cpuspeed 等于 CPUSPEEDNW= 1149.062
COST=(0*12/12)+(0.05*90/12)+(254243/1149.062/12/1000)(毫秒换算成秒)=
0+0.375+0.01843=0.39343
0.375是IO成本
0.01843是CPU成本
手工计算出来的COST取最接近的整数等于1和我们看到的2有差别
这是由于隐含参数_table_scan_cost_plus_one参数造成的

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y
  2    WHERE x.inst_id = USERENV ('Instance')
  3     AND y.inst_id = USERENV ('Instance')
  4     AND x.indx = y.indx
  5     AND x.ksppinm LIKE '%_table_scan_cost_plus_one%';

NAME                                                                             VALUE                                                                              DESCRIB
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
_table_scan_cost_plus_one                                                        TRUE                                                                             bump estimated full table scan and index ffs cost by one

根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1 即 1+1=2;

示例2
select count(*) from t1 where month_no>=8
这是一个无边界的闭(包含8)区间,因此需要对算法进行调整.具体调整过程为必须包括闭合值(此处即为8)所在的行–也就是要增 加一个1/num_distinct(要注意的是oracle8i使用的是density而不是1/num_distinct,但是,如果没有获取统计信息或者没有直方 图可用,那么无须注意这一差别)
选择率=(high_value-limit)/(high_value-low_value)+1/num_distinct=4/11+1/12
基数=1200*(4/11+1/12)=536.363636

sys@JINGYONG> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     40

sys@JINGYONG> select count(*) from t1 where month_no>=8;

  COUNT(*)
----------
       497

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g748ttx5rv2p1, child number 0
-------------------------------------
select count(*) from t1 where month_no>=8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   536 |  1608 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO">=8)

示例3
select count(*) from t1 where month_no<8
选择率=(limit-low_value)/(high_value-low_value)=(8-1)/(12-1)=7/11
基数=1200*7/11=763.636364

sys@JINGYONG> select count(*) from t1 where month_no<8;

  COUNT(*)
----------
       703

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
SQL_ID  cpvw8yxstbtng, child number 0
-------------------------------------
select count(*) from t1 where month_no<8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   764 |  2292 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO"<8)

示例4
select count(*) from t1 where month_no< =8 选择率=(limit-low_value)/(high_value-low_value)+1/num_distinct=(8-1)/(12-1)+1/12 基数=1200*(7/11+1/12)=863.636364

sys@JINGYONG> select count(*) from t1 where month_no< =8;

  COUNT(*)
----------
       804

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
——————————————————————————–

SQL_ID  buhw0y52jy3nr, child number 0
————————————-
select count(*) from t1 where month_no< =8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   864 |  2592 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO"<=8)

示例5
select count(*) from t1 where month_no between 6 and 9
两边都是有界的闭区间它其实与
select count(*) from t1 where month_no>=6 and month_no< =9是等价的.它们给出了两个闭合值.因此要对算法进行两 次调整. 选择率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct+1/num_distinct=(9-6)/(12-1)+1/12+1/12 基数=1200*(3/11+1/6)=527.272727

sys@JINGYONG> select count(*) from t1 where month_no between 6 and 9;

  COUNT(*)
———-
       421

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID  83ud81y133kxm, child number 0
————————————-
select count(*) from t1 where month_no between 6 and 9

Plan hash value: 3337892515

——————————————————————————-
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   527 |  1581 |     2   (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————



sys@JINGYONG> select count(*) from t1 where month_no>=6 and month_no< =9;

  COUNT(*)
----------
       421

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID  74gjsbjjdv97k, child number 0
————————————-
select count(*) from t1 where month_no>=6 and month_no< =9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   527 |  1581 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">=6 AND “MONTH_NO”< =9))

示例7
select count(*) from t1 where month_no>=6 and month_no<9
选择率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct=(9-6)/(12-1)+1/12
基数=1200*(3/11+1/12)=427.272727

sys@JINGYONG> select count(*) from t1 where month_no>=6 and month_no<9;

  COUNT(*)
----------
       328

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  5w0n5237vpngb, child number 0
-------------------------------------
select count(*) from t1 where month_no>=6 and month_no<9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   427 |  1281 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">=6 AND "MONTH_NO"<9))

示例8
select count(*) from t1 where month_no>6 and month_no< =9 选择率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct=(9-6)/(12-1)+1/12 基数=1200*(3/11+1/12)=427.272727

sys@JINGYONG> select count(*) from t1 where month_no>6 and month_no< =9;

  COUNT(*)
----------
       303

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
——————————————————————————–

SQL_ID  bbd2wxjratndg, child number 0
————————————-
select count(*) from t1 where month_no>6 and month_no< =9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   427 |  1281 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">6 AND “MONTH_NO”< =9))

示例9
select count(*) from t1 where month_no>6 and month_no<9
选择率=(high_limit-low_limit)/(high_value-low_value)=(9-6)/(12-1)
基数=1200*(3/11)=327.272727

sys@JINGYONG> select count(*) from t1 where month_no>6 and month_no<9;

  COUNT(*)
----------
       210

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  6axnk33swbtpw, child number 0
-------------------------------------
select count(*) from t1 where month_no>6 and month_no<9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   327 |   981 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">6 AND "MONTH_NO"<9))

对于使用绑定变量的谓词优化器就无法知道任何变量的值也不知道绑定变量的类型,因此,这种情况下优化器
就会使用一个固定的选择率来产生执行计划.例如:

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>:b1;


已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO">TO_NUMBER(:B1))



sys@JINGYONG> explain plan for select count(*) from t1 where month_no between :b1 and :b2;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:B1)< =TO_NUMBER(:B2))
   3 - filter("MONTH_NO">=TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))



sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO">=TO_NUMBER(:B1))

已选择14行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no<:b1 ;


已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO" explain plan for select count(*) from t1 where month_no< =:b1
;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO"< =TO_NUMBER(:B1))

已选择14行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
 and month_no< =:b2;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:B1)< =TO_NUMBER(:B2))
   3 - filter("MONTH_NO">=TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))

已选择16行。



sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
 and month_no<:b2 ;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1) AND "MONTH_NO" explain plan for select count(*) from t1 where month_no>:b1
and month_no<:b2 ;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:B1)TO_NUMBER(:B1) AND "MONTH_NO" explain plan for select count(*) from t1 where month_no>:b1
and month_no< =:b2;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:B1)TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))

对于上面所有使用绑定变量的查询其评估的基数都是101使用固定选择率

当查询超出列的最低/最高界限测试的结果如下.其评估的基数是100使用固定选择率

sys@JINGYONG> select count(*) from t1 where month_no between 14 and 17;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8baayn49yujkh, child number 0
-------------------------------------
select count(*) from t1 where month_no between 14 and 17

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   100 |   300 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">=14 AND "MONTH_NO"< =17))


已选择19行。

sys@JINGYONG> select count(*) from t1 where month_no between 18 and 21;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9agzhp783caa1, child number 0
-------------------------------------
select count(*) from t1 where month_no between 18 and 21

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   100 |   300 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">=18 AND "MONTH_NO"< =21))


已选择19行。

sys@JINGYONG> select count(*) from t1 where month_no between 24 and 27;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dksv39k0vx5sh, child number 0
-------------------------------------
select count(*) from t1 where month_no between 24 and 27

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   100 |   300 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO">=24 AND "MONTH_NO"< =27))


已选择19行。

sys@JINGYONG> select count(*) from t1 where month_no between -11 and 0;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  drdqd21q24hzy, child number 0
-------------------------------------
select count(*) from t1 where month_no between -11 and 0

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   100 |   300 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_NO"< =0 AND "MONTH_NO">=(-11)))


已选择19行。

双谓词选择率
为了能够计算通用的联合谓词选择率,需要用到以下3种基本公式,它们是以选择率的形式给出的,而不是以基数的形式给出的.
(谓词1 and 谓词2)的选择率=谓词1的选择率+谓词2的选择率
(谓词1 or 谓词2)的选择率=谓词1的选择率+谓词2的选择率-(谓词1 and 谓词2)的选择率
(not 谓词1)的选择率=1-谓词1的选择率,绑定变量不能使用这个公式进行计算

下面是一组概率论方面的公式
p(a and b)=p(a)*p(b)
p(a or b)=p(a)+p(b)-p(a and b)=p(a)+p(b)-p(a)*p(b)
p(not a)=1-p(a)
上面的联合谓词选择率与概率论公式是等价的.

首先,我们来细致研究如下的where子句:
select count(*) from T1 where month_no>8 or month_no< =8; 利用简单的选择率计算公式required range/total range可以得出谓词1的选择率为(12-8)/(12-1)=4/11=0.363636 同样的.谓词2的选择率为(8-1)/(12-1)+1/12=7/11+1/12=0.719696 (p1 or p2)的选择率计算公式为p1的选择率+p2的选择率-(p1 and p2)的选择率,因此,联合的选择率为 0.363636+0.719696-(0.363636*0.719696)=0.8216 将这个选择率乘以1200行,进行四舍五入后,得到结果后为986,很明显,这个结果和人的直观思维得到的结果不一样. 当我们将多个谓词应用到某个表时,需要确定测试的列之间是否存在依赖关系.如果存在依赖关系,优化器得到的选择率将是错误 的,基数也就是错误的,因此也就无法得到合适的执行计划. 小结: 为了估计一组谓词返回数据的行数,优化器首先计算选择率(返回数据行数的分数),然后将其与输入行数相乘.对于单个列上的单个谓词，优化器将利用不同值的数目或density作为计算谓词选择率的基础.对于单个列上基于的区间谓词,优 化器采用分数required range/total available range进行一些端点值调整的方式来计算谓词选择率.对于包含绑定变量的区间谓词来说,优化器使用硬编码常量作为选择率优化器通过使用类似于计算独立事件的联合概率方式的公式来计算联合谓词的选择率.如果列中包含的数据集并不是相互独立的 话,在选择率的计算方面将会出现误差,从而导致基数出现误差.

首先来介绍oracle数据库使用基于规则优化器(RBO)来决定如何执行一个sql语句.基于规则优化器顾名思义,它是遵循一组规则来判断一个sql语句的执行计划.这组规则是有排名的如果有两种可能的规则应该应用于一个sql语句,那么排名较低的规则会被使用

在oracle 7中,引入了基于成本的优化器使用优化器的功能得以增强包括并行执行,分区,还会考虑用户真实数据的内容和分布情况.基于成本的优化器会检查一个sql语句的所有执行计划并选择一个成本值最低的,这里的成本代表一个指定执行计划所要消耗的资源使用情况.一个执行计划成本值越低该执行计划越有效.为了让基于成本的优化器精确的判断一个执行计划的成本它必需有描述该语句所访问的所有对象(表和索引)的信息和描述运行这个语句的系统信息.这些需要的信息通常被称为优化器统计数据.理解和管理优化器统计数据是优化sql执行的关键.知道何时以及及时的怎样收集统计数据对于维护一个稳定的性能来说很关键.优化器统计数据包括以下方面的信息:
什么是优化器统计数据
收集统计数据
管理统计数据
其它类型的统计数据

什么是优化器统计数据
优化器统计数据是一组描述数据库和数据库中对象信息的集合.这些统计数据在优化器给每一个sql语句选择一个最优的执行计划时会被使用.统计数据存储在数据字典中且它们能通过数据字典视图比如象user_tab_statistics来进行访问.优化器统计数据与v$视图来查看的性能统计数据不同.v$视图中的信息是系统状态和执行sql工作负载的信息.

表和列统计数据
表统计数据包括了表中的行记录数,表用来存储这些数据的数据块数,表中的平均行长度等信息.优化器使用这些信息并结合其它的统计数据来计算执行计划中各个操作的成本并评估每一个操作将返回的行数.例如,一个表扫描的成本是使用表所使用的数据块数和参数db_file_multiblock_read_count来计算出来的.可以查询user_tab_statistics视图来查看表的统计数据.

列统计数据包括一个列中的不同值的个数(NDV)还有在这个列中的最小值/最大值.可能查询user_tab_col_statistics视图来查看这列的统计数据.优化器使用列统计数据并结合表的统计数据(行数)来评估一个sql操作将要返回的行数.例如一个表有100行记录且对一个有10个不同值的列使用等号谓词评估,那么优化器会假设统一的数据分布,那么评估的基数等于表的记录数除以这个列不同值的个数:100/10=10.

额外的列统计数据
基本表和列统计数据没有提供一种机制来告诉优化器关于表中或列中的数据的特性.例如,这些统计数据不能告诉优化器表中的列中的数据是否有倾斜或者列之间是否存在关联.数据特性的信息可以通过扩展基本的统计数据象,直方图,列组和表达式统计数据提供给优化器.

直方图
直方图告诉优化器关于列中的数据分布情况.缺省情况(没有直方图),优化器会假设一个列中的不同值会均匀分布的.同上所述,优化器评估一个等号谓词的基数是通过这个表中的行数除以这个等号谓词列中不同值的个数得到的.如果数据分布不是均匀的(比如数据倾斜),那么这样的基数评估就会出错.为了精确的反映非均匀的数据分布就需要对列创建直方图.直方图的存在改变了优化器评估基数的公式且会让优化器生成一个更精确的执行计划.

Oracle基于列的使用信息(SYS.COL_USAGE$)和数据的倾斜情况能自动判断这个列是否需要直方图.例如只在一个等号谓词中看到的唯一性列oracle是不会对这个唯一性列自动创建直方图.

有两种类型的直方图,频率直方图和高度平衡直方图.oracle会基于列中的不同值的个数来决定创建直方图的类型.

频率直方图
当列中的不同值的个数据小于254时会创建频率直方图.oracle使用下面的步骤来创建频率直方图:
1. 让我们假设oracle正对promotions表的promo_category_id创建一个频率直方图.第一步从promotions表中通过对promo_category_id排序来查询promo_category_id
2. 每一个promo_category_id被指派到它所属的直方图bucket中

在这个步骤中可能直方图的bucket数超过254,因此拥有相同值的bucket会被压缩到使用这个值的最高bucket中.在这种情况下,bucket 2到 115会被压缩到bucket 115,且bucket 484到503会被压缩到bucket 503直到总的bucket数仍然保持与列中不同值的个数相等.注意上面的步骤是出于演示.dbms_stats包对直接构造压缩直方图进行了优化.

优化器现在使用频率直方图可以精确的判断谓词promo_category_id的基数.例如,对于谓词promo_category_id=10,优化器首先需要判断在直方图中10作为end point的bucket的数量.通过找到endpoint为10的bucket 503,然后减去前面的bucket数,bucket 483,503-483=20.然后基数评估将使用下面的公式进行计算(number of bucket endpoints/ total number of bucket) * num_rows,20/503*503,所以在promotoins表中promo_category_id=10的记录有20行.

当列中的不同值的数理超过254时就会创建高度平衡直方图.在高度平衡直方图中,列值被划分到bucket中所以每一个bucket可能包含相同数量的行.oracle使用下面的步骤来创建一个高度平衡直方图.
1. 我们假设oracle将要对customers表中的cust_city_id列创建一个高度平衡直方图因cust_city_id列的不同值的数量超过了254.与频率直方图类似,第一步是执行ordered by cust_city_id子句来查询customers表中的cust_city_id.

2. 在customers表中有55500行直方图中最多有254个bucket.为了让每一个bucket中有一个相等的行数,oracle必须在每一个bucket中存入219行.第219行的cust_city_id将成为第一个bucket的endpoint在这种情况下是51043.第438行的cust_city_id将成为第二个bucket的endpoint并且一直到所有254buckets被填满为止.

3. 一旦bucket创建完成后oracle会检查是否第一个bucket的endpoint值是否是cust_city_id列中的最小值,如果不是,一个”zero”bucket会被添加到直方图中而且cust_city_id列中的最小值作为它的endpoint.

4. 与频率直方图一样最后的步骤是压缩高度平衡直方图并且删除有重复endpoint的bucket.在cust_city_id列的高度平衡直方图中51166是bucket24和bucket25的endpoint.因此bucket24将会被压缩到bucket 25中

5. 使用高度平衡直方图现在优化器能对谓词cust_city_id列进行更好的基数评估.例如,对于谓词cust_city_id=51806,优化器首先会检查在直方图中使用51806作为endpoint的有多少个bucket.在这种情况下136,137,138和139的endpoint是51806(查看user_histograms).优化器将会使用下面的计算公式:
(Number of bucket endpoints / total number of buckets) * number of rows in the table
在这种情况下: 4/254 * 55500=874

然而如果谓词cust_city_id=52500它不是任何bucket的endpoint值那么优化器会使用一个不同的计算公式.对于只是一个bucket或者不是任何bucket的endpoint的值优化器将使用下面的计算公式:
DENSITY * number of rows in the table
这里的density是对直方图使用内部算法计算出来的.density的值可以通过查看user_tab_col_statistics得到这个值是从oracle database 10.2.0.4之前使用.这个值是为了向后兼容,这个值在oracle database 9i和前期的oracle database 10g中使用.因此如果optimizer_features_enable被设置的版本比10.2.0.4前那么视图中的density值将会被使用.

扩展统计数据
在oracle database 11g中引入了扩展列统计数据.扩展统计数据包括另外两种额外类型的统计数:列组和表达式统计数据.

列组
在真实的数据中,在相同表中存储在不同列中的数据之间通常是有关联的.例如,在customers表中,cust_state_province列会受country_id列的影响,比如当state为California那么country只能是United States.只使用基本的列统计数据优化器是没有办法知道真实数据之间的关系如果一个语句中的where子句中有多个列来自同一个表那么有可能会计算出错误的基数.通过将这些列作为一个团体组来扩展它的统计数据使优化器知道这些真实世界的关系.

通过对一组列创建统计数据,当来自同一个表的几个列同时一起出现在语句中的where子句中时优化器能更好的评估基数.可以使用dbms_stats.create_extended_stats函数来对要收集列统计数据的这些列定义一个列组.当一个列组被创建后,当对这个表收集统计数据时和其它原始列一样oracle将自动对列组维护统计数据.

在创建列组和重新收集统计数据之后,在视图user_tab_col_statistics中可以看到一个系统生成名字的列.这个新列代表列组:

为了将系统生成列名映射到列组上可查看用户下其它扩展的统计数据,可以查询user_stat_extensions

现在当这些列一起出现在where子句中优化器就能使用列组统计数据而不使用单列统计数据.要注意的是并不是这个列组中的所有列都必须出现在sql语句中只要列组中的这些列的子集出现在sql语句中优化器就会使用扩展的统计数据.

表达式统计数据
也可以对一个表达式(包括函数)创建扩展的统计数据,来帮助优化器来对where子句中嵌有表达式的列进行基数评估.例如,常见的是一个where子句对一个customers表的last name使用upper函数,upper(cust_last_name)=:B1,那么对于这个表达式upper(cust_last_name)创建一个扩展的统计数据是有益的.

与列组一样,在表达式统计数据被定义后需要重新对这个表收集统计数据.在统计数据被收集后可能通过user_tab_col_statistics视图来查看通过系统生成的列名,它代表表达式统计数据.就象列组一样关于表达式统计数据可以在user_stat_extensions视图中可以找到.

扩展的统计数据的限制
扩展的统计数据只有当where子句中是等号谓词或者in列表时才会被使用.如果在底层的列存在直方图且列组上不存在直方图那么扩展的统计数据将不会被使用.

索引统计数据
索引统计数据提供了索引中不同值的数量(distinct keys),索引的深度(blevel),索引的叶子块数量(leaf_blocks)和集族因子.优化器使用这些信息与其它的统计数据一起来判断一个索引访问的成本.例如优化器使用blevel,leaf_blocks和表统计数据的num_rows来判断一个索引范围扫描的成本

收集统计数据
对于不断改变的数据对象统计数据也必须定期的收集才能精确的描述数据库对象.dbms_stats包是oracle推荐收集统计数据的方法用它来替代过时的analyze命令.dbms_stats包包含了超过50个不同的收集和管理统计数据的过程.但最重要的是这些过程gather_*_stats过程.这些过程被用来收集表,列和索引统计数据.运行这些过程需要需要是对象的所有者或者有analyze any的系统权限或者是dba角色.这些过程使用的参数几乎是相同的.所以这里重点介绍gather_table_stats过程.

Gather_table_stats
Dbms_stats.gather_table_stats过程可以用来收集表,分区,索引和列统计数据.虽然这个过程有15个不同的参数,但是运行这个过程时只需要指定前两个或前三个就能满足大多数用户的需求.
包含这个表的方案名
表名
如果是分区表且想要对这个特定的分区收集统计数据就是一个特定的分区名

其它的参数在大多数情况下可以保留其默认值.

Estimate_percent参数
这个estimate_percent参数判断在计算统计数据是使用的行记录的百分比.当表中所有的行(100%sample)被处理时收集的统计数据最精确,通常也称作计算统计数据.在oracle database 11g中引入了一种新的抽样算法是基于哈希的且提供了确定的统计数据.这种新的算法精确度与100%抽样很接近但是成本只有100%抽样的10%.当任何dbms_stats.gather_*_stats过程中的estimate_percent设置为auto_sample_size(缺省值)时就会使用这种新的算法.以前用户将estimate_percent参数设置为一个较低的值来确保统计数据能被快速的收集完成.然而没有经过详细的测试是很难知道使用什么样的抽样大小可以得到精确的统计数据.强烈建议从oracle database 11g开始让estimate_percent参数使用其缺省值(而不是显式的设置).

Method_opt参数
这个method_opt参数控制着在收集统计数据时是否创建直方图.直方图是当列中数据不是均匀分布时创建的一种特定类型的列统计数据.使用缺省值for all column size auto,oracle会基于列的使用信息(dbms_stats.report_col_usage)和列中不同值的数量来自动判断哪个列和将要使用的bucket的数量.列的使用信息反映了数据库对一个指定对象所处理的所有sql操作的一种分析.列使用跟踪缺省情况下是启用的.

如果列在where子句中以等号谓词,范围,like等形式出现那么这个列是创建直方图的一个备选者.如果在创建直方图之前列中的数据存在倾斜oracle也会验证.例如列只以等号谓词出现
且是唯一性列将不会对这个列创建直方图.

Degree参数
这个degree参数控制着用于收集统计数据的并行服务器进程的个数.通常oracle使用并行服务器进程的个数与数据字典中表的degree属性指定的值相同(并行度).缺省情况下在oracle database中所有表的这个属性被设置为1,如果对一个大表收集统计数据为了加快收集的速度这个参数是用的.当参数degree设置为auto_degree,oracle将基于对象的大小来自动判断收集统计数据时的并行服务器进程的个数.这个值的范围介于小对象的1(串行执行)到大对象的
Default_degree(parallel_threads_per_cpu * cpu_count).

Granularity参数
这个granularity参数指示了对分区表收集统计数据的级别.可能的级别是表(全局),分区或子分区.缺省情况下基于表的分区策略oracle将会判断需要使用的级别.统计数据总是在第一级分区进行收集不管分区的类型.当子分区类型是list或range时会收集子分区统计数据.如果表不是分区表这个参数会被忽略.

Cascade参数
这个cascade参数控制着是否对表中的索引收集统计数据.缺省值auto_cascade,oracle将只会对哪些统计数据过期的表重新收集索引统计数据.当一个大量数据直接加载时并且索引是禁用时节cascade通常是设置为false.在数据加载完成后,索引要被重建统计数据也会自动创建,当收集表统计数据时不需要收集索引统计数据

No_invalidate参数
这个no_invalidate参数决定在统计数据收集后游标(当收集统计数据时访问表的游标)是否立即失效.缺省值是dbms_stats.auto_invalidate,游标(已经被解析的语句)不会立即失效.它们将使用之前统计数据所创建的执行计划直到oracle基于内部启示决定依赖的游标失效为止.为了确保在共享池中没有性能问题或者如果有大量的依赖游标而且它们都是硬解析随着时间的推移失效将会发生.

改变dbms_stats.gather_*_stats中参数的缺省值
可以对一个单独的dbms_stats.gather_*_stats命令指定一个非缺省值或者对于数据库覆盖其缺省值.可以使用dbms_stats.set_*_prefs
过程来覆盖dbms_stats.gather_*_stats过程中的缺省参数值.下面是可以修改的参数列表:
AUTOSTATS_TARGET (SET_GLOBAL_PREFS only as it relates to the auto stats job) CONCURRENT (SET_GLOBAL_PREFS only)
CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL STALE_PERCENT

可以使用下面的dbms_stats.set_*_prefs过程来在表,方案,数据库或全局级别来覆盖每一个参数的缺省值.
SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
SET_GLOBAL_PREFS

Set_table_prefs过程允许修改用于一个特定表统计收集dbms_stats.gather_*_stats过程的缺省参数值.

Set_schema_prefs过程允许修改用于一个特定方案中所有表统计收集dbms_stats.gather_*_stats过程的缺省参数值.这个过程实际上是对一个特定方案中的每一个表调用一次set_table_prefs过程,因为它是调用set_table_prefs所以调用这个过程在其运行后不会对新创建的对象有影响.新对象将对所有参数使用golable引用值.

Set_database_prefs过程允许修改用于数据库所有用户方案统计收集dbms_stata.gather_*_stats过程的缺省参数值.这个过程实际上是对每一个用户方案中的每一个表调用set_table_prefs过程. 因为它是调用set_table_prefs所以调用这个过程在其运行后不会对新创建的对象有影响.新对象将对所有参数使用golable引用值.如果将参数add_sys设置为true那么它也有可能包含oracle所拥有的方案(sys,system等).

Set_global_prefs过程允许修改用于数据库中任何对象统计收集dbms_stats.gather_*_stats过程的缺省参数值.所有参数缺省为全局设置除非有表进行了优先设置或者这个参数通过gahter_*_stats命令被显式的设置.通过这个过程修改的参数将会影响修改之后所有新创建的对象.新对象将会使用所有参数的global_prefs.
使用set_global_prefs也可以对两个额外的参数autostat_target和concurrent.autostat_target设置缺省值来控制什么对象将会被自动统计收集job来进行统计收集.这个参数可能的值有all,oracle和auto.缺省值是auto.

Concurrent参数控制着在一个用户(或数据库)是否并发对多个表和对一个表的多个分区收集统计数据.这是一个布尔型的参数,缺省值为false.concurrent参数值不会影响自动统计收集job.

Dbms_stats.gather_*_stats过程和自动统计收集job遵循以下层次的参数值,通过命令显式设置的参数值将会对其它设置进行覆盖.如果参数没有通过命令进行设置,就会检查表级的参数引用.如果没有表级引用设置,就使用global引用.

如果你不能确保什么级别的参数引用被设置可以使用dbms_stats.get_prefs函数来检查.这个函数有三个参数,参数名,方案名和表名.在下面的例子中我们首先检查sh.sales表上的stale_percent的值.然后设置表级参数并且检查使用dbms_stats.get_prefs的影响.

自动统计收集job
通过在一个预定义的维护窗口中运行一个oracle自动任务来收丢失统计或统计过期的所有数据库对象自动收集统计数据(工作日的上午10点到零晨2点和周末的6点到零晨2点).

自动收集统计数据是通过调用内部过程
Dbms_stats.gather_database_stats_job_proc来实现的.这个过程操作非常类似如使用gahter auto选项的dbms_stats.gather_database_stats过程.主要区别是oracle内部非常重视需要统计数据的数据库对象，所以这些对象那个最需要更新统计数据谁就会被优先处理.可以通过查询dba_autotask_client_job视图或EM来验证.也可以通过EM来改变这个job的维护窗口.

一个表中被修改的行数超过stale_percent(缺省值10%)时就会认为这个表的统计数据过时了.oracle会监控所有表的DML活动并在SGA中进行记录.监控的信息会定时的刷新到磁盘且可以通过*_tab_modifications视图来查看.

也可以调用dbms_stats.flush_database_monitoring_info过程来手动刷新这些数据.如果想在查询时得到最新信息(在所有统计数据收集之前内部监控数据会被刷新).可以通过查询user_tab_statistics视图中的stale_stats列来查看哪个表的统计数据过时了.

表的stale_stats被设置为NO,统计数据是最新的.表的stale_stats被设置为YES,统计数据是过时的.表的stale_stats没有被设置说明丢失统计数据.

如果你有一套完善的统计数据收集过程或者因为某些原因想对主应用程序方案禁用自动统计收集,保留对数据字典表的收集.可以将dbms_stats.set_global_prefs过程的autostats_target从auto设置为oracle.

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ORACLE’);
END;
/

同时一起禁用自动收集任务:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);
END;
/
提高收集统计数据的效率
当你确定了对哪些统计数据感兴趣你可能想要及时的收集这些统计数据.传统上人们希望通过使用并行来提高统计数据收集的速度.然而,如果方案中所有的对象很小并不能使用并行执行那要怎样提高统计数据收集的速度呢

并行统计数据收集
在oracle database 11g release 2(11.2.0.2)中,并行统计数据收集模式被引入来同时并发的收集一个方案中的多个表和一个表中的多个分(子分)区.对多个表和多个分(子分)区同时并行收集统计数据能充分利用多CPU的资源来减少收集的时间.

并发统计数据收集是通过全局参数来控制的,concurrent可以设置为true或false.缺省值为false.当concurrent设置为true时,oracle将会使用oracle job调度和高级队列组件来创建和管理多个并发统计数据收集job.

当concurrent设置为true时,对一个分区表调用dbms_stats.gather_table_stats使用oracle对这个分区表的每一个分区创建一个竭的统计数据收集job.这些job中有多少将会同时并发执行,这些可能job队列过程中有多少将会排队(初始化参数job_queue_processes.在rac中每一个节点的job_queue_processes)和有多不可用的系统资源.当当前运行的job完成后,更多的job会被排队和执行直到所有的分区都完成统计数据收集为止.

如果使用dbms_stats.gather_database_stats,dbms_stats.gather_schema_stats或者dbms_stats.gather_dictionary_stats收集统计数据,那么oracle将会对每一个非分区表和对分区表的每一个分区创建一个单独的统计数据收集job.每一个分区表都有一个调度job来管理它的分区job.数据库会尽可能的同时多运行几个job和余下的job将会排队直到执行的job完成.然而为了防止死锁的发生多个分区表不能同时被处理.因此如果对一个分区表运行了几个job那么其它的分区表将会进行排队等待直到当前的收集job完成为止.对于非分区表没有限制.

下面的数据说明了在sh方案中使用dbms_stats.gather_schema_stats命令在不同级别创建job.
Oracle将会对每一个非分区表创建一个统计数据收集job.
CHANNELS,
COUNTRIES,
CUSTOMERS,
PRODUCTS,
PROMOTIONS,
TIMES
每一个分区表一个调度job.比如,sales和costs.且它将对sales和costs中的每一个分区创建一个统计数据收集job.

假设job_queue_processes参数设置为32,oracle job调度器允许运行32个统计数据收集job.假设costs表的第29个job开始执行,那么三个非分区表统计数据收集job也会开始运行,sales的统计数据收集job将会自动排队,因为一次只能处理一个分区表.当每一个job完成后,另外的job将会进行排队和开始执行直到所有64个job全部完成为止.每一个单独的统计数据收集job都能使用并行执行.

配置和设置
在oracle database 11.2.0.2中,统计数据收集的并行设置缺省情况下是关闭的.可以使用下面的命令将其打开.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’TRUE’);
END;
/
为了收集统计数据可能也会需要一些额外的权限.用户必须要有job scheduler和AQ权限:
CREATE JOB
MANAGE SCHEDULER
MANAGE ANY QUEUE

当job scheduler在sysaux表空间中存储它的内部表和视图时sysaux表空间应该是联机状态.最后对于统计数据收集过程来说job_queue_processes参数的设置应该要能完全利用可用的系统资源.如果不计划使用并行执行应该将job_queue_processes设置为cpu核数的2倍(在rac中需要对每一个节点进行设置).要确保在系统级别设置这个参数(alter system..或者通过init.ora文件)而不是在会话级别进行设置(alter session).

如果将使用并行执行作为并发统计数据收集的一部分应该禁用parallel_adaptive_multi_user初始化参数.
ALTER SYSTEM SET parallel_adaptive_multi_user=false;
也建议启用并行语句队列.这要求资源管理器是激活的且创建一个临时资源计划组资源消耗组”OTHER_GROUPS”将启用排队.通常资源管理器只在维护窗口期间是激活的.下面的脚本将创建一个临时资源计划并对这个计划使用资源管理器.

你会注意到自动统计数据收集job现在不会使用并发.将concurrent设置为true对自动统计数据收集job不会有影响.

分区表的统计数据收集
分区表的统计数据收集它是由表级别和分区级别的统计数据收集组成.在oracle database 11g之前,新增加一个分区或修改一个分区都要求扫描整个表来重新刷新表级别的统计数据.如果跳过全局层面的统计数据收集那么优化器会基于已经存在的分区级别的统计数据来推断全局层面的统计数据.这种方法对于简单表的统计数据来说是精确的比如象行数可以通过聚合每一个分区的行数来统计—但是其它的统计数据是不能精确的判断的.比如,不能基于所有分区的单个分区来精确的判断一个列中的不同值的数量(优化器使用的最关键的统计数据).

在oracle database 11g中通过引入增量全局统计数据增加了对分区表的统计数据收集.如果一个分区的incremental参数设置为true,dbms_stats.gather_*_stats参数granularity为global,estimate_percent设置为auto_sample_size. Oracle将会对新的分区收集统计数据并通过扫描新增加的或修改的分区来更新所有全局层面的统计数据但不是扫描整个表.

增量全局统计数据是通过为表中每一个分区存储的概要来计算出来的.一个概要是分区和分区中列的统计数据的元数据.每一个概要存储在sysaux表空间中.全局统计数据是通过聚合分区级别的统计数据和每一个分区的概要生成的,因此消除了为了收集表级别统计数据而要扫描整个表.当一个新的分区被增加到表中,仅需要对新增加的分区收集统计数据.全局统计数据会自动使用新的分区概要和已经存在的分区概要来精确的更新.

下面是使用增量全局统计数据的步骤
在表级或全局层面启用增量统计数据
BEGIN
DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,’INCREMENTAL’,’TRUE’);
END;
/
为了检查一个指定表的incremental的当前设置可以使用dbms_stats.get_prefs;
SELECT DBMS_STATS.GET_PREFS(‘INCREMENTAL’,’SH’,’SALES’) FROM dual;
注意incremental不会应用到子分区上.子分区和分区的统计数据会正常的被收集.只是分区统计数据将会用来确定全局或表级别的统计数据.

管理统计数据
为了收到适当的统计数据,提供一种全面框架来管理它们也很重要.oracle提供了一系列的方法来做这件事包括将统计数据还原到之前的版本,将统计数据从一个系统传输到另一个系统或者甚至手动设置统计数据.这些选项在特定情况下是非常有用的,但是不建议替换标准的收集统计数据的方法dbms_stats.

还原统计数据
从oracle database 10g开始,当你使用dbms_stats收集统计数据时原始的统计数据会自动在数据字典表中进行备份, 如果新收集的统计数据导致任何的问题通过运行dbms_stats.restore_table_stats就能很容易的还原.视图dba_tab_stats_history包含了每一个表统计数据保存的时间戳.

下面的例子将sales表的统计数据还原成昨天收集的且在shared_pool中引用sales表的所有游标会自动变为无效.想让所有的游标变为无效因为将统计数据还原成昨天的想让它们立即影响所有游标.no_invalidate参数值会判断引用该表的游标是否会变为无效.
BEGIN
DBMS_STATS.RESTORE_TABLE_STATS(ownname => ‘SH’,
tabname => ‘SALES’,
as_of_timestamp => SYSTIMESTAMP-1
force => FALSE,
no_invalidate => FALSE);
END;
/

Pending统计数据
通常情况下当收集统计数据时,收集到的统计数据会被立即发布(写)到适当的数据字典表并提供给优化器使用.在oracle database 11g中可以将收集到的统计数据不立即发布,而是将它们存储为一个没有发布的pending状态.它们不是存储在常用的数据字典表中而是存储在pending表所以在发布它们之前可以对它们进行测试.这些pending统计数据可以以一种受控的方式对单个会话启用,这样就可以在发布之前对这些统计数据进行验证.为了启用pending统计数据收集需要使用dbms_stats.set_*_prefs这样的一个过程对你想创建pending统计数据的对象将参数publish从true改变false.
BEGIN
DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,‘PUBLISH’,’FALSE’);
END;
/

收集统计数据
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’);
END;
/
对这些对象收集的统计数据可以通过user_*_pending_stats来显示.可以通过一个alter session命令来设置初始化参数optimizer_use_pending_stats为true来告诉优化器使用pending统计数据并运一个sql,对于访问的表没有pending统计数据优化器将会使用标准数据字典表中的当前统计数据.当对pending统计数据进行验证后可以使用下面的过程将其发布.
DBMS_STATS.PUBLISH_PENDING_STATS.
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS(‘SH’,’SALES’);
END;
/

导出/导入统计数据
当开发一个新应用程序或一个新模块想要测试时,理想情况下想要测试系统与生产系统的硬件平台和数据大小是相同的.这种情况并不总是可以满足的.最常见的问题是生产系统的大小.通过将一个产生数据库中的优化统计数据复制到另一个相同数据库版本的系统中,这样就可以模仿生产环境中的行为.生产数据库中的统计数据可以使用dbms_stats.export_*_stats和dbms_stats.import_*_stats过程复制到测试系统中.

在导出统计数据之前需要使用dbms_stats.create_stat_table过程来创建一个表来存储这些统计数据.在这个表创建之后可以使用dbms_stats.export_*_stats过程从数据字典中导出统计数据.当统计数据填充到统计数据表中,可以使用datadump从生产数据库中将统计数据表中的数据导出并导入到测试数据库中.当统计数据表完全导入到测试数据库中,可以使用dbms_stats.import_*_stats过程导入到数据字典表.下面的例子将创建一个统计数据表MYSTATS并从sh方案中将统计数据导出到MYSTATS统计数据表中.

复制分区统计数据
当处理分区表时优化器依赖整个表(全局统计数据)统计数据和单个分区统计数据来给一个sql语句选择一个好的执行计划.如果查询需要访问的只有单个分区优化器只会使用要访问的这个分区的统计数据.如果查询要访问多个分区优化器将会使用全局统计数据和分区统计数据.

常见的一个范围分区表有一个新的分区增加到一个存在的表中并向这个增加的分区插入数据.如果终端用户在统计数据收集之前要查询新插入的数据,由于过时的统计数据可能会选择一个次优的执行计划.最常见的一种情况是where子句中谓词使用值超出了列统计数据中[最小值,最大值]的范围.这就是一个’out-of-range’的错误.
超出范围的条件可以使用dbms_stats.copy_table_stats过程(从oracle database 10.2.0.4开始可以使用)来预防.这个过程复制一个代表源(子)分区的统计数据到一个新创建的空的(子)分区中.它也会复制依赖对象的统计数据:列,本地(分区)索引等等.按下面的方式来调整分区列的最小值和最大值.
如果分区类型是hash分区那么目标分区的最小值和最大值与源分区相同.

如果分区类型是list分区且目标分区是一个not default分区,那么目标分区的最小值将被设置为描述目标分区列表值中的最小值.最大值将被设置为描述目标分区列表值中的最大值.

如果分区炻是list分区且目标分区是一个default分区,那么目标分区的最小值被设置为源分区中的最小值,目标分区的最大值被设置为源分区中的最大值.

如果分区类型是range那么目标分区的最小值设置为之前分区的上限值,目标分区的最大值设置为目标分区的上限值除非目标分区的上限值是maxvalue,在这种情况下目标分区的最大值设置为之前分区的上限值.

它也能基于指定的比例来复制统计数据(比如块数或行数).下面的命令将sales_q3_2011范围分区的统计数据复制到sales表的sales_q4_2011分区中.比例因子为2.
BEGIN
DBMS_STATS.COPY_TABLE_STATS(‘SH’,’SALES’,’SALES_Q3_2002′,’SALES_Q4_2002′, 2);
END;
/

如果索引分区名与表分区一样只会复制索引统计数据.全局或表级统计数据缺省情况下不会被更新.全局层面的统计数据只有调用dbms_stats.copy_table_stats时没有全局统计数据存在时通过聚合生成全局统计数据时才会被影响.

比较统计数据
一个系统中sql语句的执行计划与另一个系统中的执行计划不同一个关键的原因变是每个系统中的统计数据不同,例如测试系统中的数据不是100%的与生成系统的中相同.为了识别不同的统计数据
Dbms_stats.diff_table_stats_*函数可以用来比较两个不同数据来源的表统计数据.统计数据来源可以是:
一个用户统计数据表和数据字典中的当前统计数
单个用户统计数据表包含两组统计数据可以通过statids来识别
两个不同用户统计数据表
历史中的两个时间点的统计数据
当前统计数与厍上的一个时间点的统计数据
Pending统计数据与数据字典中当前的统计数据
Pending统计数据与用户统计数据表
这个函数也可以比较两个单独对象的统计数据(索引,列,分区)而且如果统计数据之间的差别超过了指定的阈值也俑显示两个来源对象的所有统计数据.这个阈值可以通过一个函数的参数来指定缺省值是10%.第一个来源的统计数据将会用作计算不同百分比的基数.

下面的例子中将用当前数据字典中emp表的统计数据与统计数据表TAB1中emp表的统计数据进行比较,下面的sql语句将会生成了一个报告:
SELECT report, maxdiffpct
FROM table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’ ));

锁定统计数据
在有些情况下,可以通过锁定统计数据来阻止对表或方案收集新的统计数据.当统计数据被锁定后这些统计数据直到这些统计数据被解锁或者使用gather_*_stats过程时将参数force设置为true否则是不会被修改的.

在oracle database 11g中dbms_stats包被扩展到允许统计数据在分区级别被锁定和解锁.这些额外的过程允许更细粒度的来控制.
BEGIN
DBMS_STATS.LOCK_PARTITION_STATS(‘SH’,’SALES’, ‘SALES_Q3_2000’);
END;
注意这里有一种层次结构的锁定统计数据.例如,如果对一个分区表锁定统计数据,然后为了对一个分区重新收集统计数据需要对这一个分区解锁统计数据这时就会收到ora-20005错误.出现这个错误是因为分区虽然已经解锁但表级仍然被锁定.这时要对分区重新收集统计数据只在将force参数设置为true才能成功.

手动设置统计数据
在某些特定的情况下手动设置存储在数据字典中的优化器统计数据是非常有效的.比如一个高度不稳定的全局临时表(注意虽然这里里讨论手动设置统计数据,但是不建议这么做.因为不精确或不一致的统计数据可能导致选择性能极差的执行计划).统计数据可能调用
Dbms_stats.set_*_stats过程来手动设置.

其它类型的统计数据
除了基本表,列和索引统计数据之外优化器还使用额外的信息来判断一个语句的执行计划.额外的信息包含动态抽样和系统统计数据.

动态抽样
动态抽样是在oracle database 9i release 2中引入用来在优化一个sql语句时收集额外的语句特定对象的统计数据.最常见的误解是动态抽样可以用来替优化器统计数据.动态抽样的目的是当常规的统计数据不能满足良好的基数评估时来扩大现有的统计数据.

所以怎样和何时使用动态抽样呢.在编译一个sql语句时优化器通过考虑可用的统计数据是否能生成一个良好的执行计划来决定是否使用动态抽样.如果可用的统计数据不满足就会使用动态抽样.它通常是用来弥补由于丢失或无效的统计数据导致一个糟糕的执行计划的情况.对于一个查询中的一个或多个表没有统计数据,优化器在优化语句之前将会使用动态抽样来对这些表生成基本的统计数据.动态抽样收集的统计数据质量不高或者没有使用dbms_stats收集的完整.

第二种使用动态抽样的情况是当语句包含一个复杂的谓词表达式且扩展统计数据不可以使用或者不能使用的时候.例如,一个查询在两个相关列之间使用的不是等号谓词.在这种情况下标准的统计数据不能满足判断,优化器假设每一个谓词将会减少查询所要返回的行数.根据标准的统计数据判断的基数是20197但实际上返回的行数是210420.
SELECT count(*)
FROM sh.Sales
WHERE cust_id < 2222 AND prod_id > 5;

使用标准的统计数据优化器不能识别sales表中cust_id与prod_id之间的关联.通过设置
Optimizer_dynamic_sampling为级别6,优化器将使用动态抽样对复杂谓词表达式收集额外的信息.通过动态抽样提供的额外信息允许优化器生成一个更精确的基数评估因此会选择一个更好的执行计划.

正如你所看到的一样动态抽样是由参数optimizer_dynamic_sampling来控制的,它可能设置为不同的级别(0-10).这些级别控制着两件不同的事;当动态抽样开始时使用多大的抽样大小来生成统计数据.太大的抽样大小,动态抽样对编译查询的时间的影响就会越大.

从oracle database 11g release 2开始,优化器将会自动决定动态抽样是否有用和对于并行执行的sql语句使用什么样的动态抽样级别.这个决定是基于语句中表的大小和谓词的复杂程度.然而如果optimizer_dynamic_sampling参数被显式的设置为一个非缺省值,那么用户指定的值将会被使用.可以查看执行计划的note部分来了解是否使用了动态抽样.例如,如果对sales表启用了并行执行,执行下面的查询,优化器将会自动使用级别为4的动态抽样

对于串行执行的sql语句,动态抽样的级别将根据optimizer_dynamic_sampling参数来决定.将不是由优化器自动触发.原因是串行语句通常运行时间短且在编译时的任何开销都可能影响它的性能.而并行语句会使用更多的资源,所以在编译时产生的额外开销对获得更好的执行计划来说是值得的.

系统统计数据
在oracle database 9i中,系统统计数据引入是为了让优化器通过使用关于执行语句的真实系统硬件信息比如cpu速度和IO性能来更精确的计算出执行计划中每一步的成本.

系统统计数据缺省情况下是启用且缺省值是自动初始化的,这些值代表了大多数系统.当收集系统统计数据时它们将会覆盖这些初始值.为了收集系统统计数据可以在一个有代表性的工作负载时间窗口使用dbms_stats.gather_system_stats,理想的情况是在负载高峰期间收集.

系统统计数据只需收集一次.系统统计数据不是作为自动统计数据收集job的一部分也不会自动被收集.必须使用gahter_system_statistics来更新系统统计数据.

字典表统计数据
因为现在优化器只支持基于成本的优化器,数据库中所有的表都需要有统计数据包括所有字典表(sys,system用户所拥有的表它们存储在system和sysaux表空间中).字典表的统计数据是由在晚上维护窗口运行的自动统计数据收集job来维护的.如果想对应用程序用户关闭自动统计数据收集job任务但是对字典表保留.可以调用dbms_stats.set_global_prefs将autostats_target参数从auto改为oracle.

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ORACLE’);
END;
/
字典表的统计数据通过使用dbms_stats.gather_dictionary_stats过程来手动设置.用户必须要有analyze any dictionary和analyze any系统权限或者dba角色来更新字典表统计数据.建议对字典表的统计数据维护与常规用户方案统计数据维护方式保持一致.

固定表的统计数据
需要对动态性能表和它们的索引(固定对象)收集统计数据.它们有x$表和v$视图.因为v$视图可能会象其它的用户表或视图一样出现在sql语句中.对这些表收集统计数据来让优化器生成一个更好的执行计划来说是很重要的.然而不象其它的数据库表,当优化统计数据丢失时不会对语句调用的x$表使用动态抽样.优化器会使用预先定义的缺省统计数据.这些缺省值可能没有代表性且可能会导致选择一个次优的执行计划.在系统中可能会造成一些性能问题.因为这个原因强烈建议收集固定对象的统计数据.

自动统计数据收集job不能对固定对象的统计数据进行收集或维护.可以使用dbms_stats.gather_fixed_objects_stats过程来收集固定对象的统计数据
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
这个dbms_stats.gather_fixed_objects_stats过程除了数据块的数量之外与dbms_stats.gather_table_stats过程收集的统计数据是一样的.因为x$表只是在内存结构中不存储到磁盘所在块总是为0.因为x$表的瞬态性很重要当系统中有一个有代表性的工作负载就是对固定对象收集统计数据的时候.用户必须要有analyze any dictionary系统权限或者dba角色来更新固定对象统计数据.当你对数据库或程序进行升级后强烈建议重新收集固定对象的统计数据.

小结
为了让基于成本的优化器精确的判断一个执行计划的成本,它必须有sql语句所访问的所有对象的信息和关于运行这个语句的系统方面的信息.这些必要的信息通常被称作优化器统计数据.理解和管理优化器统计数据是优化sql执行的关键.知道何时以及怎样及时的收集统计数据是维护良好性能的关键.

通过使用自动统计数据收集job和dbms_stats包.dba可以对系统维护一组精确的统计数据来确保优化器将有最好的资源信息来确定语句的执行计划.

一个社保系统的自助查询系统查询个人医疗费用明细的查询语句要用一分多钟还没查询出来，语句如下：

select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'

从上面的语句可知是从视图 v_zzzd_ylbx_ylfymxcx中查询数据。v_zzzd_ylbx_ylfymxcx视图的创建语句如下：

create or replace view v_zzzd_ylbx_ylfymxcx as
select a.indi_id aac001,a.idcard aac002,'' aof008,a.center_id aab301,
       a.name aac003,a.hospital_id akf008,d.hospital_name akf009,a.serial_no akf010,
       f.biz_name akf011,
       nvl(round(sum(b.real_pay),2),0) akf012,
       nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf013, 0 akf014,0 akf015,0 akf016,
       nvl(round(sum(case when b.fund_id = '001' then b.real_pay else 0 end),2),0) ak093,
       nvl(round(sum(b.real_pay),2),0) - nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak092,
       nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak094,
       nvl(round(sum(case when b.fund_id in('003', '999') then b.real_pay else 0 end),2),0) ak095,
       a.fin_date akf017,to_char(nvl(a.in_days,0)) akf018,
       nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf019,
       nvl(round(sum(case when b.fund_id in( '001','201','301' ) then b.real_pay else 0 end),2),0) akf020
from  bs_insured h,mt_biz_fin a ,mt_pay_record_fin b,bs_disease c,bs_hospital d ,bs_hosp_level e ,bs_biztype f,bs_corp g
where h.indi_id=a.indi_id
  and a.hospital_id = b.hospital_id
  and a.serial_no = b.serial_no
  and a.biz_type = f.biz_type
  and a.center_id = f.center_id
  and a.center_id=c.center_id
  and a.fin_disease=c.icd
  and a.hospital_id = d.hospital_id
  and d.hosp_level=e.hosp_level
  and a.biz_type in ('10','11','12','13','16','17')
  and a.valid_flag = 1
  and b.valid_flag = 1
  and a.pers_type in ('1','2')
  and a.corp_id = g.corp_id
group by a.indi_id ,a.idcard ,a.center_id,a.name ,a.hospital_id ,d.hospital_name,a.serial_no , f.biz_name,a.fin_date,a.in_days;

生成SQL Profile有两种方式：自动和手动方式，这里使用自动方式来生成SQL Profile.
下面创建一个SQL自动调整优化任务：

SQL> declare 
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
  6   my_task_name :=dbms_sqltune.create_tuning_task(
  7           sql_text => my_sqltext,
  8           user_name => 'INSUR_CHANGDE',
  9           scope=>'COMPREHENSIVE',
 10          time_limit=>60,
 11          task_name => 'my_sql_tuning_task_2014080803',
 12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080803');
  3  end;
  4  /

PL/SQL procedure successfully completed.

通过下面的语句查询优化建议

SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080803') from dual;


GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080803
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 19:42:47
Completed at                      : 08/08/2014 19:43:49
Number of Index Findings          : 1
Number of SQL Restructure Findings: 1
Number of Errors                  : 1

-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
             aac002='430703198202280017'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 99.98%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
    create index INSUR_CHANGDE.IDX$$_429C0001 on
    INSUR_CHANGDE.MT_BIZ_FIN("IDCARD",TO_NUMBER("VALID_FLAG"),"PERS_TYPE","BIZ_
    TYPE");
    这里在创建IDX$$_429C0001索引时,TO_NUMBER("VALID_FLAG")这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
     
  - 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
    create index INSUR_CHANGDE.IDX$$_429C0002 on
    INSUR_CHANGDE.MT_PAY_RECORD_FIN("HOSPITAL_ID","SERIAL_NO");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "Access Advisor"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  谓词 TO_NUMBER("A"."VALID_FLAG")=1 (在执行计划的行 ID 9 处使用) 包含索引列 "VALID_FLAG"
  的隐式数据类型转换。此隐式数据类型转换使优化程序无法有效地使用表 "INSUR_CHANGDE"."MT_BIZ_FIN" 的索引。
  这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
  Recommendation
  --------------
  - 将谓词重写为等价型以便利用索引。

  Rationale
  ---------
    如果谓词是不等式条件或者如果存在关于索引列的表达式或隐式数据类型转换, 则优化程序无法使用索引。

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- 当前操作因超时而中断。这是因为优化任务设置的超时时间为60秒的原因

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3562745886

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   1 |  HASH GROUP BY                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   2 |   NESTED LOOPS                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   3 |    NESTED LOOPS                     |                         |     7 |  1491 |   127K  (2)| 00:25:25 |
|   4 |     NESTED LOOPS                    |                         |     7 |  1253 |   127K  (2)| 00:25:25 |
|   5 |      NESTED LOOPS                   |                         |     7 |  1127 |   127K  (2)| 00:25:25 |
|   6 |       NESTED LOOPS                  |                         |     7 |  1085 |   127K  (2)| 00:25:25 |
|   7 |        NESTED LOOPS                 |                         |    14 |  1554 |   127K  (2)| 00:25:25 |
|   8 |         NESTED LOOPS                |                         |    14 |  1484 |   127K  (2)| 00:25:25 |
|*  9 |          TABLE ACCESS FULL          | MT_BIZ_FIN              |    14 |  1232 |   127K  (2)| 00:25:25 |
|  10 |          TABLE ACCESS BY INDEX ROWID| BS_BIZTYPE              |     1 |    18 |     1   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN         | PK_BS_BIZTYPE           |     1 |       |     1   (0)| 00:00:01 |
|* 12 |         INDEX UNIQUE SCAN           | PK_BS_CORP              |     1 |     5 |     1   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN       |     1 |    44 |     1   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN            | IDX_MT_PAY_RECORD_FIN_1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN             | PK_BS_INSURED           |     1 |     6 |     1   (0)| 00:00:01 |
|* 16 |      INDEX RANGE SCAN               | INX_BS_DISEASE_01       |     1 |    18 |     1   (0)| 00:00:01 |
|  17 |     TABLE ACCESS BY INDEX ROWID     | BS_HOSPITAL             |     1 |    34 |     1   (0)| 00:00:01 |
|* 18 |      INDEX UNIQUE SCAN              | PK_BS_HOSPITAL          |     1 |       |     1   (0)| 00:00:01 |
|* 19 |    INDEX UNIQUE SCAN                | PK_BS_HOSP_LEVEL        |     1 |     2 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter("A"."IDCARD"='430703198202280017' AND TO_NUMBER("A"."VALID_FLAG")=1 AND
              ("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2') AND ("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR
              "A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
  11 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
  12 - access("A"."CORP_ID"="G"."CORP_ID")
  13 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
  14 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  15 - access("H"."INDI_ID"="A"."INDI_ID")
  16 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  18 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  19 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")

这是按优化建议创建两个索引后的执行计划

2- Using New Indices  
--------------------
Plan hash value: 2373509962

----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   1 |  HASH GROUP BY                       |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   2 |   NESTED LOOPS                       |                   |     7 |  1505 |    13   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |                   |     7 |  1470 |    12   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                   |     7 |  1428 |    11   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |                   |     7 |  1302 |    10   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                   |                   |     7 |  1288 |     9   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                   |     7 |  1050 |     7   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                   |    14 |  1484 |     4   (0)| 00:00:01 |
|   9 |          INLIST ITERATOR             |                   |       |       |            |          |
|  10 |           TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    14 |  1232 |     2   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | IDX$$_429C0001    |    14 |       |     1   (0)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID | BS_BIZTYPE        |     1 |    18 |     1   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN          | PK_BS_BIZTYPE     |     1 |       |     1   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN            | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN                | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |    INDEX UNIQUE SCAN                 | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - access("A"."IDCARD"='430703198202280017' AND "MT_BIZ_FIN".???)
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  13 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
  14 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
  15 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  17 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  18 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("H"."INDI_ID"="A"."INDI_ID")
  21 - access("A"."CORP_ID"="G"."CORP_ID")

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

因为前一次优化任务因为超时中断了所以再次进行SQL自动优化任务,并将超时时间设置为600秒

SQL> declare 
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
  6   my_task_name :=dbms_sqltune.create_tuning_task(
  7           sql_text => my_sqltext,
  8           user_name => 'INSUR_CHANGDE',
  9           scope=>'COMPREHENSIVE',
 10          time_limit=>600,
 11          task_name => 'my_sql_tuning_task_2014080804',
 12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080804');
  3  end;
  4  /

PL/SQL procedure successfully completed.

通过下面的语句查询优化建议

SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080806') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080804
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 600
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 20:03:46
Completed at                      : 08/08/2014 20:04:27
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
             aac002='430703198202280017'

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 28.75%)
  ------------------------------------------
  - 考虑接受推荐的 SQL 概要文件。
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2014080804', replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3514293130

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                   |   251 | 53965 |    35   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   252 | 52920 |    34   (3)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |   252 | 51408 |    33   (4)| 00:00:01 |
|*  5 |      HASH JOIN                        |                   |   251 | 46686 |    32   (4)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID     | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4704 |    28   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3472 |    22   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                   |    79 |  9638 |    21   (0)| 00:00:01 |
|  10 |           INLIST ITERATOR             |                   |       |       |            |          |
|  11 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|  13 |           TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 15 |          INDEX UNIQUE SCAN            | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN              | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  17 |       TABLE ACCESS FULL               | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN                 | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 20 |    INDEX UNIQUE SCAN                  | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
   6 - filter("B"."VALID_FLAG"='1')
  12 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2'))
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  14 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  15 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  16 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  18 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  19 - access("H"."INDI_ID"="A"."INDI_ID")
  20 - access("A"."CORP_ID"="G"."CORP_ID")

2- Using SQL Profile
--------------------
Plan hash value: 484693682

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   251 | 53965 |    24   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |    28 |  4788 |    19   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                   |    28 |  4284 |    18   (6)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                   |    28 |  4116 |    17   (6)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4060 |    16   (7)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3108 |    10   (0)| 00:00:01 |
|*  9 |          HASH JOIN                    |                   |    28 |  2968 |     9   (0)| 00:00:01 |
|  10 |           TABLE ACCESS FULL           | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|  11 |           INLIST ITERATOR             |                   |       |       |            |          |
|  12 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN            | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN               | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                  | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."VALID_FLAG"='1')
   9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
  13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2'))
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  14 - access("A"."CORP_ID"="G"."CORP_ID")
  16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  18 - access("H"."INDI_ID"="A"."INDI_ID")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")

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

执行下面的语句来接受SQL 概要文件

SQL>  execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2014080804', replace => TRUE,force_match => TRUE);

PL/SQL procedure successfully completed.

再来测试该语句

SQL> select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017';

 AAC001 AAC002               AAB301  AKF008       AKF010   AKF011    AKF012   AKF013  AKF014 
------- -------------------- ------- -----------  -------- ------- -------- -------- ------- 
  44499 430703198202280017   430701  4307000305   18000304 购药          19       19       0 
  44499 430703198202280017   430701  4307030186   14200513 购药          34       34       0 
  44499 430703198202280017   430701  4307000070   11535710 购药           7        7       0 
  44499 430703198202280017   430701  4307000211   13157523 购药          10       10       0 
  44499 430703198202280017   430701  4307000178   10504509 购药        37.2     37.2       0 
  44499 430703198202280017   430701  4307000025   14186783 购药         6.5      6.5       0 
  44499 430703198202280017   430701  4307000211   18855092 购药          51       51       0 
  44499 430703198202280017   430701  4307000025   23298689 购药          32       32       0 
  44499 430703198202280017   430701  4307000305   17251025 购药          20       20       0 
  44499 430703198202280017   430701  4307000211   11246538 购药        10.5     10.5       0 
  44499 430703198202280017   430701  4307000011   20015343 门诊          20       20       0 
  44499 430703198202280017   430701  4307000135   13248044 购药       103.2    103.2       0 
  44499 430703198202280017   430701  4307000070   17745955 购药          20       20       0 
  44499 430703198202280017   430701  4307000011   23548511 门诊        94.2     94.2       0 
  44499 430703198202280017   430701  4307000305   18000319 购药          16       16       0 
  44499 430703198202280017   430701  4307000025   20291585 购药         374      374       0 
  44499 430703198202280017   430701  4307000075   11425923 购药        11.8     11.8       0 
  44499 430703198202280017   430701  4307000089   23298593 购药       170.8    170.8       0 
  44499 430703198202280017   430701  4307000110   11548588 购药        28.5     28.5       0 
  44499 430703198202280017   430701  4307000011   18454938 门诊       105.8    105.8       0 
  44499 430703198202280017   430701  4307000075   11757756 购药       282.7    282.7       0 
  44499 430703198202280017   430701  4307000025   10545113 购药       340.8    340.8       0 
  44499 430703198202280017   430701  4307000285   17325032 购药        67.5     67.5       0 
  44499 430703198202280017   430701  4307000070   17341126 购药          87       87       0 
  44499 430703198202280017   430701  4307000211   17655418 购药          20       20       0 
  44499 430703198202280017   430701  4307000011   19042114 门诊       127.2    127.2       0 
  44499 430703198202280017   430701  4307000211   18070864 购药           6        6       0 
  44499 430703198202280017   430701  4307000011   23547574 门诊          36       36       0 

28 rows selected.

Elapsed: 00:00:00.01

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  1n2t3u0q0gmhz, child number 0
-------------------------------------
select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'

Plan hash value: 484693682

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |       |       |    25 (100)|          |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   251 | 53965 |    24   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |    28 |  4788 |    19   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                   |    28 |  4284 |    18   (6)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                   |    28 |  4116 |    17   (6)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4060 |    16   (7)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3108 |    10   (0)| 00:00:01 |
|*  9 |          HASH JOIN                    |                   |    28 |  2968 |     9   (0)| 00:00:01 |
|  10 |           TABLE ACCESS FULL           | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|  11 |           INLIST ITERATOR             |                   |       |       |            |          |
|  12 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN            | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN               | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                  | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / B@SEL$2
  10 - SEL$F5BB74E1 / F@SEL$2
  12 - SEL$F5BB74E1 / A@SEL$2
  13 - SEL$F5BB74E1 / A@SEL$2
  14 - SEL$F5BB74E1 / G@SEL$2
  15 - SEL$F5BB74E1 / D@SEL$2
  16 - SEL$F5BB74E1 / D@SEL$2
  17 - SEL$F5BB74E1 / E@SEL$2
  18 - SEL$F5BB74E1 / H@SEL$2
  19 - SEL$F5BB74E1 / C@SEL$2
  20 - SEL$F5BB74E1 / B@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      OPT_PARAM('optimizer_index_caching' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "F"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "A"@"SEL$2" ("MT_BIZ_FIN"."IDCARD" "MT_BIZ_FIN"."VALID_FLAG"
              "MT_BIZ_FIN"."PERS_TYPE" "MT_BIZ_FIN"."BIZ_TYPE"))
      NUM_INDEX_KEYS(@"SEL$F5BB74E1" "A"@"SEL$2" "IDX$$_429C0001" 3)
      INDEX(@"SEL$F5BB74E1" "G"@"SEL$2" ("BS_CORP"."CORP_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
      INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("BS_HOSP_LEVEL"."HOSP_LEVEL"))
      INDEX(@"SEL$F5BB74E1" "H"@"SEL$2" ("BS_INSURED"."INDI_ID"))
      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("BS_DISEASE"."CENTER_ID" "BS_DISEASE"."ICD"))
      INDEX(@"SEL$F5BB74E1" "B"@"SEL$2" ("MT_PAY_RECORD_FIN"."HOSPITAL_ID"
              "MT_PAY_RECORD_FIN"."SERIAL_NO"))
      LEADING(@"SEL$F5BB74E1" "F"@"SEL$2" "A"@"SEL$2" "G"@"SEL$2" "D"@"SEL$2" "E"@"SEL$2" "H"@"SEL$2"
              "C"@"SEL$2" "B"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "G"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "H"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."VALID_FLAG"='1')
   9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
  13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND (("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2')))
       filter(("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
  14 - access("A"."CORP_ID"="G"."CORP_ID")
  16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  18 - access("H"."INDI_ID"="A"."INDI_ID")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."INDI_ID"[NUMBER,22], "A"."IDCARD"[VARCHAR2,25], "A"."CENTER_ID"[VARCHAR2,10],
       "A"."NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "D"."HOSPITAL_NAME"[VARCHAR2,70],
       "A"."SERIAL_NO"[VARCHAR2,16], "F"."BIZ_NAME"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7],
       "A"."IN_DAYS"[NUMBER,22], SUM("B"."REAL_PAY")[22], SUM(CASE "B"."FUND_ID" WHEN '003' THEN
       "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" ELSE 0 END
       )[22], SUM(CASE  WHEN (("B"."FUND_ID"='999' OR "B"."FUND_ID"='003') AND
       ("B"."POLICY_ITEM_CODE"='S00' OR "B"."POLICY_ITEM_CODE"='S01' OR "B"."POLICY_ITEM_CODE"='C001' OR
       "B"."POLICY_ITEM_CODE"='C004''C006')) THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID"
       WHEN '003' THEN "B"."REAL_PAY" WHEN '999' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE
       "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" WHEN '201' THEN "B"."REAL_PAY" WHEN '301' THEN
       "B"."REAL_PAY" ELSE 0 END )[22]
   2 - "B"."POLICY_ITEM_CODE"[VARCHAR2,20], "B"."FUND_ID"[VARCHAR2,3], "B"."REAL_PAY"[NUMBER,22]
   3 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
       "D"."HOSPITAL_NAME"[VARCHAR2,70], "B".ROWID[ROWID,10]
   4 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
       "D"."HOSPITAL_NAME"[VARCHAR2,70]
   5 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
   6 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
   7 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
   8 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7]
   9 - (#keys=2) "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22],
       "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22],
       "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7]
  10 - "F"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_TYPE"[CHARACTER,2], "F"."BIZ_NAME"[VARCHAR2,20]
  11 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
       "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25],
       "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
  12 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
       "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."PERS_TYPE"[VARCHAR2,3],
       "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22],
       "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
  13 - "A".ROWID[ROWID,10], "A"."IDCARD"[VARCHAR2,25], "A"."PERS_TYPE"[VARCHAR2,3],
       "A"."BIZ_TYPE"[VARCHAR2,2]
  15 - "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
  16 - "D".ROWID[ROWID,10]
  20 - "B".ROWID[ROWID,10]

Note
-----
   - SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement


163 rows selected.

从SQL profile “SYS_SQLPROF_0151ed60f3d28000” used for this statement 这个信息就是知道已经使用了SQL概要文件
现在语句执行只要0.1毫秒

介绍
oracle优化器对一个sql语句检测所有可能的执行计划并选择一个成本值最小的,这里的成本代表了一个特定执行计划的资源使用情况.为了让优化器能准确的判断一个执行计划的成本它必须要关于sql语句要访问的所有对象(表或索引)的信息同时还要有运行sql语句的系统信息.

这些必要的信息通常称为优化器统计信息.理解和管理优化器统计信息是优化sql执行的关键.知道何时以及如何收集统计信息对于维护可以接受的性能来说至关重要.

这里将介绍在常见的oracle数据库场景中何时以及如何来收集统计信息.它包含以下内容:
怎样收集统计信息
何时收集统计信息
提高收集统计信息的效率
何时不收集统计信息
收集处理的统计信息

怎样收集统计信息
在oracle中收集统计信息的首选方法是使用提供了自动统计信息收集job.

自动统计信息收集job
对运行oracle autotask任务的一个预定义维护窗口期间对哪些丢失统计信息或统计信息过期的所有数据库对象收集统计信息,oracle内部很重视数据库对象的统计信息因此这此对象在进行处理前需要更新统计信息.自动统计信息收集job是使用dbms_stats.gather_database_stats_job_proc过程来实现的,它与dbms_stats.gather_*_stats过程使用相同的缺省参数.这个缺省值在大多数情况下是有效的.然而偶尔也需要改变这些统计信息收集参数的缺省值,可以通过dbms_stats.set_*_pref过程来进行修改.例如设置一个表中有5%的数据发生了改变而不是缺省值10%时就认会它的统计信息失效了.如果想要改变这个统计信息失效的阈值,可以使用dbms_stats.set_table_prefs过程来修改stale_percent参数.

sys@JINGYONG> begin
  2  dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','5');
  3  end;
  4  /

PL/SQL 过程已成功完成。

使用dbms_stats.set_table_prefs过程将表统计信息失效的阈stale_percent改变了5%.

手动统计信息收集
如果已经有一个完善的统计信息收集过程或者因为某些原因想要对特定用户方案禁用自动统计信息收集而只保留收集数据字典的统计信息.可以使用dbms_stats.set_global_prefs过程来改变autostats_target参数为oracle来替代auto.

sys@JINGYONG> begin
  2  dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
  3  end;
  4  /

PL/SQL 过程已成功完成。

用上面的代码改变自动统计信息收集job只自动收集数据字典统计信息.

为了手动收集统计信息你应该使用dbms_stats包,用它来替找过时的analyze命令.dbms_stats包提供多个
dbms_stats.gather_*_stats过程来收集用户方案对象,数据字典和固定对象的统计信息.理想情况下应该让这些过程除了用户方案名和对象名参数之外其它的参数值都使用缺省值.缺省参数值和自适应参数设置在大多数情况下已经足够了.

sys@JINGYONG> begin
  2  dbms_stats.gather_table_stats('SH','SALES');
  3  end;
  4  /

PL/SQL 过程已成功完成。

两个修改最频繁的参数是ESTIMATE_PERCENT和METHOD_OPT

ESTIMATE_PERCENT
在收集统计信息的过程最常见的问题是’使用什么样的抽样大小’与这个问题相关的设置是dbms_stats.gather_*_stats过程中的ESTIMATE_PERCENT参数.这个ESTIMATE_PERCENT参数判断用来计算统计信息所使用的行数百份比.当表中的所有行被处理时收集的统计信息是最准确的(比如100%抽样).然而抽样的样本越大收集操作的时间越长.因此使用怎样的抽样大小来提供及时准确的统计信息.

oracle11G之前的ESTIMATE_PERCENT
在oracle10g中,ESTIMATE_PERCENT的缺省值从100%变成了AUTO_SAMPLE_SIZE.这个AUTO_SAMPLE_SIZE的目的是让oracle在每次收集统计信息时来判断每一个表的合适的抽样大小.这将允许oracle自动地对每一个表改变其抽样大小但仍然能确保及时有效的收集统计信息.这种方法对于大多数表来说是一种可取的方法但是对于数据存在倾斜的表来说存在问题.当表中数据出现倾斜AUTO_SAMPLE_SIZE算法通常选择的抽样大小太小,在这种情况下最好的方法还是手动指定ESTIMATE_PERCENT参数的大小.

oracle11g中的ESTIMATE_PERCENT
oracle11g中引入一种新的hash-based抽样算法来提供精确的统计数据解决了精确和速度两个关键问题.它的精确度接近100%抽样大小的水平但是成本与10%抽样大小相当.这种新的算法只有当任何dbms_stats.gather_*_stats过程中的ESTIMATE_PERCENT参数设置为AUTO_SAMPLE_SIZE时才会使用.

下面的表格显示了一个早前使用1%抽样,100%抽样和AUTO_SAMPLE_SIZE抽样收集统计信息的结查.第一行比较运行的时间,后继的行将显示每次运行计算出来的L_ORDERKDY和L_COMMENT两个列不同值的数量(NDV)

-----------------------------------------------------------------------------------------------
                                 1% sample      auto_sample_size      100% sample
-----------------------------------------------------------------------------------------------
Elapse time (sec)                 797                 1908             18772
NDV for L_ORDERKEY Column       225000000         450000000            450000000
NDV for L_COMMENT Column        7244885           177499684            181122127
-----------------------------------------------------------------------------------------------

在这种情况下新的auto_sample_size算法比100%抽样执行的时间要快9倍且只比1%抽样执行的时间慢2.4倍,而提供的统计信息质量几乎接近100%抽样的水平(不足以改变执行计划).

从oracle11g开始强烈建议你使用estimate_percent参数的缺省值.如果你手动设置estimate_percent参数尽管你将它设置为100%它还是会使用旧的收集算法.
method_opt
在dbms_stats.gather_*_stats过程中到目前为止最有争议的参数就是method_opt.这个method_opt参数控制着在收集统计信息过程是否创建直方图.直方图是一种特殊类型的列统计信息提供关于表中列数据分布的详细信息.所以这就是为什么直方图存在争议的问题

直方图的创建会增加统计收集的时间和系统资源的使用但最大的问题是直方图与bind peeking功能的相互作用以及对near popular values基数评估的影响.

直方图与bind peeking
bind peeking对直方图的不利影响已经在oracle11g中通过引入自适应游标共享被淡化了但是直到今天仍然感受到它的影响.为了说明自适应游标共享是怎样解决这个问题的先来检查一个这个问题的原因.

oracle11g之前的直方图与bind peeking
在oracle11g之前当优化一个在where子句中包含绑定变量的语句时优化在第一次执行这个语句时会窥视这些绑定变量的值(在硬解析阶段).优化器然后会基于这个初始化的绑定变量值来判断执行计划.对于后续执行这个查询不会执行绑定变量窥视(没有硬解析发生),所以对于后面的这个语句的所有执行即使绑定变量发生了改变也会决定使用第一次设置的绑定变量值所产生的执行计划对于在表达式中使用绑定变量的列存在直方图这将有助于判断初始化绑定变量值的最优执行计划.因此对于相同的语句依赖于初始化硬解析时绑定变量的值执行计划可能会有所不同.

有两种方法可以避免这个问题:删除直方图并在将业停止收集直方图或禁用bind peeking绑定变量窥视.根据所有的sql是否都在使用绑定变量你可以判断哪一种方法更适合你的数据库.

禁止直方图的创建
如果你的环境中所有sql语句使用了绑定变量那么最好是删除存在的直方图并在将来的收集统计信息过程中禁止创建直方图.禁上直方图的创建能确保依赖于绑定变量的值的执行计划不会发生改变也会减少收集统计信息的时间.没有直方图优化器会假设列中不相同的值是均匀分布在所有行中的并当窥视sql语句中初始化绑定变量值时使用NDV(number distinct values)来判断基数的评估.

可以使用dbms_stats.delete_table_stats过程来删除统计信息中存在的直方图信息.

sys@JINGYONG> begin
  2  dbms_stats.delete_table_stats('SH','SALES');
  3  end;
  4  /

PL/SQL 过程已成功完成。

接下来可以通过使用dbms_stats.set_param过程来改变method_opt参数的缺省值来阻止将来生成直方图.这能确保
dbms_stats.gather_*_stats过程和自动统计信息收集job在将来都不会收集直方图信息.

sys@JINGYONG> begin
  2  dbms_stats.set_param(pname=>'METHOD_OPT',pval=>'FOR ALL COLUMNS SIZE 1');
  3  end;
  4  /

PL/SQL 过程已成功完成。

最后可以使用dbms_stats.gather_table_stats过程来对受影响的对象重新收集统计信息.

注意在oracle11g中通过使用dbms_stats.delete_column_stats和对直方图设置col_stat_type可以删除不想要的直方图而不用删除所有的直方图信息.也可以对单个表或者使用dbms_stats.set_table_prefs过程来对列禁止直方图的创建.你知道直方图也用于某些连接谓词而且删除直方图对连接谓词的基数评估会有影响.在这种情况下更安全的方法是禁用绑定变量窥视.

禁用绑定变量窥视
如果你的环境中有一些sql语句是使用绑定变量而有一些sql语句使用了literal values那么你应该禁用绑定变量窥视.通过禁用绑定变量窥视它将阻止优化器窥初始绑定变量值且将不使用直方图来进行基数评估.相反优化器将列中的不相同值是均匀分布在行中并使用NDV(number distinct values)来进行基数评估操作.这将对于使用绑定变量的语句使用一致的执行计划.但是如果sql语句使用literal values那么仍然能利用直方图来得到最优的执行计划.可以通过设置隐含参数_optim_peek_user_binds为false来禁用绑定变量窥视.

oracle11g中的直方图与绑定变量窥视
在oracle11g中优化器已经增强了允许多个版本的执行计划用于使用绑定变量的单个sql语句.这个功能就叫作自适应游标共享且依赖于对执行统计的监控来确保每一个绑定变量值使用正确的执行路径.

在第一次执行时优化器将窥视绑定变量值且基于绑定变量值的选择性来判断其执行计划,与oracle11g之前的版本一样.如果优化器认为最佳的执行计划可能依赖于绑定变量的值(例如,列上的直方图或者一个范围谓词,or,< ,>)这个游标将会被标记为bind sensitive.当一个游标被标记为bind sensitive.oracle将监控游标使用不同绑定值的行为来确定是否要使用一个不同的执行计划.

如果一个不同的绑定变量值在后继的执行中使用,优化器将使用相同的执行计划因为oracle一开始会假设游标能被共享.然而新的

绑定变量的执行统计会被记录并与之前绑定变量值的执行统计进行比较.如果oracle判断新的绑定变量值造成了操作的数据量明显不同那么对于新的绑定变量值在下一次执行时会进行硬解析且这个游标会被标记为bind-aware.每一个bind_aware游标与绑定变量的选择性范围有关因此游标只有在这个语句的绑定变量值在一个被认为可共享的范围之内才能被共享.

当另一个新的绑定变量值被使用时,优化器将会基于绑定变量值的选择性的相似度来找到一个它认为最好的一个游标.如果它不能找到一个游标,它将创建一个新的.如果执行计划的一个新的游标与一个已经存在的游标一样,那么两个游标将会在共享池中合并从而节省空间.游标的选择性范围为了包含新绑定变量值的选择性将会有所增加.

通过允许对单个sql语句存在多个执行计划,在oracle11g中直方图对于使用绑定变量的语句不再有负面影响.

直方图和near popular values
当优化器遇到一个where子句中谓词列上有直方图,它将基于literal value的出现频率来进行基数评估.例如假设在sh用户下的customers表中的cust_city_di列上有一个高度平衡的直方图且有一个使用cust_city_id=51806的查询.优化器首先会检查这个直方图有51806作为它的end point有多少个桶.在这种情况下,endpint是51806的桶有136,137,138和139(可以查看user_histograms).因为endpoint的值有两个或多个桶要被考虑为出现频繁的优化器将使用下面的公式来进行基数评估:
(Number of bucket endpoints / total number of buckets) * number of rows in the table
在这种情况下:4/254*55500=874

sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=51806;


执行计划
----------------------------------------------------------
Plan hash value: 296924608

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   382   (3)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   874 |  4370 |   382   (3)| 00:00:04 |

--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_CITY_ID"=51806)

然而如果谓词是cust_city_id=52500,它对于任何桶来说都不是一个endpoint那么优化器会使用一个同的公式来进行基数评估.对于endpoint值只在一个桶出现或者任何桶中都没有这个endpoint时优化器会使用下面的计算公式:
density * number of rows in the table,

density的值可以在user_tab_col_statistics中看到,它的值从oracle10.2.0.4以后优化器将不再使用.记录这个值是为了向后兼容,在oracle9i和oracle10g前期的版本中会使用这个值.此外如果参数optimizer_features_enable设置的版本小于10.2.0.4,那么视图中的density仍然会被使用.

sys@JINGYONG> select column_name,density from dba_tab_col_statistics where owner
='SH' and table_name='CUSTOMERS';

COLUMN_NAME                       DENSITY
------------------------------ ----------
CUST_ID                        .000018018
CUST_FIRST_NAME                .000769231
CUST_LAST_NAME                 .001101322
CUST_GENDER                            .5
CUST_YEAR_OF_BIRTH             .013333333
CUST_MARITAL_STATUS            .090909091
CUST_STREET_ADDRESS            .000019629
CUST_POSTAL_CODE               .001605136
CUST_CITY                      .001612903
CUST_CITY_ID                   .002179391
CUST_STATE_PROVINCE            .006896552
CUST_STATE_PROVINCE_ID         .000009009
COUNTRY_ID                     .000009009
CUST_MAIN_PHONE_NUMBER         .000019608
CUST_INCOME_LEVEL              .083333333
CUST_CREDIT_LIMIT                    .125
CUST_EMAIL                     .000588582
CUST_TOTAL                              1
CUST_TOTAL_ID                  .000009009
CUST_SRC_ID                             0
CUST_EFF_FROM                           1
CUST_EFF_TO                             0
CUST_VALID                             .5

已选择23行。

sys@JINGYONG> select column_name,num_buckets,histogram from dba_tab_col_statisti
cs where owner='SH' and table_name='CUSTOMERS' and column_name='CUST_CITY_ID';

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
CUST_CITY_ID                           254 HEIGHT BALANCED


sys@JINGYONG> show parameter optimzer_features_enable
sys@JINGYONG> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.1
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;


执行计划
----------------------------------------------------------
Plan hash value: 296924608

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   382   (3)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |    66 |   330 |   382   (3)| 00:00:04 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_CITY_ID"=52500)

现在将optimizer_features_enable设置为10.2.0.3

sys@JINGYONG> alter session set optimizer_features_enable='10.2.0.3';

会话已更改。

sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;


执行计划
----------------------------------------------------------
Plan hash value: 296924608

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   382   (3)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   121 |   605 |   382   (3)| 00:00:04 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_CITY_ID"=52500)

现在的基数是121=55500*.002179391,CUST_CITY_ID的density为.002179391

这些nearly popular值被归类为non-popular values使用与non-popular values相同的计算公式.例如,如果谓词是
cust_city_id=52114,那么它的评估基数将是66行.与non-popular值52500的基数一样,但是cust_city_id=52114实际上有227行记录.

sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;


执行计划
----------------------------------------------------------
Plan hash value: 296924608

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   382   (3)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |    66 |   330 |   382   (3)| 00:00:04 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_CITY_ID"=52114)

sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;

  COUNT(*)
----------
       227

唯一能让优化器意识到这些near popular values的方法是使用动态抽样.动态抽样在优化一个sql语句时会收集额外的statement-specific对象统计信息.在这个例子中,动态抽样提示加入到了查询中且优化器会得到一个更准确的基数评估值.

sys@JINGYONG> select /*+ dynamic_sampling(a 2) */ count(a.cust_id) from sh.custo
mers a where a.cust_city_id=52114;


执行计划
----------------------------------------------------------
Plan hash value: 296924608

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   382   (3)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |    246 |   410 |   382   (3)| 00:00:04 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."CUST_CITY_ID"=52114)

Note
-----
   - dynamic sampling used for this statement (level=2)

使用动态抽样可以提高高度平衡直方图中non-popular value的基数评估

在上面已经讨论了在oracle10g中使用直方图可能存的问题和可能的解决方法.建议从oracle11g开始收集统计信息时使用参数METHOD_OPT的缺省值且利用自适应游标.

如果想手动设置method_opt参数值不使用缺省值要确保只对需要直方图的列进行设置.将method_opt设置为for all columns size 254将会使oracle对每一个列都收集直方图信息.这对于收集统计信息来说是不必要的会增加运行时间和浪费系统资源,也会增加存储这些统计信息的空间.

还要避免将method_opt设置为for all index columns size 254它使oracle对存过索引的每一个列收集直方图信息,也会浪费系统资源.这个设置还有一个副作用就是会阻止oracle对哪些不存在索引的列收集基本的列统计信息.

pending statistics
当决定改变dbms_stats_gather_*_stats过程的参数缺省值时强烈建议在生产系统中修改之前先验证这些改变.如果没有一个完整的测试环境应该使用pending statistics.使用pending statistics代替常用的数据字典表,存储在pending表中的统计信息在它们被发和被系统使用之前可以以一种受控的方式来启用和测试.为了激活pending统计信息的收集需要对希望创建pending统计信息的对象使用dbms_stats.set_*_prefs过程将参数publish从缺省值true改变false.下面的例子中对sh用户下的sales表启用pending统计信息并对sales表收集统计信息.

sys@JINGYONG> begin
  2  dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL 过程已成功完成。

通过将publish设置为false来启用pending统计信息

正常的收集对象统计信息

sys@JINGYONG> begin
  2  dbms_stats.gather_table_stats('SH','SALES');
  3  end;
  4  /

PL/SQL 过程已成功完成。

对于这些对象收集的统计信息可以查询*_tab_pending_stats视图来显示:

sys@JINGYONG>select * from dba_tab_pending_stats where owner='SH';
 
OWNER                          TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------- ----------- -------------
SH                             SALES                                                                                            918843       1769          29      918843 2013-12-19 9:
SH                             SALES                          SALES_1995                                                             0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_1996                                                             0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_H1_1997                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_H2_1997                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q1_1998                                                      43687         90          29       43687 2013-12-19 9:
SH                             SALES                          SALES_Q1_1999                                                      64186        121          29       64186 2013-12-19 9:
SH                             SALES                          SALES_Q1_2000                                                      62197        119          29       62197 2013-12-19 9:
SH                             SALES                          SALES_Q1_2001                                                      60608        119          30       60608 2013-12-19 9:
SH                             SALES                          SALES_Q1_2002                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q1_2003                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q2_1998                                                      35758         76          29       35758 2013-12-19 9:
SH                             SALES                          SALES_Q2_1999                                                      54233        103          29       54233 2013-12-19 9:
SH                             SALES                          SALES_Q2_2000                                                      55515        109          30       55515 2013-12-19 9:
SH                             SALES                          SALES_Q2_2001                                                      63292        119          30       63292 2013-12-19 9:
SH                             SALES                          SALES_Q2_2002                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q2_2003                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q3_1998                                                      50515         95          29       50515 2013-12-19 9:
SH                             SALES                          SALES_Q3_1999                                                      67138        120          29       67138 2013-12-19 9:
SH                             SALES                          SALES_Q3_2000                                                      58950        110          30       58950 2013-12-19 9:
SH                             SALES                          SALES_Q3_2001                                                      65769        124          29       65769 2013-12-19 9:
SH                             SALES                          SALES_Q3_2002                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q3_2003                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q4_1998                                                      48874        108          29       48874 2013-12-19 9:
SH                             SALES                          SALES_Q4_1999                                                      62388        114          29       62388 2013-12-19 9:
SH                             SALES                          SALES_Q4_2000                                                      55984        106          30       55984 2013-12-19 9:
SH                             SALES                          SALES_Q4_2001                                                      69749        136          29       69749 2013-12-19 9:
SH                             SALES                          SALES_Q4_2002                                                          0          0           0           0 2013-12-19 9:
SH                             SALES                          SALES_Q4_2003                                                          0          0           0           0 2013-12-19 9:
 
29 rows selected
 

可以通过一个alter session命令来设置初始化参数optimizer_use_pending_stats为true来使用pending统计信息.在启用pending统计信息之后任何在该会话运行的sql将使用这些新的没有发布的统计信息.对于在工作负载下的所访问的表没有pending统计信息时优化器将使用标准数据字典表中的当前统计信息.当你验证这些pending统计信息后可以使用dbms_stats.publish_pending_stats过程来发布.

何时收集统计信息
为了选择一个最佳的执行计划优化器必须要有有代表性的统计信息,有代表性的统计信息并不是最新的统计信息但是这组统计信息能帮助优化器判断在执行计划中每一个操作步骤所期待的正确的行记录数.

自动统计信息收集job
在一个预定义的维护窗口中oracle会自动对哪些丢失统计信息或者统计信息失效的所有对象收集统计信息(每个工作日的晚上10点到零晨2点和每个周末的6点到零晨2点).

可以使用企业管理器或使用dbms_scheduler和dbms_auto_task_admin包来改变这个维护窗口.

如果已经有一个完善的统计信息收集过程或者如果因为某些原因想要禁用自动统计信息收集可以禁用收集任务:

sys@JINGYONG> begin
  2  dbms_auto_task_admin.disable(
  3  client_name=>'auto optimizer stats collection',
  4  operation=>null,
  5  window_name=>null);
  6  end;
  7  /

PL/SQL 过程已成功完成。

手动统计信息收集
如果计划手动维护优化器统计信息将需要判断何时进行收集.

基于失效统计,自动收集job或者系统中加载新数据的时间你能判断何时来收集统计信息.如果基本数据没有发生明显的改变不建议不断的重新收集统计信息这样只会浪费系统资源.

如果数据在一个预定义的ETL或ELT job只加载到系统中那么统计信息收集操作应该作为这个过程的一部分被调度.注意如果使用分区交换加载并希望利用增量统计信息将需要在交换过程完成后收集统计信息.

然而如果系统中有大量的联机事务只插入少量的数据但是这些操作每天都会发生,你将需要判断何时你的统计信息将会失效然后触发统计信息收集job.如果你计划依赖user_tab_statistics中的stale_stats列来判断统计信息是否失效你应该能意识到这些信息每天及时更新.如果需要更多更及时的信息比如你的表什么时候执行过DML操作你将需要查看user_tab_modifications视图,它会显示每一个表上执行的insert,update,delete操作,表是否执行过truncated并计算自己是否已经失效.需要注意这些信息是否定时的从内存中自动更新.如果需要最新的信息需要使用dbms_stats.flush_database_monitoring_info函数来手动刷新.

阻止超出范围的条件
不管你是使用自动统计信息收集job还是手动收集统计信息,如果终端用户在统计信息收集之前开始查询新插入的数据,即使只有不到10%的数据发生了变化也可能由于失效的统计信息得到一个次优的执行计划.发生这种问题最常见的原因是where子句中谓词提供的值超出了最小/最大列统计信息所能表示的范围.这通常称为超出范围的错误.

这种情况在分区表中很常见.一个新分区刚添加到一个存在的范围分区表中且记录刚被插入到分区中.在对这个新分区收集统计信息之前终端用户就开始查询这些新的数据.对于分区表,可以使用dbms_stats.copy_table_stats过程(从oracle10.2.0.4开始可以使用)来阻止超出范围的条件表达式.这个过程将复制原分区数据的统计信息为新创建分区的统计信息.它能复制依赖对象的统计信息:列,本地(分区)索引等等.直到对分区收集统计信息之前复制的统计信息只能作为临时的解决方法来使用.复制的统计信息不能代替真实收集的统计信息.

注意通常dbms_stats.copy_table_stats只能调整分区统计信息不能调整全局或表级别的统计信息.如果想在复制统计信息时对分区列进行全局级别的更新需要将dbms_stats.copy_table_stats中的flags参数设置为8.

对于非分区表你能通过dbms_stats.set_column_stats过程来手动设置列的最大值.通常这种方法不建议它并不能代替真实的收集的统计信息.

提高收集统计信息的效率
随着数据量的增长和维护窗口的缩短能及时的收集统计信息是很重要的.oracle提供了多种方法来提高统计信息收集的速度.使用并行用于收集统计信息的几种并行方法
内部对象并行
外部对象并行
内部对象并行与外部对象并行的组合

内部对象并行
内部对象并行是由dbms_stats.gather_*_stats过程的degree参数来控制的.degree参数控制着用于收集统计信息的并行服务器进程的数量.

通常oracle使用数据字典表中并行属性的值作为指定并行服务器进程的参数值.在oracle数据库中所有的表都有一个degree属性缺省值为1.对要收集统计信息的大表设显示地设置这个参数能提高统计信息收集的速度.

你也可以设置degree为auto_degree.oracle将基于一个对象的大小自动判断一个合适的并行服务进程个数来收集统计信息.这个值的范围在1-小对象(串行操作)到大对象的default_degree((PARALLEL_THREADS_PER_CPU X CPU_COUNT)之间.

你将会注意到对一个分区表设置degree这意味着对每一个分区使用多个并行服务器进程来收集统计信息但是不能同时对不同的分区收集统计信息.统计信息只能在一个分区收集完之后才能收集下一个分区.

外部对象并行
在oracle11.2.0.2中,外部对象并行被引入且由global statistics gathering preference concurrent来控制.当concurrent设置为true时,oracle将使用oracle job作业调度和高级队列组来创建和管理多个统计信息收集job并发执行.通过oracle来完全利用多个cpu来对多个表和(子)分区并发的收集统计信息来减小整个统计信息收集的时间.

活动并行统计信息收集job的最大个数是由job_queue_processes参数来控制的.job_queue_processes缺省值设置为1000.这通常对于并行统计信息收集操作来说太高了尤其是在并行执行也在使用时更是如此.一个最有效的值应该是总cpu核数的2倍(在rac中这是每一个节点的参数值).你需要确在系统级别设置这个参数(alter system命令或init.ora文件)而不是在会话级别(alter session).

内部和外部并行的组合
在一个并行统计收集操作中的每一个统计信息收集job都能以并行的方式来执行.将并行统计收集和并行执行组合起来能大大减小收集统计信息的时间.

当使用并行执行作为一个并行统计信息收集操作的一部分时你应该禁用parallel_adaptive_multi_user初始化参数来阻止并行job被降级为串行操作.它应该在系统级别来禁用而不是在会话级别禁用这个参数:

sys@JINGYONG> alter system set parallel_adaptive_multi_user=false;

系统已更改。

增量统计信息
分区表的统计信息收集是由表级别(global statistics)和(子)分区级别的统计信息收集操作组成的.如果一个分区表的incremental preference设置为true,dbms_stats.gather_*_stats中参数granularity的值包含global和estimate_percent设置为auto_sample_size,oracle将会通过扫描这些已经被添加或被修改的分区来获得全局级别的统计信息而不是整个表的.

增量全局统计信息是由表中每个分区存储的概要计算出来的.一个概要是这个分区和分区中列的统计信息的元数据.聚合分区级的统计信息和每个分区的概要信息将能精确的生成全局级别的统计信息因此消除了需要扫描整个表的操作.当一个新的分区添加到表中,你仅仅需要对这个新的分区进行统计信息收集而已.表级别的统计信息将会使用新分区的概要信息和已经存的分区的概要信息来自动和精确的计算出来.

注意当增量统计信息被启用时分区统计信息不从子分区统计信息中进行聚合操作.

何时不收集统计信息
尽管oracle需要精确的统计信息来选择一个最优的执行计划,有些情况下收集统计信息是很困难的,很昂贵的或者是不能及时完成的所以要有一和睦替代的策略.

volatile表
一个volatile表是随着时间的变化数据量会发生很大改变的表.例如,一个订单队列表,这个表在一天开始的时候是空的,随着时间的推移订单将会填满这个表.当被处理的一个订单从表中删除时所以这一天结束时这个表会被再次清空.

如果你依赖自动统计信息收集job来维护象这样的表的统计信息那么这些表显示的统计信息总是空的因为收集job是在晚上.然而在当天工作期间这个表可能有成千上万行记录.

对于这样的表最好是在白天收集一组有代表性的统计信息并锁定这些信息.锁定这些统计信息将阻止自动统计信息收集job来覆盖它们.优化器在优化sql语句之前在编译sql语句时会使用动态抽样对表收集基本的统计信息.尽管通过动态抽样产生的统计信息质量不高或者不象使用dbms_stats包收集的统计信息那样完整但在大多数情况下已经够用了.

全局临时表
全局临时表在应用程序上下文中经常用来存储中间结果.一个全局临时表对于有合理权限的所有用户共享它的定义,但是数据只在各自的会话中可见.直到数据被插入表中之前是不分配物理存储的.一个全局临时表可能是transaction specific(提交时删除行(或session-specific(提交时保存行).对一个transaction specific的表收集统计信息将导致对这个表进行truncate操作.相反,可以对全局临时表收集统计信息.然而统计信息的收集将仅仅基于session-private数据内容进行收集但是这些统计信息将能被访问这个表的所有会话使用.

如果有一个全局临时表持续有行数据且每一个会话将有相同的数据量和相同的数值那么应该在一个会话中收集一组有代表性的统计信息并锁定它们防止其它会话将其覆盖.注意自动统计信息收集job是不会收集全局临时表的统计信息.

中间工作表
中间工作表是典型的一个ELT过程或者一个复杂事务的一部分.这些表只会写一次,读一次然后truncate或者delete.在这种情况下收集统计信息成本超过了它所带来的好处,因为统计信息只能用一次.相反动态抽样在这种情况下更有用.建议锁定中间工作表的统计信息来持久的阻止自动统计信息收集job来对它们收集统计信息.

收集其它类型的统计信息
因为现在只支持基于成本的优化器,数据库中所有的表需要有统计信息,包含所有的数据字典表(sys,system用户所拥有的表和内置在system和sysaux表空间中的表)和通过动态v$性能视图使用的x$表.

数据字典统计信息
数据字典表的统计信息是由自动统计信息收集job在晚维护窗口进行收集的.强烈建议你允许oracle自动统计信息收集job来维护数据字典统计信息即使在你关闭对主应用程序方案关闭自动统计信息收集的情况下.可以使用dbms_stats.set_global_prefs过程

将autostats_target从auto改成oracle

sys@JINGYONG> begin
  2  dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
  3  end;
  4  /

PL/SQL 过程已成功完成。

固定对象统计信息
自动统计信息收集job不会收集固定对象的统计统计信息.当优化统计信息丢失时不象其它的数据库表对于sql语句中调用X$表是不能自动使用动态抽样的.如果它们的统计信息丢失优化器会使用预先定义的缺省统计信息.这些缺省的统计信息可能没有代表性且可能导致选择次优的执行计划,在系统中可能会导致严重的性能问题.如果是这个原因造成性能问题强烈建议你手动收集固定对象的统计信息.

可以使用dbms_stats.gather_fixed_objects_stats过程来收集固定对象的统计信息.因为在系统如果存在一个有代表性的工作负载收集x$这些固定对象的统计信息是很重要的.在大型系统中由于收集固定对象统计信息需要额外的资源所以对固定对象收集统计信息不总是可行.如果不能在负载高峰期间收集固定对象的统计信息那么应该在系统负载降低之后对三种关键类型的固定对象表收集统计信息:
structural data–比如controlfile contents
Session based data – 比如 v$session, v$access
Workload data -比如 v$sql, v$sql_plan
建议当主数据库或应用程序升级后,实现新的模块或者改变数据库的配置后重新收集固定对象统计信息.例如,如果增加SGA的大小包含缓冲区缓存和共享池信息的x$表会显著的发生改变,比如v$buffer_pool或v$shared_pool_advice视图使用的x$表.

系统统计信息
系统统计信息能让优化器通过使用执行这个语句相关的实际系统硬件信息,比如,cpu速度和IO性能,来在执行计划中对每一个步骤获得更精确的成本值.系统统计信息缺省情况下是启用的,它使用缺省值自动初始化,这些值对于大多数系统来说是有代表性的.

小结
为了让优化器准确的判断执行计划中的成本它必须有这个语句所访问的所对象的精确的统计信息和运行sql语句所在系统的系统统计信息.这里介绍了为什么必须要有统计信息,怎样使用统计信息和各种收集统计信息的方法.

通过使用自动统计信息收集job和其它收集技术相结合能让DBA维护一组精确的统计信息来确保优化器总是有必要的信息来选择最优的执行计划.一旦一个统计信息收集策略已经在使用时,要改变这个策略应该以一种受控的方式来利用一些关键特性比如pending统计信息来确保不会对程序性能造成影响.