高速缓冲区命中率与COST
COST-BASED ORACLE 讲到BCHR（buffer cache hit ratio）高速缓存区命中率 将影响COST
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> select count(*) from t1;
COUNT(*)
----------
10000
SQL> select object_id from dba_objects where object_name='T1';
OBJECT_ID
----------
52816
由这2个参数控制，书中介绍DEFAULT为TRUE 而10GR2 为DEFAULT
输入 par 的值: cache_stats
原值 14: x.ksppinm like '%_&par%'
新值 14: x.ksppinm like '%_cache_stats%'
NAME VALUE ISDEFAULT ISMOD IS
ADJ
------------------------------ ------------------------- --------- ---------- --
---
KSPPDESC
--------------------------------------------------------------------------------
----------------------------------------------------
_cache_stats_monitor FALSE TRUE SYSTEM_MOD FA************** 当前状态
LSE
if TRUE, enable cache stats monitoring~~开启CACHE状态监控
_optimizer_cache_stats FALSE TRUE SYSTEM_MOD FA************** 当前状态
LSE
cost with cache statistics~~~~~~依靠CACHE 信息
_optimizer_cost_model CHOOSE************** 当前状态
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> alter session set events '10046 trace name context forever,level 12';
会话已更改。
SQL> select count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 6.11 Resp: 6.11 Degree: 0
Cost_io: 6.00 Cost_cpu: 1642429
Resp_io: 6.00 Resp_cpu: 1642429
Best:: AccessPath: TableScan
Cost: 6.11 Degree: 1 Resp: 6.11 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 6.1077 Degree: 1 Card: 10000.0000 Bytes: 0
Resc: 6.1077 Resc_io: 6.0000 Resc_cpu: 1642429
Resp: 6.1077 Resp_io: 6.0000 Resc_cpu: 1642429
10046
PARSING IN CURSOR #2 len=348 dep=1 uid=0 ct=3 lid=0 tim=14637224019 hv=2512561537 ad='20acab64'
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-
127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl
(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#
END OF STMT
PARSE #2:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=14637224014
BINDS #2:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=0813d704 bln=22 avl=04 flg=05
value=52816（T1 TABLE）
PARSING IN CURSOR #2 len=493 dep=1 uid=0 ct=3 lid=0 tim=14637222629 hv=2584065658 ad='20aba99c'
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl
(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t
.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl
(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
PARSE #2:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=14637222624
BINDS #2:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=08135868 bln=22 avl=04 flg=05
value=52816（T1 TABLE） 可以看到引用了TAB_STATS$
PARSING IN CURSOR #8 len=789 dep=1 uid=0 ct=3 lid=0 tim=14637223218 hv=3159716790 ad='20ab867c'
select
i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac
,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl
(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc
(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand
(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and
i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
END OF STMT
PARSE #8:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=14637223214
BINDS #8:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=0810c688 bln=22 avl=04 flg=05
value=52816（T1 TABLE）可以看到引用了IND_STATS$
SQL> desc TAB_STATS$;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
OBJ# NOT NULL NUMBER
CACHEDBLK NUMBER
CACHEHIT NUMBER
LOGICALREAD NUMBER
ROWCNT NUMBER
BLKCNT NUMBER
EMPCNT NUMBER
AVGSPC NUMBER
CHNCNT NUMBER
AVGRLN NUMBER
AVGSPC_FLB NUMBER
FLBCNT NUMBER
ANALYZETIME DATE
SAMPLESIZE NUMBER
FLAGS NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
SQL> alter system set "_cache_stats_monitor"=true;~~~~~~~~~~~~~改为TRUE
系统已更改。
SQL> alter system set "_optimizer_cache_stats"=true;~~~~~~~~~~~~~改为TRUE
系统已更改。
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> alter session set events '10046 trace name context forever,level 12';
会话已更改。
SQL> select count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 5.11 Resp: 5.11 Degree: 0
Cost_io: 5.00 Cost_cpu: 1642429
Resp_io: 5.00 Resp_cpu: 1642429~~~~~~~~~~~~~~~~~~~~~~可以看出buffer cache hit ratio影响了IO COST 部分
Best:: AccessPath: TableScan
Cost: 5.11 Degree: 1 Resp: 5.11 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 5.1077 Degree: 1 Card: 10000.0000 Bytes: 0
Resc: 5.1077 Resc_io: 5.0000 Resc_cpu: 1642429
Resp: 5.1077 Resp_io: 5.0000 Resc_cpu: 1642429
kkoipt: Query block SEL$1 (#0)
10046 trace中 没有 OBJ# 52816 查询表TAB_stats$,ind_stats$的信息 可以看到OPTIMIZER 访问表路径有了变化~~~具体应该怎么分析还不知道，COST-BASED ORACLE 说应该是
采用CACHE_stats_1$表 TRACE中没有这个表
总得来说对COST IO 部分有影响 ORACLE 默认是关闭的（COST依据CACHE信息），不影响传动成本计算，影响CPU_COSTING计算
**************************************
SQL> alter system set "_optimizer_cost_model"=io;（传统成本计算）
系统已更改。
_cache_stats_monitor TRUE****************为TRUE 后并不影响传统成本计算
LSE
if TRUE, enable cache stats monitoring
_optimizer_cache_stats TRUE
LSE
cost with cache statistics
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> alter session set events '10046 trace name context forever,level 12';
会话已更改。
SQL> select count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 4 |~~~~~~~~~~~~
---------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-613827/ ，如需转载，请注明出处，否则将追究法律责任。
