如何收集准确的统计信息,查询很慢

yezhibin · 发表于 2009-9-2 13:00

我模拟了以上的类似的场景，具体如下：

1、创建测试表
  SQL> create table t1
      as select 'a' col1, rpad('x', 200) col2 from all_objects where rownum<=100

SQL>insert into t1  select  'b' col1, rpad('x', 200) col2 from all_objects where rownum<=10000;

SQL>create index t1_idx on t1 (upper(col1));

2、统计分析后进行检测：

  SQL>exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);

  SQL>explain plan for select * from t1 where upper(col1)='A';

  SQL>select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |  5050 |  1001K| 70 (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1 |  5050 |  1001K| 70 (2)| 00:00:01 |
--------------------------------------------------------------------------

10053跟踪文件如下：

SINGLE TABLE ACCESS PATH
Column (#3): SYS_NC00003$(CHARACTER)
AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
  Table: T1  Alias: T1
Card: Original: 10100  Rounded: 5050  Computed: 5050.00  Non Adjusted: 5050.00
  Access Path: TableScan
Cost:  69.77  Resp: 69.77  Degree: 0
   Cost_io: 69.00  Cost_cpu: 7351525
   Resp_io: 69.00  Resp_cpu: 7351525
  Access Path: index (AllEqRange)
Index: T1_IDX
resc_io: 159.00  resc_cpu: 3001659
ix_sel: 0.5  ix_sel_with_filters: 0.5
Cost: 159.31  Resp: 159.31  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 69.77  Degree: 1  Resp: 69.77  Card: 5050.00  Bytes: 0

以上是隐藏列SYS_NC00003$被统计分析。
因为该隐藏列参与的CBO的成本计算，在计算CBO的时候全表扫描cost=69.77，而走索引的cost=159.31，所以选择全表扫描

3、删除索引和统计分析

SQL>drop index t1_idx;
SQL>exec dbms_stats.delete_table_stats(user, 't1');
SQL>explain plan for select * from t1 where upper(col1)='A';
SQL>select * from table(dbms_xplan.display);
  --------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       | 101 | 20503 | 11 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1    | 101 | 20503 | 11 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | T1_IDX | 40 |    |    9 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

以上就是和你所发生的现象一样，我们进一步实验，先统计分析，然后再创建索引，最后再对索引进行统计分析
SQL>exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);
SQL>create index t1_idx on t1 (upper(col1));
SQL>exec dbms_stats.gather_index_stats(user, 't1_idx');
SQL>explain plan for select * from t1 where upper(col1)='A';
SQL>select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       | 101 | 20503 | 11 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1    | 101 | 20503 | 11 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | T1_IDX | 40 |    |    9 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

10053 trace 文件：
SINGLE TABLE ACCESS PATH
  Column (#3): SYS_NC00003$(CHARACTER)  NO STATISTICS (using defaults)
AvgLen: 1.00 NDV: 316 Nulls: 0 Density: 0.0031683
  Table: T1  Alias: T1
Card: Original: 10100  Rounded: 101  Computed: 101.00  Non Adjusted: 101.00
  Access Path: TableScan
Cost:  69.76  Resp: 69.76  Degree: 0
   Cost_io: 69.00  Cost_cpu: 7252545
   Resp_io: 69.00  Resp_cpu: 7252545
  Access Path: index (AllEqGuess)
Index: T1_IDX
resc_io: 11.00  resc_cpu: 94356
ix_sel: 0.004  ix_sel_with_filters: 0.004
Cost: 11.01  Resp: 11.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_IDX
      Cost: 11.01  Degree: 1  Resp: 11.01  Card: 101.00  Bytes: 0

注意一点隐藏列没有被统计分析，他的值采用时缺省设置，参与成本计算的时候，全表扫描成本是69.76，但索引的成本扫描时11.01

这就是为什么在建立function based index时候，有一个隐藏列统计分析造成的原因。

zergduan · 发表于 2009-9-2 21:43

yezhibin
我觉得你这个问题是因为缺少直方图，导致的 Density: 0.5 证明oracle 认为upper(col1)是平均分布的，所以不使用索引扫描。你应该收集以下HIDDEN COLUMNS的直方图~

zergduan · 发表于 2009-9-2 22:54

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as MYDB

SQL> exec dbms_stats.delete_table_stats('MYDB','T1');

PL/SQL procedure successfully completed

SQL> explain plan for
  2  select * from t1 where upper(col1)='A';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation          |  Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT    |          |    |    |    |
|*  1 |  TABLE ACCESS FULL | T1       |    |    |    |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("T1"."COL1")='A')
Note: rule based optimization

14 rows selected

SQL> exec dbms_stats.gather_table_stats('MYDB','T1',cascade => TRUE);

PL/SQL procedure successfully completed

SQL> explain plan for
  2  select * from t1 where upper(col1)='A';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation          |  Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT    |          |  3138 | 622K| 25 |
|*  1 |  TABLE ACCESS FULL | T1       |  3138 | 622K| 25 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("T1"."COL1")='A')
Note: cpu costing is off

14 rows selected

SQL> exec dbms_stats.gather_table_stats('MYDB','T1',cascade => TRUE,method_opt => 'FOR ALL HIDDEN COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed

SQL> explain plan for
  2  select * from t1 where upper(col1)='A';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                |  Name    | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          | 100 | 20300 |    4 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1       | 100 | 20300 |    4 |
|*  2 | INDEX RANGE SCAN       | T1_IDX    | 100 |    |    1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("T1"."COL1")='A')
Note: cpu costing is off

15 rows selected

Yong Huang · 发表于 2009-9-3 01:41

Yezhibin,

Lemonair's SQL is

WHERE UPDATE_DATE BETWEEN to_date('08/20/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND ...

not

WHERE somefunction(UPDATE_DATE) someoperator ...

Note the to_date function is applied to the literal value, not to the column name. If it were somefunction(columnname), then an FBI would be helpful.

Yong Huang

huanhuanlove · 发表于 2009-9-11 11:45

哈哈。。。。。。。又学了一招