oracle實驗記錄 (快取記憶體命中率與成本)

高速緩衝區命中率與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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613827/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 (快取記憶體命中率與成本)

相關文章