oracle dbms_stat與analyze 獲取有效的統計資訊(1)
關於資料庫統計資訊
9前都是用analyze,9i 推薦dbms_stats,dbms_stats解決了 analyze不支援外部表，對於收集分割槽資訊只收集每個segment資訊，且只在物件級別評估
不過dbms_stats自身也有問題 無法收集empty_blocks資訊，無法收集row_chains資訊
defualt dbms_stats修改資料字典，主要功能如下
1.收集並儲存統計資訊（存到資料字典or備份表-自建）
2.lock or unlock 資料字典中統計資訊
3.恢復資料字典中統計資訊
4.刪除資料字典or備份表中統計資訊
5.exp資料字典中統計資訊到備份表
6.imp備份表中統計資訊到資料字典
7.get資料字典or備份表統計資訊
8.set資料字典or備份表統計資訊
#系統統計資訊
9i推出cpu_cost 所以出現了系統統計資訊（_optimizer_cost_model控制使用那種成本模型）
9i預設不收集系統統計資訊所以使用了io_cost,10g預設收集系統統計資訊所以預設使用cpu_cost （no_cpu_costing hint可以讓sql使用io_cost模型）
According to the CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +~~~~~~~~~~~~~~~~~~~~~~~~~~~~來自於cost-based oracle書中(這部分是計算fts時i/o cost)
#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
系統統計資訊就是 sreadtim,#SRDs ,#CPUCycles這些
系統統計資訊又分非工作量統計資訊（noworkload statistics-人工測試,模擬應用負載）和工作量統計資訊(workload statsitics-實際真實的測試，相當於實際使用實際的應用負載)
系統統計資訊存aux_stats$中
SQL> desc aux_stats$
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAME NOT NULL VARCHAR2(30)
PNAME NOT NULL VARCHAR2(30)
PVAL1 NUMBER
PVAL2 VARCHAR2(255)
sysstats_info:顯示系統統計資訊的狀態收集時間，正確收集status=completed，若收集出現問題status=badstats,query optimizer不會使用badstats的系統統計資訊
(status的其他狀態manual gathering 手動收集中，auto gathering 自動收集中，收集非工作量統計資訊ststus=noworkload 9i)
SQL> select pname,pval1,pval2 from aux_stats$ where sname='SYSSTATS_INFO';
PNAME PVAL1 PVAL2
------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DSTART 08-03-2007 02:16
DSTOP 08-03-2007 02:16
FLAGS 1
STATUS COMPLETED
SQL> execute dbms_stats.gather_system_stats;(需要pubilc去執行，所以每個user都可以收集系統統計資訊，修改資料字典裡系統統計資訊需要 role gather_system_statistics，or dml aux_STATS$的許可權,default 通過dba role grant gather_system_statistics role)
PL/SQL procedure successfully completed.
SQL> select pname,pval1,pval2 from aux_stats$ where sname='SYSSTATS_INFO';
PNAME PVAL1 PVAL2
------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DSTART 04-20-2010 02:47~~~~~~~~收集開始時間（1分鐘內正常）
DSTOP 04-20-2010 02:47~~~~收集結束時間
FLAGS 1
STATUS COMPLETED
sysstats_main:系統統計資訊結果
SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW 484.974958 speed in millions of operations per second (一個cpu一秒鐘能處理的操作次數，單位百萬次)
IOSEEKTIM 10 disk seek time in milliseconds
IOTFRSPEED 4096 disk transfer time in bytes per millisecond
MAXTHR (整個系統的最大i/o吞吐量，位元組/s)
MBRC (一次多塊讀平均讀取資料塊數)
MREADTIM (多塊資料平均讀時間，單位毫秒)
SLAVETHR (並行處理中slave process的平均I/O吞吐量,位元組/S）
SREADTIM (單塊資料平均讀時間，單位毫秒)
已選擇9行。以上沒有資訊收集 ORACLE 會用DEFAULT 計算************
計算*使用 預設資訊**************************
MBRC=db_file_multiblock_read_count
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED
#rac所有instance都使用一套統計資訊(選擇負載小的node收集)
非工作量統計資訊
oracle 10g非工作量系統統計資訊總可用，刪除非工作量系統統計資訊後db startup時候會自動收集(9I 非工作量系統統計資訊不存資料字典，aux_stats$中status=noworkload)
SQL> execute dbms_stats.gather_system_stats(gathering_mode=>'noworkload');（oracle自動跑幾個測試，可能要多次收集替換default值）
PL/SQL procedure successfully completed.
SQL> select pname,pval1,pval2 from aux_stats$ where sname='SYSSTATS_INFO';
PNAME PVAL1 PVAL2
------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DSTART 04-20-2010 02:53
DSTOP 04-20-2010 02:53
FLAGS 1
STATUS COMPLETED
DBMS_STATS.GATHER_SYSTEM_STATS (
gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD',
interval INTEGER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
gathering_mode
Mode values are:
NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
interval
Time, in minutes, to gather statistics. This parameter applies only when gathering_mode='INTERVAL'
stattab
Identifier of the user statistics table where the statistics will be saved
statid
Optional identifier associated with the statistics saved in the stattab
statown
Schema containing stattab (if different from current schema)
#工作量統計資訊
可以用
execute dbms_stats.gather_system_stats(gathering_mode=>'interval',interval=>30)
立刻開始start,30分鐘後自動結束，可以查詢user_jobs,user_scheduler_jobs(10gr2用排程了)
()
00:19:52 SQL> select schedule_name,job_name,job_action ,to_char(END_DATE,'yyyy-mm-dd hh24:mi:ss') from user_scheduler_jobs where job_action like 'begin dbms_stats%';
SCHEDULE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
JOB_NAME JOB_ACTION TO_CHAR(END_DATE,'Y
------------------------------ ------------------------------ -------------------
STATJOB$_511 begin dbms_stats.gather_system
_stats(gathering_mode => 'AUTO
_STOP', statown => 'SYS'); end
;
若已有SESSION 執行了，則不能再次執行
SQL> execute dbms_stats.gather_system_stats(gathering_mode=>'interval',interval=>30)
BEGIN dbms_stats.gather_system_stats(gathering_mode=>'interval',interval=>30); END;
*
ERROR at line 1:
ORA-20003: Unable to gather system statistics
ORA-06512: at "SYS.DBMS_STATS", line 20670
ORA-06512: at line 1
#設定工作量統計資訊
：思路很簡單開始收集一次-中間db正常執行-結束收集一次，2次差值結算系統統計資訊(若i/o統計無法計算將設定為null,9i為-1)
execute dbms_stats.gather_system_stats(gather_mode=>'start')
db正常執行>30分鐘
execute dbms_stats.gather_system_stats(gather_mode=>'stop')
#指令碼來自oracle trouble shooting
execute dbms_stats.create_stat_table('sys','aux_stats_history')
VARIABLE job NUMBER
execute dbms_job.submit(:job,'declare statid varchar2(30) := ''S''||to_char(sysdate,''yyyymmddhh24miss''); begin dbms_stats.gather_system_stats(''start'', null, ''aux_stats_history'', statid, ''sys''); dbms_lock.sleep(3600); dbms_stats.gather_system_stats(''stop'', null, ''aux_stats_history'', statid, ''sys''); end;',sysdate,'sysdate+1/24')
COMMIT;
使用這個方法每小時收集一下工作量系統統計資訊 存在aux_stats_history中，最後將aux_stats_history中幾天收集的資訊弄到一個excle表上
mbrc,mreadtim,sreadtim ，cpuspeed取平均值，maxthr,slavethr取最大值
#往excel表裡搞資料時使用的查詢從aux_stats_history中提取資料
SELECT n1 AS sreadtim, n2 AS mreadtim, n3 AS cpuspeed, n11 AS mbrc
FROM sys.aux_stats_history
WHERE c4 = 'CPU_SERIO'
ORDER BY statid;
SELECT n1 AS maxthr, n2 AS slavethr
FROM sys.aux_stats_history
WHERE c4 = 'PARIO'
ORDER BY statid;
然後開始手動修改系統統計資訊，建議這種方式將系統的system status固定住
dbms_stats.delete_system_stats();
dbms_stats.set_system_stats(PNAME=>'CPUSPEED',pvalue=>&value);
dbms_stats.set_system_stats(PNAME=>'MBRC',pvalue=>&value);
dbms_stats.set_system_stats(PNAME=>'MREADTIM',pvalue=>&value);
dbms_stats.set_system_stats(PNAME=>'SREADTIM',pvalue=>&value);
dbms_stats.set_system_stats(PNAME=>'SLAVETHR',pvalue=>&value);
dbms_stats.set_system_stats(PNAME=>'MAXTHR',pvalue=>&value);
cost計算公式
訪問列開銷(計算訪問一個列的cpu開銷跟這個列在表中的位置有關係)
cpu_cost=列位置.20(公式是訪問1行的，多行就是多個倍數 基本是按20遞增的)
總cost =io_cost+cpu_cost
將cpu_cost轉換成i/o cost的單位 =cpu_cost=cpu_cost/cpuspeed（cpu速度）.sreadtim（單塊讀時間）.1000
CPU 部分 轉換成IO COST 單位
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~早期實驗的例子
#CPUCycles / (cpuspeed * sreadtim)
#CPUCycles:1642429~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~就是cpu_cost
cpuspeed default 使用 CPUSPEEDNW
sreadtime:12(milliseconds)=12000(microseconds) =sreadtime*1000
SQL> select 1642429/(484*12000) from dual;
1642429/(484*12000)
-------------------
.282787362
在使用非工作量統計資訊計算時候
cpuspeed default 使用 CPUSPEEDNW(cpuspeednw=cpuspeed noworkload 代表非工作量統計資訊)
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED(基於非工作量統計資訊和block size計算)
******正常情況下若存在工作量系統統計資訊，query optimizer會使用 並忽略noworkload的系統統計資訊（但query optimizer會進行一些健康檢查，有可能會禁用或部分替換工作量系統統計資訊）
1.當sreadtim,mreadtim,mbrc不可用時，query optimizer忽略工作量系統統計資訊
2.當mreadtim<=sreadtim時 sreadtim重新計算=IOSEEKTIM+db+block_size/IOTFRSPEED,mreadtim重新計算=ioseektim+mbrc.db_block_size/iotfrspeed(基於非工作量統計資訊和block size計算)
當建議固定工作量統計資訊後，可以過陣子再收集下將新收集的備份到備份表（gather_system_stats引數statown,stattab 來實現）看下和資料字典裡固定的工作量統計資訊有沒顯著差異，有的話可以考慮改下
物件object
表統計資訊，index統計資訊，column統計資訊
*_tab_statistics表統計資訊
*_tab_col_statistics 列統計資訊
*_ind_statistics 索引統計資訊
#使用histogram 完善統計資訊
oracle理論上認為資料分佈是均勻的，但 比如一個表裡有2000 rows 每行就一列（int）型別，其中前1000ROWS 都是 數字1，而後面才是從2開始其它不相同數字，此時where a=1時候oracle會估算出錯誤的card
因為選擇率是按（1/distinct算的 這樣選擇率為1/1001，card=2000*1/1001得到一個錯誤的估算），此時histogram可以解決（另外histogram還可以解決日期型別帶來的一些問題）
簡單說histogram就是query optimizer需要的額外資訊來確定資料的分佈情況
histogram分2種 一種是頻率histogram，另一種是高度均衡histogram
#測試（測試用例 參考oracle trouble shooting）
SQL> CREATE TABLE t
2 AS
3 SELECT rownum AS id,
4 round(dbms_random.normal*1000) AS val1,
5 100+round(ln(rownum/3.25+2)) AS val2,
6 100+round(ln(rownum/3.25+2)) AS val3,
7 dbms_random.string('p',250) AS pad
8 FROM dual
9 CONNECT BY level <= 1000
10 ORDER BY dbms_random.value;
Table created.
SQL> select val2 as val2,count(*) from t group by val2 order by val2;
VAL2 COUNT(*)
---------- ----------
101 8
102 25
103 68
104 185
105 502************************可以看見 不均衡
106 212
6 rows selected.
SQL> SQL> begin
2 dbms_stats.gather_table_stats(
3 'SYS',
4 't',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 120'
8 );
9 end;
10 /
*size 120其實就是用了120個bucket，這個表裡唯一值是6，或者用>6的bucket的數量就行
PL/SQL procedure successfully completed.
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS frequency
4 FROM user_tab_histograms
WHERE table_name = 'T'
5 6 AND column_name = 'VAL2'
7 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ----------
101 8 8
102 33 25
103 101 68
104 286 185
105 788 502************
106 1000 212
6 rows selected.
頻率histogram 表示 列的每個值都對應一個bucket（當列的histogram bucket >=列distinct時候為頻率）當列中的唯一值總數量>254時候為高度均衡histogram
(因為bucket最多254個)
分析例中可以看出列val2中有6個唯一值(101-106)
endpoint_value:就是該值本身，列是number的，所以char,varchar2都需要轉換為number,（轉換隻取前6個位元組，所以histogram儲存的值就是這個列轉換後的前面部分所以固定字首的字串會讓histogtam嚴重不均衡
，若是多位元組字符集，6個位元組可能只有3個字元）*******注意這個問題histogram不是萬能的
endpoint_number:是取值的累計次數（當前endpoint_number-上一個endpoint_number就是當前value出現的次數)
*_tab_col_statistics中在收集完histogram後，欄位histogram會顯示是哪種histogram(若光收集histogram，列資訊相當於也收集了*_tab_col_statistics)
例 解決問題
create table t3(a int)
SQL> ed
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..1000 loop
4 insert into t3 values(1);
5 end loop;
6 for i in 2..9001 loop
7 insert into t3 values(i);
8 end loop;
9 commit;
10* end;
11 /
PL/SQL 過程已成功完成。
SQL> execute dbms_stats.gather_table_stats('SYS','T3');
PL/SQL 過程已成功完成。
SQL> select count(*) from t3;
COUNT(*)
----------
10000
SQL> select num_rows from user_tables where table_name='T3';
NUM_ROWS
----------
10000
SQL> select table_name,density,num_nulls,num_distinct from user_tab_col_statisti
cs where table_name='T3';
TABLE_NAME DENSITY NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
T3 .000111099 0 9001
SQL> select count(*) from t3 where a=1;
COUNT(*)
----------
1000
從上面資訊可以看到 ~~列A 有10000ROWS 其中A=1的 1000 ROWS， A=2 到9001 的有9000 ROW
這樣NUM_DISTINCT=9001
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
會話已更改。
SQL> select count(*) from t3 where a=1;
執行計劃
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 3 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
可以看到CARD 非常不準確 還是按公式 num_rows*1/num_distinct算的
SQL> select 10000*1/9001 from dual;
10000*1/9001
------------
1.11098767
***********************
Table Stats::
Table: T3 Alias: T3
#Rows: 10000 #Blks: 23 AvgRowLen: 3.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
AvgLen: 4.00 NDV: 9001 Nulls: 0 Density: 1.1110e-004 Min: 1 Max: 9001
Table: T3 Alias: T3
Card: Original: 10000 Rounded: 1 Computed: 1.11 Non Adjusted: 1.11
Access Path: TableScan
Cost: 7.14 Resp: 7.14 Degree: 0
Cost_io: 7.00 Cost_cpu: 2163793
Resp_io: 7.00 Resp_cpu: 2163793
Best:: AccessPath: TableScan
Cost: 7.14 Degree: 1 Resp: 7.14 Card: 1.11 Bytes: 0~~~~~~~~~~~~~~~~~~看到trace 中card =1.11 ，實際應該為1000
我們可以用HISTOGREAM來解決
1 begin
2 dbms_stats.gather_table_stats(
3 'SYS',
4 't3',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 120'
8 );
9* end;
SQL> /
PL/SQL 過程已成功完成。
使用HISTOGRAM收集統計資訊後 ORACLE 將使用DENSITY 來計算
SQL> select table_name,density,num_nulls,num_distinct from user_tab_col_statisti
cs where table_name='T3';
TABLE_NAME DENSITY NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
T3 .0001 0 9001
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
會話已更改。
SQL> select count(*) from t3 where a=1;
執行計劃
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T3 | 917 | 2751 | 7 (0)| 00:00:01 |~~~~~~~~~~可以看到CARD接近了=917
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
SQL> select count(*) from t3 where a=2;
執行計劃
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 3 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=2)
SQL>
上面可以看出 A=1時候CARD=917 ORACLE 並沒有使用density來計算， A=2時候用的DENSITY計算的 card=10000*0.0001=1
針對a=1 oracle 使用的 histogram bucket來計算的
SQL> select endpoint_number,endpoint_value from dba_histograms where table_name=
'T3' and rownum<5;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
11 1
12 9
13 93
14 177
可以看出histogram是一個 高度均衡的（HISTOGRAM分2種 第一中 頻率histogram 每個bucket存一個值 當列的histogram bucket >=列distinct時候為頻率 ，另一種高度均衡HISTOGRAM
當列DISTINCT比 histogram bucket多 時為高度均衡 每個bucket存相同數量的值）
此例為高度均衡 可以看出來 1-11 BUCKET 存 值1，其實第12個BUCKET中也存值1， 因為12-13中可以看出每個BUCKET 存 84個值(93-9)（此例中除值1外 其他都唯一，所以很好看出來），而 第12個 BUCKET 最高存的值為9 這樣表示 第12個BUCKET還存了 值1（應該是76 =84-8 ，8是2到9的個數） ,oracle 發現 值1 是一個高頻率出現的值（跨越的多個桶），oracle將採用BUCKET計算11/120（跨越bucket數/總bucket數）=.091666667 從這裡看到了雖然BUCKET12中也有值1 但ORACLE沒算進來（因為該bucket中還有其它值2-8） 用11/120 這也就造成了CARD只是接近而不是準確的1000
#index統計資訊
b*tree index結構就是 根塊-分支-葉塊（葉塊存index key值&指向表中資料的rowid）
*_ind_statistics 有index中資訊，其中比較重要的就是cluster_factor這個代表索引與表中資料的緊密度
其中欄位
avg_leaf_blocks_per_key：存放一個key value的平均葉子塊數 avg_leaf_block_per_key=leaf_blocks/distinct_keys
avg_data_blocks_per_key:表中單個key引用的平均資料塊數avg_data_blocks_per_key=clustering_factor/distinct_keys
簡單看一下clustering_factor
簡單的說CLUSTERING_FACTOR 用於INDEX 的有序度和表的混亂度之間比較
b*tree index是經過排序的
例如 INDEX中 記錄的第一個rowid指向 表所在DATAFILE 中 BLOCK#1 第1行 計數器 記為1，第2個rowid 指向 BLOCK#2 由於改變了塊 所以 計數器加1 ，INDEX 第3個rowid
指向BLOCK#2 塊沒變 所以計數器還為2，接著沿INDEX執行 第4個rowid 指向BLOCK#1 塊又變了計數器加1
計數器對應著CLUSTERING_FACTOR 計數器每次從一個塊到另一個新塊時候加1 這樣CLUSTERING_FACTOR也加一
****所以clustering_factor可以描述資料在表中的散佈方式
#如果clustering_factor接近表中的行數，大多行都不在同一個塊中,分佈太散
#當clustering_factor接近表中的塊數，說明資料集中有序
當用INDEX 獲取一行以上資料時（INDEX RANGE SCAN），需要遍歷INDEX的一部分 叫INDEX的 X%，掃描INDEX 時必須逐行的讀取表,那麼當遍歷INDEX 的 X%時，轉換表塊的次數就等於clustering_factor 的 X%
另外clustering_factor對於oracle 優化器計算index cost 有直接關係
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
今天讀troubleshooting oracle performance 找到一個直接獲取clusering_factor的函式指令碼特此記錄,仔細看可以發現 計算方式與上面的理論是一樣的
SQL> show user
USER is "XH"
SQL> create table t3 (a int ,b int);
Table created.
SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t3 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index t3_ind on t3(a);
Index created.
SQL> select clustering_factor from user_ind_statistics where index_name='T3_IND';
CLUSTERING_FACTOR
-----------------
18
SQL> CREATE OR REPLACE FUNCTION clustering_factor (
2 p_owner IN VARCHAR2,
3 p_table_name IN VARCHAR2,
4 p_column_name IN VARCHAR2
5 ) RETURN NUMBER IS
6 l_cursor SYS_REFCURSOR;
7 l_clustering_factor BINARY_INTEGER := 0;
8 l_block_nr BINARY_INTEGER := 0;
9 l_previous_block_nr BINARY_INTEGER := 0;
10 l_file_nr BINARY_INTEGER := 0;
11 l_previous_file_nr BINARY_INTEGER := 0;
12 BEGIN
13 OPEN l_cursor FOR
14 'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, '||
15 ' dbms_rowid.rowid_to_absolute_fno(rowid, '''||
16 p_owner||''','''||
17 p_table_name||''') file_nr '||
18 'FROM '||p_owner||'.'||p_table_name||' '||
19 'WHERE '||p_column_name||' IS NOT NULL '||
20 'ORDER BY ' || p_column_name;
21 LOOP
FETCH l_cursor INTO l_block_nr, l_file_nr;
22 23 EXIT WHEN l_cursor%NOTFOUND;
24 IF (l_previous_block_nr <> l_block_nr OR l_previous_file_nr <> l_file_nr)
25 THEN
26 l_clustering_factor := l_clustering_factor + 1;
27 END IF;
28 l_previous_block_nr := l_block_nr;
29 l_previous_file_nr := l_file_nr;
30 END LOOP;
31 CLOSE l_cursor;
32 RETURN l_clustering_factor;
33 END;
34 /
Function created.
SQL> select clustering_factor('XH','T3','A') from dual;
CLUSTERING_FACTOR('XH','T3','A')
--------------------------------
18
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-664238/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle dbms_stat與analyze 獲取有效的統計資訊(5)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(4)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(3)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(2)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(6)Oracle
- dbms_stat與analyze的區別
- dbms_stat, dbms_utility與analyze的區別
- Oracle10g dbms_stat 恢復原有的統計資訊Oracle
- MySQL 5.7 ANALYZE TABLE分析索引的統計資訊MySql索引
- 獲取資訊版本1
- [Oracle] Oracle收集統計資訊的取樣比例Oracle
- ORACLE表統計資訊與列統計資訊Oracle
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- Oracle資料庫的資料統計(Analyze)Oracle資料庫
- ORACLE 統計資訊的收集與管理Oracle
- 獲取計算機系統唯一資訊計算機
- 資料庫效能調優之始： analyze統計資訊資料庫
- Oracle獲取所有表名資訊和獲取指定表名欄位資訊Oracle
- 【統計資訊】Oracle統計資訊Oracle
- Android系統資訊獲取Android
- Oracle 元件資訊獲取途徑整理Oracle元件
- 獲取Oracle隱含引數資訊Oracle
- oracle 統計資訊檢視與收集Oracle
- Oracle的統計資訊Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 在.NET中輕鬆獲取系統資訊(1) －WMI篇 (轉)
- SNMP系統資訊獲取工具onesixtyone
- Oracle Analyze的用法Oracle
- 利用python獲取nginx服務的ip以及流量統計資訊PythonNginx
- 修改oracle 的統計資訊Oracle
- Oracle 統計資訊Oracle
- Oracle統計資訊Oracle
- 全球IP whois資訊獲取與情報挖掘
- iOS 之獲取APP與手機 資訊iOSAPP
- Windows系統安全獲取重要資訊的方法(一)Windows
- 如何優雅獲的獲取不同系統版本中的程式資訊
- SAP ABAP使用CDS獲取系統資訊
- Sigar獲取作業系統資訊作業系統