使用oradebug dump processstate 来诊断enq: TX – row lock contention

朋友的应用程序在年度结转时调用存储过程时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 优化统计数据之直方图(histograms)

直方图是一种按数据出现的频率来进行分类存储的方法.在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,下面的图表显示了这些数据是如何存储在直方图中的:
1 
基于上面的图表可以得出以下信息:
直方图的桶数比列中的不同值的个数小
因为我们指定了直方图的桶数是5,所以整个数据集除了最后一个桶(在这里只有3个值)其它按相同的大小分配到每一个桶中.
每一个桶中的endpoints和第一个桶中的first point被标记因为它们有特殊意义.
数据3被标记为红色,它是一种特殊情况它的endpoint出现在多个桶中.
下面的图表是直方图的另一种显示方式:
2 

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

下面我们来对列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是一个特殊它显示的是列中的最小值(高度平衡直方图).

基于CBO优化器谓词选择率的计算方法

选择率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进行一些端点值调整的方式来计算谓词选择率.对于包含绑定变量的区间谓词来说,优化器使用硬编码常量作为选择率优化器通过使用类似于计算独立事件的联合概率方式的公式来计算联合谓词的选择率.如果列中包含的数据集并不是相互独立的 话,在选择率的计算方面将会出现误差,从而导致基数出现误差.

获取当前所有会话等待事件信息

select s.sid,s.username,s.event,s.wait_time,s.state,s.seconds_in_wait,p.PROGRAM,s.MACHINE,
(select c.SQL_FULLTEXT from v$sqlarea c where c.SQL_ID=s.SQL_ID) sql_fulltext,
(select c.BIND_DATA from v$sqlarea c where c.SQL_ID=s.SQL_ID) BIND_DATA,s.SQL_ID,
s.P1TEXT,s.p1,s.p1raw,s.P2TEXT,s.p2,s.P2RAW,s.P3TEXT,s.P3,s.P3RAW
from v$session s,v$process p
where s.username is not null and s.PADDR=p.ADDR
and s.event not like’%SQL*Net%’
order by s.wait_time desc

获取数据库每天的排序统计数据脚本

set pages 9999;
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .9999

select
to_char(sn.snap_time,’day’) DAY,
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_dsk
from
stats$sysstat oldmem,
stats$sysstat newmem,
stats$sysstat newdsk,
stats$sysstat olddsk,
stats$snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.name=’sorts (memory)’
and newmem.name=’sorts (memory)’
and olddsk.name=’sorts (disk)’
and newdsk.name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.snap_time,’day’);

select
to_char(sn.begin_interval_time,’day’) DAY,
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_dsk
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_sysstat newdsk,
dba_hist_sysstat olddsk,
dba_hist_snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.stat_name=’sorts (memory)’
and newmem.stat_name=’sorts (memory)’
and olddsk.stat_name=’sorts (disk)’
and newdsk.stat_name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.begin_interval_time,’day’);

Proudly powered by WordPress | Indrajeet by Sus Hill.