Oracle 统计信息
- - 数据库 - ITeye博客--Oracle 优化器统计信息
Oracle优化器统计信息描述了关于数据库和相关对象的统计信息,当执行SQL查询时,优化器会使用这些统计信息估算出各种不同的执行计划的资源消耗,
,从而选择最高效的执行计划. 当统计信息缺失或者陈旧时,Oracle可能会选择错误的执行计划,导致SQL执行效率低下.
--Oracle 优化器统计信息 Oracle优化器统计信息描述了关于数据库和相关对象的统计信息,当执行SQL查询时,优化器会使用这些统计信息估算出各种不同的执行计划的资源消耗, ,从而选择最高效的执行计划。当统计信息缺失或者陈旧时,Oracle可能会选择错误的执行计划,导致SQL执行效率低下。 优化器统计信息包括以下部分: 表级别统计信息 1. 数据行数 2. 数据块数。 3. 每行的长度 --相关性能视图 admin@ORCL> SELECT NUM_ROWS, BLOCKS, AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'T'; NUM_ROWS BLOCKS AVG_ROW_LEN ---------- ---------- ----------- 14301 200 102 字段的统计信息 1. 唯一值的个数。 2. 空值的个数 3. 数据分布情况(直方图) 4. 相关列统计信息(Extended statistics) admin@ORCL> SELECT NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OWNER'; NUM_DISTINCT NUM_NULLS HISTOGRAM ------------ ---------- --------------- 10 0 FREQUENCY admin@ORCL> SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OWNER'; TABLE COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU ----- -------------------- --------------- -------------- -------------------- T OWNER 13 3.3888E+35 T OWNER 15 3.3913E+35 T OWNER 39 3.5442E+35 T OWNER 41 4.1186E+35 T OWNER 43 4.1192E+35 T OWNER 1379 4.1711E+35 T OWNER 4885 4.3277E+35 T OWNER 5102 4.3277E+35 T OWNER 5208 4.5330E+35 T OWNER 5508 4.5831E+35 索引统计信息 1. 索引叶子块的个数 2. 索引的高度(Levels) 3. 集群因子(Clustering Factor) admin@ORCL> SELECT BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_T'; BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ---------- ----------- ----------------- 1 61 28600 系统级别的统计信息 1. I/O 执行性能和消耗 2. CPU 执行性能和消耗 Oracle会根据以上的统计信息来计算每种执行计划的Cost,然后选择Cost最小的执行计划(即CBO)。Cost是Oracle对执行计划消耗的计算指标。 CBO的计算模型是Oracle内部的,目前是没有公布的,而且Oracle的计算模型是十分复杂的,我们只能依据一些实验来答题猜测CBO的计算模型。 admin@ORCL> CREATE TABLE T1 AS SELECT TRUNC(DBMS_RANDOM.VALUE(0,25)) N1, RPAD('X',40) IND_PAD, TRUNC(DBMS_RANDOM.VALUE(0,20)) N2, LPAD(ROWNUM,10,'0') SMALL_VC, RPAD('X',200) PADDING FROM ALL_OBJECTS WHERE ROWNUM<= 10000; Table created. --创建索引 admin@ORCL> create index t1_i1 on t1(n1,ind_pad,n2) nologging pctfree 91; Index created. admin@ORCL> SELECT 2 NUM_ROWS, --记录数 3 DISTINCT_KEYS, --不重复的键值数 4 BLEVEL, --索引数的级别 5 LEAF_BLOCKS, --叶子块数 6 CLUSTERING_FACTOR, --聚促因子 7 AVG_LEAF_BLOCKS_PER_KEY, 8 AVG_DATA_BLOCKS_PER_KEY 9 FROM USER_INDEXES WHERE TABLE_NAME = 'T1' AND INDEX_NAME = 'T1_I1'; NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ------------- ---------- ----------- ----------------- ----------------------- ----------------------- 10000 500 2 1111 9754 2 19 --收集表统计信息 admin@ORCL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for all columns size 1') PL/SQL procedure successfully completed. admin@ORCL> select small_vc from T1 where n1 = 2 and n2 = 3; 13 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2886394002 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 340 | 25 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 340 | 25 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | T1_I1 | 20 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("N1"=2 AND "N2"=3) filter("N2"=3) --字段的selectivity admin@ORCL> select COLUMN_NAME,NUM_DISTINCT from user_tab_col_statistics where table_name = 'T1' and column_name in ('N1','N2'); COLUMN_NAME NUM_DISTINCT ------------------------------ ------------ N1 25 N2 20 第一步的成本: = blevel+ceil((n1.selectivity*n2.selectivity)*leaf_blocks) = 2+ceil((1/20*1/25)*1111) = 5 第二步的成本 =ceil(n1.selectivity*n2.selectivity*clustering_fact) = 20 通过以上的数据模型可以看出,涉及到Oracle的COST成本包括字段的可选择性(不收集直方图),索引的叶子块数,及索引的集群因子。