设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 900|回复: 0

[经验分享] Oracle 学习之性能优化（四）收集统计信息

[复制链接]
狼狼

尚未签到
发表于 2018-9-9 09:37:03 | 显示全部楼层 |阅读模式
　　emp表有如下数据。
SQL> select ename,deptno from emp;　　

　　
ENAME   DEPTNO
　　
------------------------------ ----------
　　
SMITH       20
　　
ALLEN       30
　　
WARD       30
　　
JONES       20
　　
MARTIN       30
　　
BLAKE       30
　　
CLARK       10
　　
SCOTT       20
　　
KING       10
　　
TURNER       30
　　
ADAMS       20
　　
JAMES       30
　　
FORD       20
　　
MILLER       10
　　

　　
14 rows selected.
　　假设我们有如下简单的查询
select ename,deptno from emp where ename='RICH' and deptno=10;　　那么Oracle在执行查询的时候，是先比较ename字段呢？还是先比较deptno字段呢？
　　显然先比较deptno再比较ename字段的效率明显低于先比较ename，再比较deptno。 那Oracle究竟如何去判断呢？
　　我们先查询一张表
SQL> COL COLUMN_NAME FOR A30　　
SQL> SELECT column_name, num_distinct, density
　　
  FROM dba_tab_columns
　　
WHERE owner = 'SCOTT' AND table_name = 'EMP';
　　

　　
COLUMN_NAME       NUM_DISTINCT    DENSITY
　　
------------------------------ ------------ ----------
　　
EMPNO 14 .071428571
　　
ENAME 14 .071428571
　　
JOB  5    .2
　　
MGR  6 .166666667
　　
HIREDATE 13 .076923077
　　
SAL 12 .083333333
　　
COMM  4   .25
　　
DEPTNO  3 .333333333
　　

　　
8 rows selected.
　　Oracle其实知道，你的表中存放数据的一些特征，上面语句显示的只是凤毛麟角。通过这些特征，Oracle优化器就能知道如何去查询，使得执行的效率最高。
　　以上这些信息，我们称之为对象的统计信息。那么如何收集统计信息呢？
　　一、 analyze 命令
　　使用analyze命令可以收集统计信息，如：

  • 　　收集或删除对象的统计信息
  • 　　验证对象的结构
  • 　　确定table 或cluster的migrated 和chained rows。
　　示例：
SQL> create user anal identified by anal ;　　

　　
User created.
　　

　　
SQL> grant resource,connect to anal;
　　

　　
Grant succeeded.
　　

　　
SQL> grant select any dictionary to anal;
　　

　　
Grant succeeded.
　　

　　
SQL> conn anal/anal
　　
Connected.
　　
SQL> create table t1 as select * from dba_objects;
　　
SQL> create table t2 as select * from dba_objects;
　　
SQL> create table t3 as select * from dba_objects;
　　
SQL> create table t4 as select * from  dba_objects;
　　
SQL> create table t5 as select * from dba_objects;
　　
SQL> create table t6 as select * from dba_objects;
　　
SQL>  create unique index pk_t1_idx on t1(object_id);
　　
SQL>  create unique index pk_t2_idx on t2(object_id);
　　
SQL>  create unique index pk_t3_idx on t3(object_id);
　　
SQL>  create unique index pk_t4_idx on t4(object_id);
　　
SQL>  create unique index pk_t5_idx on t5(object_id);
　　
SQL>  create unique index pk_t6_idx on t6(object_id);
　　我们先查看一下统计信息是否存在
　　查看表的统计信息
SQL> select table_name, num_rows, blocks, empty_blocks　　
      from user_tables
　　
     where table_name in ('T1', 'T2', 'T3', 'T4', 'T5','T6');
　　查看字段统计信息
select table_name,　　
       column_name,
　　
       num_distinct,
　　
       low_value,
　　
       high_value,
　　
       density
　　
  from user_tab_columns
　　
where table_name in ('T1', 'T2', 'T3', 'T4','T5','T6');
　　查看索引统计信息
SQL> col table_name for a30　　
SQL> col index_name for a30
　　
SELECT table_name,
　　
       index_name,
　　
       blevel,
　　
       leaf_blocks,
　　
       distinct_keys,
　　
       avg_leaf_blocks_per_key avg_leaf_blocks,
　　
       avg_data_blocks_per_key avg_data_blocks,
　　
       clustering_factor,
　　
       num_rows
　　
  FROM user_indexes
　　

　　
TABLE_NAME       INDEX_NAME  BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
　　
------------------------------ ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
　　
T6       PK_T6_IDX       1 155     74564 1 11174  74564
　　
T5       PK_T5_IDX       1 155     74563 1 11174  74563
　　
T4       PK_T4_IDX       1 155     74562 1 11174  74562
　　
T3       PK_T3_IDX       1 155     74561 1 11174  74561
　　
T2       PK_T2_IDX       1 155     74560 1 11174  74560
　　
T1       PK_T1_IDX       1 155     74559 1 11174  74559
　　

　　
6 rows selected.
　　表没有任何统计数据，但是索引已经有统计信息，可见在建立表的时候会默认收集统计信息。
　　先将索引的统计信息删除
SQL> analyze table t1 delete statistics;　　
analyze table t2 delete statistics;
　　
analyze table t3 delete statistics;
　　
analyze table t4 delete statistics;
　　
analyze table t5 delete statistics;
　　
analyze table t6 delete statistics;
　　验证索引上是否还存在统计信息
SELECT table_name,　　
       index_name,
　　
       blevel,
　　
       leaf_blocks,
　　
       distinct_keys,
　　
       avg_leaf_blocks_per_key avg_leaf_blocks,
　　
       avg_data_blocks_per_key avg_data_blocks,
　　
       clustering_factor,
　　
       num_rows
　　
  FROM user_indexes
　　执行统计信息命令，并查看统计信息有无变化
　　analyze table t1 compute statistics for table;
　　--针对表收集信息，查看user_tables
　　analyze table t2 compute statistics for all columns;
　　--针对表字段收集信息，查看user_tab_columns
　　analyze table t3 compute statistics for all indexed columns;
　　--收集索引字段信息
　　analyze table t4 compute statistics;
　　--收集表，表字段，索引信息
　　analyze table t5 compute statistics for all indexes;
　　--收集索引信息
　　analyze table t6 compute statistics for table for all indexes for all columns;
　　--收集表，表字段，索引信息
　　二、DBMS_STATS包
　　Oracle推荐使用DBMS_STATS这个包来收集统计信息。这个包的功能非常多。可以收集数据库级别、schema级别及表级别的统计信息。还可以对统计信息删除、锁定、导出、导入等。我们以最常用的表级别统计为例说明DBMS_STATS该如何使用。
　　收集的统计信存储在dba_tab_statistics、dba_ind_statistics和dba_tab_col_statistics表中。
DBMS_STATS.GATHER_TABLE_STATS (　　
   ownname          VARCHAR2,
　　
   tabname          VARCHAR2,
　　
   partname         VARCHAR2 DEFAULT NULL,
　　
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
　　
                                                (get_param('ESTIMATE_PERCENT')),
　　
   block_sample     BOOLEAN  DEFAULT FALSE,
　　
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
　　
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
　　
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
　　
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
　　
   stattab          VARCHAR2 DEFAULT NULL,
　　
   statid           VARCHAR2 DEFAULT NULL,
　　
   statown          VARCHAR2 DEFAULT NULL,
　　
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
　　
                                     get_param('NO_INVALIDATE')),
　　
   stattype         VARCHAR2 DEFAULT 'DATA',
　　
   force            BOOLEAN  DEFAULT FALSE);
　　参数说明如下：
DSC0000.jpg

DSC0001.jpg

DSC0002.jpg

DSC0003.jpg

　　示例：
SQL> col table_name for a30　　
SQL> SELECT table_name,
　　
       num_rows,
　　
       blocks,
　　
       empty_blocks,
　　
       avg_row_len
　　
  FROM user_tab_statistics;
　　

　　
TABLE_NAME NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
　　
------------------------------ ---------- ---------- ------------ -----------
　　
T1    7455910880   98
　　
T2
　　
T3
　　
T4
　　
T5
　　
T6
　　

　　
6 rows selected.
　　删除统计信息
DBMS_STATS.DELETE_TABLE_STATS (　　
ownname VARCHAR2,
　　
tabname VARCHAR2,
　　
partname VARCHAR2 DEFAULT NULL,
　　
stattab VARCHAR2 DEFAULT NULL,
　　
statid VARCHAR2 DEFAULT NULL,
　　
cascade_parts BOOLEAN DEFAULT TRUE,
　　
cascade_columns BOOLEAN DEFAULT TRUE,
　　
cascade_indexes BOOLEAN DEFAULT TRUE,
　　
statown VARCHAR2 DEFAULT NULL,
　　
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
　　
get_param('NO_INVALIDATE')),
　　
force BOOLEAN DEFAULT FALSE);
　　锁定统计信息
DBMS_STATS.LOCK_TABLE_STATS (　　
ownname VARCHAR2,
　　
tabname VARCHAR2);
　　锁定以后就不能再执行统计信息
SQL> exec dbms_stats.lock_table_stats(user,'T1');　　

　　
PL/SQL procedure successfully completed.
　　

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

　　
*
　　
ERROR at line 1:
　　
ORA-20005: object statistics are locked (stattype = ALL)
　　
ORA-06512: at "SYS.DBMS_STATS", line 23829
　　
ORA-06512: at "SYS.DBMS_STATS", line 23880
　　
ORA-06512: at line 1
　　导出、导入统计信息

  • 　　要导出统计信息首先要建立一个统计表
　　语法：
DBMS_STATS.CREATE_STAT_TABLE (　　
   ownname  VARCHAR2,
　　
   stattab  VARCHAR2,
　　
   tblspace VARCHAR2 DEFAULT NULL);
SQL> exec DBMS_STATS.CREATE_STAT_TABLE (user,'STAT_TMP','SYSAUX');　　

　　
PL/SQL procedure successfully completed.
　　2. 将表t1统计信息导出
DBMS_STATS.EXPORT_TABLE_STATS (　　
   ownname         VARCHAR2,
　　
   tabname         VARCHAR2,
　　
   partname        VARCHAR2 DEFAULT NULL,
　　
   stattab         VARCHAR2,
　　
   statid          VARCHAR2 DEFAULT NULL,
　　
   cascade         BOOLEAN  DEFAULT TRUE,
　　
   statown         VARCHAR2 DEFAULT NULL,
　　
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
SQL> EXEC DBMS_STATS.EXPORT_TABLE_STATS (ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP');　　

　　
PL/SQL procedure successfully completed.
　　3. 导入统计信息
　　语法：
DBMS_STATS.IMPORT_TABLE_STATS (　　
   ownname         VARCHAR2,
　　
   tabname         VARCHAR2,
　　
   partname        VARCHAR2 DEFAULT NULL,
　　
   stattab         VARCHAR2,
　　
   statid          VARCHAR2 DEFAULT NULL,
　　
   cascade         BOOLEAN  DEFAULT TRUE,
　　
   statown         VARCHAR2 DEFAULT NULL,
　　
   no_invalidate   BOOLEAN DEFAULT to_no_invalidate_type(
　　
                                    get_param('NO_INVALIDATE')),
　　
   force           BOOLEAN DEFAULT FALSE,
　　
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
SQL> exec dbms_stats.UNlock_table_stats(user,'T1');　　

　　
PL/SQL procedure successfully completed.
　　

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

　　
PL/SQL procedure successfully completed.
　　

　　
SQL> EXEC DBMS_STATS.IMPORT_TABLE_STATS (ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP');
　　

　　
PL/SQL procedure successfully completed.
　　

　　
SQL> SELECT table_name,
　　
       num_rows,
　　
       blocks,
　　
       empty_blocks,
　　
       avg_row_len
　　
  FROM user_tab_statistics;  2    3    4    5    6
　　

　　
TABLE_NAME NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
　　
------------------------------ ---------- ---------- ------------ -----------
　　
T1    7455910880   98
　　
T2
　　
T3
　　
T4
　　
T5
　　
T6
　　
STAT_TMP
　　

　　
7 rows selected.
　　如果是分区表，新的分区来不及收集统计系统，可以使用其它的分区统计信息来生成新分区的统计信息
DBMS_STATS.COPY_TABLE_STATS (　　
   ownname          VARCHAR2,
　　
   tabname          VARCHAR2,
　　
   srcpartname      VARCHAR2,
　　
   dstpartname      VARCHAR2,
　　
   scale_factor     VARCHAR2 DEFAULT 1,
　　
   force            BOOLEAN DEFAULT FALSE);
　　如果表还没有统计信息，那么在执行sql语句时，Oracle会动态的采样表中的一部分数据，生成统计信息。
SQL> show parameter optimizer_dynamic_sampling ;　　

　　
NAME     TYPE       VALUE
　　
------------------------------------ --------------------------------- ------------------------------
　　
optimizer_dynamic_sampling     integer       2



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.iyunv.com/thread-568539-1-1.html 上篇帖子： Oracle 学习之性能优化（三）绑定变量 下篇帖子： /limits.conf Oracle bug引起的进程不够用
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表