ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【性能优化】Oracle 性能优化：行比例偏差大 建立柱状图

【性能优化】Oracle 性能优化：行比例偏差大 建立柱状图

原创 Oracle 作者：海星星hktk 时间：2014-09-04 22:58:13 1 删除 编辑

行比例偏差大 建立柱状图


当一个表中数据分布不均，某一个查询条件对应的结果比例较大时，通过索引访问代价可能比全表扫描还要高很多。通过实验，模拟表中数据航比例偏差大、但优化器仍然选择索引扫描的情况。通过建立柱状图来影响优化器选择执行计划。


【实验环境】

操作系统：RHEL 5.5

数据库：Oracle 10.2.0


【实验过程】

1、环境部署

1.1、创建表、添加索引

 SCOTT@ prod>create table test2 as select * from emp;

SCOTT@ prod>create index ind_test2_empno on test2(empno);

1.2、分析表

 SCOTT@ prod>analyze table test2 compute statistics;

1.3、使用索引列进行查询

SCOTT@ prod>set autotrace traceonly

SCOTT@ prod>select * from test2 where empno=7788;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

 

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |    87 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2          |     1 |    87 |     2   (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TEST2_EMPNO|     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed


1.4、插入数据

SCOTT@ prod>begin

    for i in 1..10000 loop

    insert into test2 values(7788,'WARD','SALESMAN',7839,sysdate,3000,2000,20);

    end loop;

    end;

    /

PL/SQL procedure successfully completed.

SCOTT@ prod>commit;

 

SCOTT@ prod>select count(*) from test2;

 

  COUNT(*)

----------

     10014

SCOTT@ prod>select count(*) from test2 where empno=7788;

 

  COUNT(*)

----------

     10001

SCOTT@ prod>


1.5、分析表、查看执行计划

SCOTT@ prod>analyze table test2 estimate statistics;

SCOTT@ prod>select * from test2 where empno=7788;

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |   715 | 23595 |    8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2           |   715 | 23595 |    8   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_TEST2_EMPNO |   715 |       |    3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

SCOTT@ prod>


1.6、查看集簇因子

SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR

    from dba_tables t,dba_indexes i

    where t.table_name=i.table_name

    and t.owner='SCOTT'

    and t.table_name='TEST2';

 

TABLE_NAME   NUM_ROWS  BLOCKS AVG_ROW_LEN INDEX_NAME      CLUSTERING_FACTOR

---------- ---------- ------- ----------- --------------- -----------------

TEST2           10014     68          42 IND_TEST2_EMPNO               63


1.7、查看EMPNO列柱状图

SCOTT@ prod>select column_name,num_distinct,num_buckets,histogram

    from user_tab_col_statistics

    where table_name='TEST2' and column_name='EMPNO';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

EMPNO                                    14           1 NONE


通常当查询结果小于3%-5%时，oracle会选择走索引。

本次查询全表数据10014行，查询结果数据10001行，属于行比例偏差较大的情况，以empno=7788条件查询时，走索引反而代价更高。需要为列建立柱状图。

2、建立柱状图

2.1、使用包建立柱状图

SYS@ prod>exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TEST2', method_opt => 'FOR COLUMNS empno');

PL/SQL procedure successfully completed.

2.2、查看EMPNO列柱状图

 SCOTT@ prod>select column_name,num_distinct,num_buckets,histogram

    from user_tab_col_statistics

    where table_name='TEST2' and column_name='EMPNO';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

EMPNO                                    14          14 FREQUENCY

2.3、查看执行计划

 SCOTT@ prod>select * from test2 where empno=7788;

 

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 300966803

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       | 10001 |   332K|    18   (6)| 00:00:01 |

|*  1 | TABLE ACCESS FULL| TEST2 | 10001 |   332K|    18   (6)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

 

SCOTT@ prod>


2.4、查看其他查询条件时执行计划

 SCOTT@ prod>select * from test2 where empno=7499;

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |     1 |    34 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2           |     1 |    34 |    2   (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TEST2_EMPNO |     1 |       |    1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7499)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

这次执行计划选择了索引

【实验总结】

1、通常当查询结果小于3%-5%时，oracle会选择走索引。当行比例偏差较大的时，需要为列建立柱状图。建立柱状图后，查询结果行比例较大时，会走全表扫描。查询结果比例小于3%-5%时，仍然走索引。

2、为列建立柱状图的另一种方法

SCOTT@ prod>analyze table test2 estimate statistics;

SCOTT@ prod>analyze table test2 estimate statistics for columns empno;


吕星昊
2014.9.4

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29475508/viewspace-1264276/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【性能优化】Oracle 性能优化：降低列值聚簇因子 提高查询效率
下一篇： 【性能优化】Oracle 10g 数据库之间复制统计信息
请登录后发表评论 登录
全部评论
海星星hktk
专家姓名：吕星昊
Oracle OCM 讲师。达梦认证讲师DM8-DCI。OceanBase认证讲师OBCI。拥有10g、12c OCM，OBCP等认证证书。曾任 Oracle WDP 注册OCM讲师，已培养Oracle OCP认证专家2000余人，培养Oracle OCM认证大师360余人。

注册时间：2014-02-08

  • 博文量
    128
  • 访问量
    2644749

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593