我们都知道在Oracle数据库里是“读不阻塞写，写不阻塞读”，那么我们可不可以认为在正常情况下，select操作是怎样都能执行，始终不会被hang住的呢？注意我这里提到的是正常情况下，不包括那些由于latch被hold住、或者bug等相关异常所导致的select操作被hang住的情况。
答案是：不可以这样认为的。
我们来举一个反例。
首先我们来分析一下在sql硬解析时在相关表对象上library cache lock的持有情况。这里我用到了10049事件，用10049事件，最重要的就是要知道如何设置它所对应的level值。
10049的level值可能会有如下一些组合：
#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值取0×0210=0×0200|0×0010，即这里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 = 0×25788788 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 = 0×25788788 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）中我们可以看出对表t2的library 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住的。
因为一开头的X是kglget，结尾才kgllkdl（kgllkdl大致是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）操作。
我们来测一下，同时开3个session。
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，同时它的STATE为waiting，SECONDS_IN_WAIT的值在递增。
这就验证了我们的结论：在win32上的10.2.0.1中，在对表增加主键的过程中会一直阻塞对这个表的查询（select）操作。
现在我们再问一个问题：是不是所有对表的DDL操作，在DDL操作的执行过程中都会阻塞对这个表的select操作？
答案是：不是这样的。
我们来举一个反例。
现在我们来测一下对表drop一个column时library 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事件后drop表t1的列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 2在session 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）中我们可以看出对表t1的library 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.t1的library cache lock的持有模式都是S，最后才是X，所以这就可以解释为什么在对表scott.t1执行drop column操作的时候对它的select语句能够同时执行。
从trace文件来看，drop column并不是不会阻塞select操作，只是阻塞的时间点要恰好是Oracle以X模式持有library cache lock时。
最后我们来测一下对一个表增加一个unique constraint时library 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.t2的library 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对全表扫描成本的影响
Posted: June 10, 2012 | Author: Cui Hua | Filed under: Oracle | Leave a comment »Oracle在未引入CPU cost model之前，它会假设如下两个事实是成立的：
1、single-block reads are just as expensive as multiblock reads
2、全表扫描的时候，会根据MBDivisor（multiblock 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
这里全表扫描的成本Resc为3118，这里tsc表示tablescan，Resc表示resource cost。
Resp表示response cost，意思是指在有并行情况下的成本，这里没有使用并行表扫描，所以Resp和Resc相等。
我们来看一下上述3118是怎样算出来的：
Num Blocks这里为32408
DB_FILE_MULTIBLOCK_READ_COUNT为16，所以MBDivisor为10.3952
SQL> select 1.6765 * power(16, 0.6581) from dual;
1.6765*POWER(16,0.6581)
———————–
10.3952495216659
将Num Blocks和MBDivisor带入上述公式：
tsc cost = Num Blocks / MBDivisor
= 32408 / 10.3952
= 3117.5927
从上述结果可以看到，计算出来的全表扫描的成本四舍五入后就是3118。
Oracle从9i开始，引入了CPU cost model，通过dbms_stats.gather_system_stats收集系统级的统计信息，这样Oracle就能够规避上述两个假设（尤其是第一个假设，通常情况下，一次单快读的时间是要小于一次多快读的时间）所带来的成本计算不准问题。
CPU cost model所引入的系统级别的统计信息存储在表AUX_STATS$里，当你执行dbms_stats.gather_system_stats的时候，Oracle会在AUX_STATS$里记录如下四个方面的信息：
1、Assumed CPU speed in MHz
2、Single-block read average time in milliseconds
3、Multiblock read average time in milliseconds
4、Typical achieved multiblock read
正是因为Oracle会记录目标数据库所在的database server上的Typical achieved multiblock read，所以这实际上会对Oracle计算全表扫描的成本带来重大影响，因为这种情况下MBDivisor实际上就和DB_FILE_MULTIBLOCK_READ_COUNT的值没有关系了。
Oracle从9i开始，通过一个隐含参数_optimizer_cost_model来控制是否开启CPU cost model，它的默认值为CHOOSE，意思是如果AUX_STATS$里有相关记录，则开启CPU cost model，否则还是沿用以前的成本计算模型（即计算的全部是IO cost）。
9.2.0.1中AUX_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.1里AUX_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文件里可以看到，现在在表t1有8329个块的情况下，全表扫描的成本是1860.68，这其中IO Cost为1824.00，它们之间的差值1860.68－1824.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.1为8000，10.2.0.1为8329），全表扫描的成本却从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文件里可以看到，现在在表t1有9103个块的情况下，全表扫描的成本是2477.61，这其中IO Cost为2467.00。
在上述11.2.0.1里，db_file_multiblock_read_count的值由16变成了128，在表的块数差异不大的情况下（10.2.0.1为8329，11.2.0.1为9103），全表扫描的成本从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执行同样sql的cost计算结果：
—————————–
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_one在11.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、在10g、11g中aux_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的值没有关系。
扫一扫
585
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
