关于数据库统计信息
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-664238/
5777
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
