表分析的作用

简单的说，就是收集表和索引的信息，如果没有直方图信息，CBO根据这些信息决定SQL执行计划。

若都没有收集，CBO采取动态采样方式选择执行计划。

测试

--建立测试数据（基于11g）
CREATE TABLE test_fan AS SELECT * FROM dba_tables WHERE ROWNUM<=10000;
CREATE INDEX idx_owner ON test_fan(owner) NOLOGGING PARALLEL 4;
ALTER INDEX idx_owner NOPARALLEL;
--查看列owner分布情况
SELECT owner,COUNT(1) FROM test_fan GROUP BY owner;
OWNER   COUNT(1)
PUBLIC2851
OUTLN10
SYSTEM549
ORACLE_OCM3
SYS6587
查看是否分析表
SELECT d.LAST_ANALYZED,d.TABLE_NAME,d.NUM_ROWS,d.BLOCKS,d.* FROM dba_tablesd WHERE d.TABLE_NAME='TEST_FAN';
查看是否有直方图信息：
SELECT * FROM Dba_Tab_Col_Statistics t WHERE t.TABLE_NAME='TEST_FAN';
都没有的情况下查看如下SQL执行计划
SELECT * FROM test_fan t WHERE t.owner='SYS';--sql1
执行计划：
Plan hash value: 3747397153
 
------------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |    |  6374 |  1288K|  40   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_FAN |  6374 | 1288K| 40  (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."OWNER"='SYS')
 
Note
-----
SELECT * FROM test_fan t WHERE t.owner='OUTLN';--SQL2
Plan hash value: 1852869688
 
-----------------------------------------------------------------------------------------
| Id  | Operation      | Name| Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATE   |  |    10 |  2070 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_FAN  |10 |2070 | 2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN       | IDX_OWNER | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T"."OWNER"='OUTLN')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

从dynamic sampling used for this statement (level=2)可以看出，在没有任何信息情况下，采用动态采样方式获取执行计划而且非常准确。关于动态采样优缺点这里不讨论，有兴趣可以自己查资料学习。

接着分析表，对执行计划影响

对表进行分析：

方式1：

BEGIN

dbms_stats.gather_table_stats(‘PROD_DEV’, ‘TEST_FAN’);

END;

方式2：

ANALYZE TABLE TEST_FAN COMPUTE STATISTICS;

看看表分析对执行计划的影响：

--查看列owner分布情况
SELECT owner,COUNT(1) FROM test_fan GROUP BY owner;
OWNER   COUNT(1)
PUBLIC2851
OUTLN10
SYSTEM549
ORACLE_OCM3
SYS6587
Plan hash value: 3747397153
 
------------------------------------------------------------------------------
| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |  2000| 177K| 40  (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_FAN | 2000 |177K| 40   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."OWNER"='OUTLN')

CBO预估有2000条记录，20%的数据量走了全表扫描。看看表分析是如何计算的。

SELECT t.table_name
      ,t.column_name
      ,t.num_distinct
      ,t.density
      ,t.last_analyzed
      ,t.sample_size
      ,t.HISTOGRAM
FROM   Dba_Tab_Col_Statistics t
      ,Dba_Tab_Columns c
WHERE  t.table_name = 'TEST_FAN'
       AND t.column_name = c.COLUMN_NAME
       AND t.table_name = c.TABLE_NAME
       AND t.column_name='OWNER';
       TABLE_NAMECOLUMN_NAMENUM_DISTINCTDENSITYLAST_ANALYZEDSAMPLE_SIZEHISTOGRAM
TEST_FANOWNER50.22019/10/15 14:0410000NONE

CB0 rows计算公式：

rows=NUM_DISTINCT* SAMPLE_SIZE

例子中预估的rows=0.2*10000=2000

表分析实际统计了每个列唯一值的个数，这样它认为数据平均分配不存在倾斜，每个值得选择率NUM_DISTINCT即为1/COUNT(DISTINCT VALUE)

删除表分析

方式1：

BEGIN

dbms_stats.delete_table_stats(‘PROD_DEV’, ‘TEST_FAN’);

END;

方式2：

ANALYZE TABLE TEST_FAN DELETE STATISTICS;

附上没有任何统计信息切不采用动态采样的执行计划：

EXPLAIN PLAN FOR SELECT/*+dynamic_sampling(0)*/ * FROM test_fan t WHERE t.owner='SYS';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1852869688
 
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name| Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |   | 116  | 24012 |  5   (0)|00:00:01|
|   1 TABLE ACCESS BY INDEX ROWID|TEST_FAN| 116 | 24012 |  5   (0)| 00:00:01|
|*  2 |   INDEX RANGE SCAN    | IDX_OWNER | 46 |    |  1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T"."OWNER"='SYS')