Oracle数据库里什么情况下select操作会hang住

我们都知道在Oracle数据库里是“读不阻塞写,写不阻塞读”,那么我们可不可以认为在正常情况下,select操作是怎样都能执行,始终不会被hang住的呢?注意我这里提到的是正常情况下,不包括那些由于latchhold住、或者bug等相关异常所导致的select操作被hang住的情况。

 

答案是:不可以这样认为的。

 

我们来举一个反例。

首先我们来分析一下在sql硬解析时在相关表对象上library cache lock的持有情况。这里我用到了10049事件,用10049事件,最重要的就是要知道如何设置它所对应的level值。

 

10049level值可能会有如下一些组合:

#define KGLTRCLCK  0×0010                       /* trace lock operations */

#define KGLTRCPIN  0×0020                       /* trace pin operations  */

#define KGLTRCOBF  0×0040                       /* trace object freeing  */

#define KGLTRCINV  0×0080                       /* trace invalidations   */

#define KGLDMPSTK  0×0100                  /* DUMP CALL STACK WITH TRACE */

#define KGLDMPOBJ  0×0200                  /* DUMP KGL OBJECT WITH TRACE */

#define KGLDMPENQ  0×0400                 /* DUMP KGL ENQUEUE WITH TRACE */

#define KGLTRCHSH  0×2000                          /* DUMP BY HASH VALUE */

 

这里因为我要跟踪sql硬解析时相关表对象的library cache lock的持有情况,所以这里level值取0x0210=0x0200|0x0010,即这里level值取528

SQL> select to_number(‘210′,’XXXX’) from dual;

 

TO_NUMBER(‘210′,’XXXX’)

———————–

                    528

 

先在11.2.0.1里使用一下10049事件:

C:\Documents and Settings\cuihua>sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6 27 21:39:37 2012

 

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

 

SQL> conn / as sysdba;

已连接。

 

SQL> oradebug setmypid

已处理的语句

 

SQL> oradebug event 10049 trace name context forever,level 528

已处理的语句

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

———-

        14

 

SQL> oradebug tracefile_name

c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2292.trc

 

c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2292.trc没有任何内容

看起来似乎是10049事件对11gR2无效或者Oracle改变了10049事件在11gR2中的level的定义(这个我不确定)。

 

我们换一个10gR2的版本:

SQL> select * from v$version;

 

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 – Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

 

SQL> oradebug setmypid

已处理的语句

 

SQL> oradebug event 10049 trace name context forever,level 528

已处理的语句

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

———-

        13

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc

 

从上述trace文件d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc中从前到后可以看到如下内容

KGLTRCLCK kglget     hd = 0x25788788  KGL Lock addr = 0x317954E8 mode = N

LIBRARY OBJECT HANDLE: handle=25788788 mutex=2578883C(0)

name=select count(*) from scott.emp

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kgllkdl    hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kgllkdl    hd = 0x25788788  KGL Lock addr = 0x317954E8 mode = N

LIBRARY OBJECT HANDLE: handle=25788788 mutex=2578883C(1)

name=select count(*) from scott.emp

 

即针对上述cursor是以NULL模式持有library cache lock

针对表scott.emp是以share模式持有library cache lock

也就是说,只要我事先以exclusive模式在表scott.emp上持有library cache lock,那么后续的以硬解析方式执行的针对该表的所有sql(包括select语句)都将被hang住。

 

现在我们来测一下对一个表增加一个主键时的library cache lock的持有情况。

SQL> create table t2 as select * from emp;

 

Table created

 

SQL> select count(*) from t2;

 

  COUNT(*)

———-

        13

 

SQL> conn / as sysdba;

已连接。

 

SQL> oradebug setmypid

已处理的语句

 

SQL> oradebug event 10049 trace name context forever,level 528

已处理的语句

 

SQL> alter table scott.t2 add constraint PK_T2 primary key (EMPNO);

 

表已更改。

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc

 

从这个trace文件d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc中我们可以看出对表t2library cache lock的先后持有模式为:

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

即大部分时间library cache lock的持有模式都是N,只有在一头一尾的时候才是X

但请注意这种情况下select操作是会被hang住的。

因为一开头的Xkglget,结尾才kgllkdlkgllkdl大致是kgl lock delete的意思,表示释放相应的library cache lock),并且它们的KGL Lock addr相同:

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

这也就意味着在添加主键的整个过程中,Oracle始终会以exclusive模式在表scott.t2上持有library cache lock,直到最后主键添加完毕了才释放。

所以在win32上的10.2.0.1中,在添加主键的过程中会一直阻塞查询(select)操作。

 

我们来测一下,同时开3session

Session 1:

SQL> create table t3(id number);

 

Table created

 

SQL> declare

  2    i number;

  3  begin

  4    for i in 1..3000000 loop

  5     insert into t3 values (i);

  6    end loop i;

  7    commit;

  8  end;

  9  /

 

PL/SQL procedure successfully completed

 

Session 2:

SQL> select * from v$mystat where rownum<2;

 

       SID STATISTIC#      VALUE

———- ———- ———-

       138          0          1

      

session 1中开始执行添加主键操作:

Session 1:

SQL> alter table scott.t3 add constraint PK_T3 primary key (id);

……开始执行

 

转到session 2执行查询操作:

Session 2

SQL> select * from t3 where rownum<10;

……这里hang住了

 

转到session 3并执行对session2的等待事件的查询:

Session 3

SQL> select t.event,t.state,t.seconds_in_wait from v$session t where sid=138;

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      8

 

SQL> select t.event,t.state,t.seconds_in_wait from v$session t where sid=138;

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      9

 

SQL> select t.event,t.state,t.seconds_in_wait from v$session t where sid=138;

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      11

 

从中可以看到session 2在等待library cache lock,同时它的STATEwaitingSECONDS_IN_WAIT的值在递增。

这就验证了我们的结论:win32上的10.2.0.1中,在对表增加主键的过程中会一直阻塞对这个表的查询(select)操作。

 

现在我们再问一个问题:是不是所有对表的DDL操作,在DDL操作的执行过程中都会阻塞对这个表的select操作?

 

答案是:不是这样的。

 

我们来举一个反例。

现在我们来测一下对表drop一个columnlibrary cache lock的持有情况:

SQL> desc t1;

Name           Type          Nullable Default Comments

————– ————- ——– ——- ——–

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                        

STATUS         VARCHAR2(7)   Y                        

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

    592951

 

同时开两个session

session 1中打开10049事件后dropt1的列object_type:

Session 1

SQL> conn / as sysdba;

已连接。

 

SQL> oradebug setmypid

已处理的语句

 

SQL> oradebug event 10049 trace name context forever,level 528

已处理的语句

 

SQL> alter table scott.t1 drop column OBJECT_TYPE;

 

表已更改。

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc

 

session 2session 1执行drop column操作的同时查询表t1结果是select操作并没有被hang且能看到正在被drop的列object_type:

Session 2

SQL> select owner,object_name,object_type from t1 where rownum<10;

 

OWNER                          OBJECT_NAME                    OBJECT_TYPE

—————————— —————————— ——————-

SYS                            CON$                           TABLE

SYS                            I_COL2                         INDEX

SYS                            I_USER#                        INDEX

SYS                            C_TS#                          CLUSTER

SYS                            I_OBJ#                         INDEX

SYS                            I_CON2                         INDEX

SYS                            IND$                           TABLE

SYS                            BOOTSTRAP$                     TABLE

SYS                            UET$                           TABLE

 

9 rows selected

 

session 1所产生的trace文件d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc中我们可以看出对表t1library cache lock的先后持有模式为

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = N

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = X

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = X

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

即大部分时间对表scott.t1library cache lock的持有模式都是S,最后才是X,所以这就可以解释为什么在对表scott.t1执行drop column操作的时候对它的select语句能够同时执行。

trace文件来看,drop column并不是不会阻塞select操作,只是阻塞的时间点要恰好是OracleX模式持有library cache lock

 

最后我们来测一下对一个表增加一个unique constraintlibrary cache lock的持有情况

SQL> conn / as sysdba;

已连接。

 

SQL> oradebug setmypid

已处理的语句

 

SQL> oradebug event 10049 trace name context forever,level 528

已处理的语句

 

SQL> alter table scott.t2 add constraint UK_T2_EMPNO unique (EMPNO, ENAME);

 

表已更改。

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5240.trc

 

从这个trace文件中我们可以看出对表scott.t2library cache lock的先后持有模式为:

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B19C8C mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B19C8C mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

即大部分时间都是N,一头一尾才是X,这个和添加主键操作一样,在此不再赘述。

 

结论:不要随便在生产环境对大表执行DDL操作(如添加唯一性约束等),可能会导致针对这个表的所有sql(包括select操作)在执行DDL操作的时间段都hang住。

 


CPU cost对全表扫描成本的影响

Oracle在未引入CPU cost model之前,它会假设如下两个事实是成立的:

1single-block reads are just as expensive as multiblock reads

2、全表扫描的时候,会根据MBDivisormultiblock read divisor)来计算全表扫描的成本

 

9i里,MBDivisor会基于db_file_multiblock_read_count的值的变化而变化,一个大致的估算公式为:

MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

而基于MBDivisor全表扫描的成本的估算公式为:

tsc cost = Num Blocks / MBDivisor

 

我们来看一个实际计算的例子:

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

PARAMETERS USED BY THE OPTIMIZER

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

OPTIMIZER_FEATURES_ENABLE = 9.2.0

OPTIMIZER_MODE/GOAL = Choose

……省略显示其他内容

DB_FILE_MULTIBLOCK_READ_COUNT = 16

 

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

BASE STATISTICAL INFORMATION

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

Table stats    Table: OPFQ_QUE_ITM   Alias: OPFQ_QUE_ITM

  PARTITION [4]    CDN: 1338983  NBLKS:  32408  AVG_ROW_LEN:  105

  TOTAL ::  CDN: 1338983  NBLKS:  32408  AVG_ROW_LEN:  105

 

TABLE: OPFQ_QUE_ITM     ORIG CDN: 1338983  ROUNDED CDN: 7114  CMPTD CDN: 7114

  Access path: tsc  Resc:  3118  Resp:  3118

 

这里全表扫描的成本Resc3118,这里tsc表示tablescanResc表示resource cost

Resp表示response cost,意思是指在有并行情况下的成本,这里没有使用并行表扫描,所以RespResc相等。

我们来看一下上述3118是怎样算出来的:

Num Blocks这里为32408

DB_FILE_MULTIBLOCK_READ_COUNT16,所以MBDivisor10.3952

 

SQL> select 1.6765 * power(16, 0.6581) from dual;

 

1.6765*POWER(16,0.6581)

———————–

       10.3952495216659

 

Num BlocksMBDivisor带入上述公式:

tsc cost = Num Blocks / MBDivisor

       = 32408 / 10.3952

       = 3117.5927

从上述结果可以看到,计算出来的全表扫描的成本四舍五入后就是3118

 

Oracle9i开始,引入了CPU cost model,通过dbms_stats.gather_system_stats收集系统级的统计信息,这样Oracle就能够规避上述两个假设(尤其是第一个假设,通常情况下,一次单快读的时间是要小于一次多快读的时间)所带来的成本计算不准问题。

CPU cost model所引入的系统级别的统计信息存储在表AUX_STATS$里,当你执行dbms_stats.gather_system_stats的时候,Oracle会在AUX_STATS$里记录如下四个方面的信息:

1Assumed CPU speed in MHz

2Single-block read average time in milliseconds

3Multiblock read average time in milliseconds

4Typical achieved multiblock read

 

正是因为Oracle会记录目标数据库所在的database server上的Typical achieved multiblock read,所以这实际上会对Oracle计算全表扫描的成本带来重大影响,因为这种情况下MBDivisor实际上就和DB_FILE_MULTIBLOCK_READ_COUNT的值没有关系了

 

Oracle9i开始,通过一个隐含参数_optimizer_cost_model来控制是否开启CPU cost model,它的默认值为CHOOSE,意思是如果AUX_STATS$里有相关记录,则开启CPU cost model,否则还是沿用以前的成本计算模型(即计算的全部是IO cost)。

 

9.2.0.1AUX_STATS$里没有记录:

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

SQL> select count(*) from sys.aux_stats$;

 

  COUNT(*)

———-

         0

 

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     16

 

所以9.2.0.1里全表扫描的成本实际上还是基于db_file_multiblock_read_count的值:

Table stats    Table: T1   Alias: T1

  TOTAL ::  CDN: 588639  NBLKS:  8000  AVG_ROW_LEN:  93

— Index stats

  INDEX NAME: IDX_T1  COL#: 4

    TOTAL ::  LVLS: 2   #LB: 1284  #DK: 29915  LB/K: 1  DB/K: 19  CLUF: 588639

_OPTIMIZER_PERCENT_PARALLEL = 0

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

SINGLE TABLE ACCESS PATH

Column:  OBJECT_ID  Col#: 4      Table: T1   Alias: T1

    NDV: 29915     NULLS: 0         DENS: 3.3428e-005 LO:  2  HI: 30857

    NO HISTOGRAM: #BKT: 1 #VAL: 2

  TABLE: T1     ORIG CDN: 588639  ROUNDED CDN: 20  CMPTD CDN: 20

  Access path: tsc  Resc:  771  Resp:  771

  Access path: index (equal)

      Index: IDX_T1

  TABLE: T1

      RSC_CPU: 0   RSC_IO: 23

  IX_SEL:  0.0000e+000  TB_SEL:  3.3428e-005

  BEST_CST: 23.00  PATH: 4  Degree:  1

 

SQL> select 8000/(1.6765*power(16,0.6581)) from dual;

 

8000/(1.6765*POWER(16,0.6581))

——————————

              769.582296540965

这里算出来的值为769.58,约等于770,跟trace文件中记录的实际计算结果差了1,这可能是受隐含参数_table_scan_cost_plus_one的影响,它的默认值是true

 

10.2.0.1AUX_STATS$里就有记录了,虽然这里我并没有通过dbms_stats.gather_system_stats来收集系统级别的统计信息。但它还是会记录Oracle认为目标数据库所在database server上关于系统级别统计信息的一些内部默认值:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

 

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    08-30-2005 15:04

SYSSTATS_INFO                  DSTOP                                     08-30-2005 15:04

SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                     484.974958

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     16

 

我们来看一下在10.2.0.1里,AUX_STATS$里有一些内部默认值的情况下Oracle如何计算全表扫描的成本:

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

SYSTEM STATISTICS INFORMATION

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

  Using NOWORKLOAD Stats

  CPUSPEED: 485 millions instruction/sec

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: T1  Alias: T1

    #Rows: 592951  #Blks:  8329  AvgRowLen:  93.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1305  #DK: 51868  LB/K: 1.00  DB/K: 11.00  CLUF: 592940.00

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

SINGLE TABLE ACCESS PATH

  Column (#4): OBJECT_ID(NUMBER)

    AvgLen: 5.00 NDV: 51868 Nulls: 11 Density: 1.9280e-005 Min: 2 Max: 55096

  Table: T1  Alias: T1    

    Card: Original: 592951  Rounded: 11  Computed: 11.43  Non Adjusted: 11.43

  Access Path: TableScan

    Cost:  1860.68  Resp: 1860.68  Degree: 0

      Cost_io: 1824.00  Cost_cpu: 213483714

      Resp_io: 1824.00  Resp_cpu: 213483714

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 15.00  resc_cpu: 113902

    ix_sel: 1.9280e-005  ix_sel_with_filters: 1.9280e-005

    Cost: 15.02  Resp: 15.02  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_T1

         Cost: 15.02  Degree: 1  Resp: 15.02  Card: 11.43  Bytes: 0

注意到上述trace文件中有这样一句话——“Using NOWORKLOAD Stats,这表示CPU cost model已经被启用了,只不过用来计算CPU cost的时候是用系统在没有负载情况下的系统统计信息,即用来计算CPU cost的是AUX_STATS$里的内部默认值。

从上述trace文件里可以看到,现在在表t18329个块的情况下,全表扫描的成本是1860.68,这其中IO Cost1824.00,它们之间的差值1860.681824.00就应该是CPU cost,这个剩下的CPU cost应该是基于Cost_cpu: 213483714和一个根据AUX_STATS$里的内部默认值而计算出来的,例如用213483714除以一个计算出来的CPU_FACTOR啥的。

在上述10.2.0.1里,db_file_multiblock_read_count同样是16,在表的块数差异不大的情况下(9.2.0.1800010.2.0.18329),全表扫描的成本却从771增加到1860.68

 

显然此时的MBDivisor就和DB_FILE_MULTIBLOCK_READ_COUNT没有关系了,也不再满足公式MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

我们来算一下此时的MBDivisor:

此时的MBDivisor = Num Blocks / tsc cost

                = 8329 / 1860.68

                = 4.4763204849

这可比9.2.0.1中的10.3952要小太多。

 

我们再来看同一台机器上的11.2.0.1的情况:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

可以看到默认值CPUSPEEDNW发生了变化,其他默认值跟10.2.0.1一样。

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    04-02-2010 14:19

SYSSTATS_INFO                  DSTOP                                     04-02-2010 14:19

SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                     1683.65129

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

db_file_multiblock_read_count的值也从16变成了128

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     128

 

我们来看一下在11.2.0.1里,AUX_STATS$里有一些内部默认值,但CPUSPEEDNW发生了变化的情况下Oracle如何计算全表扫描的成本:

—————————–

SYSTEM STATISTICS INFORMATION

—————————–

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 1684 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

  MBRC: -1 blocks (default is 8)

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  2477.61  Resp: 2477.61  Degree: 0

      Cost_io: 2467.00  Cost_cpu: 214434308

      Resp_io: 2467.00  Resp_cpu: 214434308

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 83376

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.00  Resp: 11.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.00  Degree: 1  Resp: 11.00  Card: 8.00  Bytes: 0

注意到上述trace文件中同样有这样一句话——“Using NOWORKLOAD Stats,这表示CPU cost model已经被启用了,只不过用来计算CPU cost的时候是用系统在没有负载情况下的系统统计信息,即用来计算CPU cost的是AUX_STATS$里的内部默认值。

从上述trace文件里可以看到,现在在表t19103个块的情况下,全表扫描的成本是2477.61,这其中IO Cost2467.00

在上述11.2.0.1里,db_file_multiblock_read_count的值由16变成了128,在表的块数差异不大的情况下(10.2.0.1832911.2.0.19103),全表扫描的成本从1860.68增加到了2477.61

 

显然此时的MBDivisor也和DB_FILE_MULTIBLOCK_READ_COUNT没有关系,也不再满足公式MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

我们来算一下此时的MBDivisor:

此时的MBDivisor = Num Blocks / tsc cost

                = 9103 / 2477.61

                = 3.6741052869

这也比9.2.0.1中的10.3952要小太多,同时也比10.2.0.1里的4.4763要小。

 

现在我用dbms_stats.delete_system_stats删掉系统统计信息:

SQL> exec dbms_stats.delete_system_stats();

 

PL/SQL procedure successfully completed

 

可以看到执行完后系统级别统计信息的一些内部默认值依然没有被删除,只不过这里CPUSPEEDNW发生了改变:

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    06-09-2012 23:25

SYSSTATS_INFO                  DSTOP                                     06-09-2012 23:25

SYSSTATS_INFO                  FLAGS                                   0

SYSSTATS_MAIN                  CPUSPEEDNW                        755.942

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

我们再来看对表t1执行同样sqlcost计算结果:

—————————–

SYSTEM STATISTICS INFORMATION

—————————–

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 756 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

  MBRC: -1 blocks (default is 8)

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  2490.64  Resp: 2490.64  Degree: 0

      Cost_io: 2467.00  Cost_cpu: 214434308

      Resp_io: 2467.00  Resp_cpu: 214434308

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 83376

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.01  Resp: 11.01  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.01  Degree: 1  Resp: 11.01  Card: 8.00  Bytes: 0

trace文件里可以看到全表扫描的成本从2477.61增加到了2490.64,这是可以理解的,CPUSPEEDNW的值降低了,进而根据这些值算出来的CPU_FACTOR也会减少,而Cost_cpu的值没变(Cost_cpu: 214434308),所以算出来的CPU cost(可以近似看作CPU cost = Cost_cpu/CPU_FACTOR)会增加,进而总的全表扫描的成本会增加。

 

我们再来测一下针对同样的11.2.0.1,当我把CPU cost model禁掉后的情况:

SQL> oradebug setmypid

已处理的语句

SQL> oradebug unlimit

SQL> alter session set “_optimizer_cost_model”=’IO’;

 

会话已更改。

 

SQL> oradebug event 10053 trace name context forever, level 1

已处理的语句

SQL> explain plan for select * from scott.t1 where object_id=999;

 

已解释。

 

SQL> oradebug tracefile_name

c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5256.trc

SQL> oradebug event 10053 trace name context off

已处理的语句

 

我们来看一下产生的trace文件的内容:

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

PARAMETERS USED BY THE OPTIMIZER

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

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

  PARAMETERS WITH ALTERED VALUES

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

Compilation Environment Dump

_optimizer_cost_model               = io

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  1383.00  Resp: 1383.00  Degree: 0

      Cost_io: 1383.00  Cost_cpu: 0

      Resp_io: 1383.00  Resp_cpu: 0

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 0

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.00  Resp: 11.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.00  Degree: 1  Resp: 11.00  Card: 8.00  Bytes: 0

 

Optimizer state dump:

Compilation Environment Dump

optimizer_mode_hinted               = false

……省略显示相关内容

optimizer_features_enable           = 11.2.0.1

……省略显示相关内容

_db_file_optimizer_read_count       = 8

……省略显示相关内容

db_file_multiblock_read_count       = 128

 

从上述文件里我们可以看到现在的全表扫描成本为1383.00,而且确实没有CPU cost了。

注意,这个时候计算MBDivisor就不是用db_file_multiblock_read_count的值了,而是会用隐含参数_db_file_optimizer_read_count

 

11.2.0.1里计算MBDivisor使用如下公式:

MBDivisor = 1.6765 * power(_db_file_optimizer_read_count, 0.6581)

 

我们来计算一下上述全表扫描的成本:

tsc cost = Num Blocks / MBDivisor

       = 9103 / (1.6765 * power(8, 0.6581))

       = 1381.8390

 

SQL> select 9103/(1.6765 * power(8, 0.6581)) from dual;

 

9103/(1.6765*POWER(8,0.6581))

—————————–

             1381.83905981934

 

1381.8390约等于1382,又因为_table_scan_cost_plus_one11.2.0.1里也是true,所以最终的全表扫描的成本值要加1,即应该是1383,刚好和trace文件里的结果一致。

 

最后,我们来总结一下这篇文章的结论:

1、在9i的时候虽然已经引入CPU cost model,但因为aux_stats$里并没有记录默认值,所以9i的全表扫描的成本在默认情况下(_optimizer_cost_model的值为CHOOSE)实际上全部是IO Cost,并没有包含CPU Cost;此时全表扫描成本的计算公式为:

tsc cost = Num Blocks / MBDivisor

= Num Blocks/1.6765 * power(db_file_multiblock_read_count, 0.6581)

这时全表扫描的成本会倚赖于参数db_file_multiblock_read_count的值;

 

2、在10g11gaux_stats$已经有默认值即相当于CPU cost model在默认情况下已被开启那么现在全表扫描的成本就和参数db_file_multiblock_read_count的值就没有关系了;

 

3、如果你在11g中禁用CPU cost model,采用原先旧的IO cost model,则全表扫描成本的计算公式变为:

tsc cost = Num Blocks / MBDivisor

= Num Blocks/1.6765 * power(_db_file_optimizer_read_count, 0.6581)

即这种情况下全表扫描的成本依然和参数db_file_multiblock_read_count的值没有关系。