oracle 查看索引大小_技术分享|简述Oracle统计信息

最新推荐文章于 2024-08-12 10:29:22 发布

weixin_39671964

最新推荐文章于 2024-08-12 10:29:22 发布

阅读量317

点赞数

文章标签： oracle 查看索引大小 oracle过程包保存乱码

于树文

云技术管理处

在Oracle的11g版本中，统计信息为自动收集功能。在部署安装11g Oracle软件过程中，其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)。有时候在生产环境中，还会对一些对象做手动统计信息的搜集。统计信息对执行计划的准确性有着至关重要的参考，本文介绍自动统计信息搜集及常规操作。

什么是统计信息

说统计信息前，先要说下Oracle的优化器。

Oracle数据库中的优化器又叫查询优化器(Query Optimizer)。它是SQL分析和执行的优化工具，负责生成、制定SQL的执行计划。Oracle的优化器有两种，即基于规则的优化器(RBO)与基于代价的优化器(CBO)。

RBO自ORACLE 6以来被采用，一直沿用至ORACLE 9i。ORACLE 10g开始，ORACLE已经彻底丢弃了RBO，它有着一套严格的使用规则，只要按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到“执行计划”，也就是说RBO对数据不“敏感”。它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大于全表扫描；在RBO中，SQL的写法往往会影响执行计划，这就要求开发人员非常了解RBO的各项细则，有时候不理解规则写出来的SQL脚本性能可能非常差。

CBO是一种比RBO更加合理、可靠的优化器。它是从ORACLE 8中开始引入，但到ORACLE 9i中才逐渐成熟，在ORACLE 10g中完全取代RBO。CBO是计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。

如果对一次执行SQL时发现涉及对象(表、索引等)没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，动态的收集表和索引上的一些数据信息。

优化器统计范围：

表统计：

--行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；

列统计：

--列中唯一值的数量(NDV)，NULL值的数量，数据分布；

--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；

索引统计：

--叶块数量，等级，聚簇因子；

--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；

系统统计：

--I/O性能与使用率；

--CPU性能与使用率；

--存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中；

统计信息自动搜集策略

自动统计收集默认在夜间进行，对所有更改活动中等的对象自动统计应该足够。

使用如下命令查看：

set lines 180 pages 9999

col WINDOW_NAME for a20

col REPEAT_INTERVAL for a70

col ENABL for a15

col DURATION for a15

SELECT WINDOW_NAME,

REPEAT_INTERVAL,

ENABLED,

ACTIVE,

duration

FROM dba_scheduler_windows;

可以看出默认每周一至周五22:00开始，历时4小时进行统计信息的自动收集；每周六和周日早上06：00开始，历时20小时收集统计信息；

手动收集统计信息的情况：

1. 频繁变化的表在白天的活动期间被TRUNCATE/DROP并重建；

2. 块加载超过本身总大小10%的对象；

3. 对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的；

4. 其他。

自动收集功能启用与禁用

1、查看自动收集统计信息的任务及状态

SQL> select client_name,status from dba_autotask_client;

其中"auto optimizer stats collection"便是要寻找的自动收集统计信息的任务名称，它的状态目前是启用状态。

查看该任务所包含的执行窗口

SQL> SELECT *

FROM DBA_SCHEDULER_WINGROUP_MEMBERS

WHERE WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';

2、禁止自动收集统计信息的任务

禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成。

SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

此时"auto optimizer stats collection"任务已经被禁用。

3、启用自动收集统计信息的任务

SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

自动功能带来了统计数据采集上的便捷，但是Oracle11g自动收集统计信息的时间是22:00--2:00。这个时间段往往是业务的高峰期，给原本已紧张的系统带来更大的负担。所以，应该把自动执行的时间改到空闲的时段。

4、获得当前自动收集统计信息的执行时间

set lines 180 pages 9999

col WINDOW_NAME for a20

col REPEAT_INTERVAL for a70

col ENABL for a15

col DURATION for a15

SELECT WINDOW_NAME,

REPEAT_INTERVAL,

DURATION

FROM dba_scheduler_windows;

其中：

WINDOW_NAME：任务名

REPEAT_INTERVAL：任务重复间隔时间

DURATION：持续时间

修改自动任务步骤如下：

(1)停止任务：

BEGIN

DBMS_SCHEDULER.DISABLE(

name => '"SYS"."FRIDAY_WINDOW"',

force => TRUE);

END;

(2)修改任务的持续时间，单位是分钟：

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"SYS"."FRIDAY_WINDOW"',

attribute => 'DURATION',

value => numtodsinterval(180,'minute'));

END;

(3)开始执行时间，BYHOUR=2，表示2点开始执行：

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"SYS"."FRIDAY_WINDOW"',

attribute => 'REPEAT_INTERVAL',

value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

END;

(4)开启任务：

BEGIN

DBMS_SCHEDULER.ENABLE(

name => '"SYS"."FRIDAY_WINDOW"');

END;

(5)查看修改后的情况：

set lines 180 pages 9999

col WINDOW_NAME for a20

col REPEAT_INTERVAL for a70

col ENABL for a15

col DURATION for a15

SELECT WINDOW_NAME,

REPEAT_INTERVAL,

ENABLED,

ACTIVE,

duration

FROM dba_scheduler_windows;

统计信息的导出导入

此操作常用于对表做了大批量的DML，或者数据库刚迁移完，还没到自动统计信息搜集的时候。使用DBMS_STATS包导出与导入统计信息。

1.首先创建保存统计信息的信息表

STAT_TABLE

begin

dbms_stats.create_stat_table(

ownname => 'CLOUD',

stattab => 'STAT_TABLE');

end;

2.导出统计信息到stat_table里

begin

dbms_stats.export_table_stats(

ownname => 'CLOUD',

tabname => 'EMP',

stattab => 'STAT_TABLE');

end;

保存原有统计信息到STAT_TABLE中，目的是3过程不成功，执行步骤4恢复原表的统计信息。

3.收集统计信息

begin

dbms_stats.gather_table_stats(

ownname => 'CLOUD',

tabname => 'EMP');

end;

4.导入统计信息到原表里

begin

dbms_stats.import_table_stats(

ownname => 'CLOUD',

tabname => 'EMP',

stattab => 'STAT_TABLE');

end;

总结

Oracle统计信息给优化器带来很大的便利，优化器类似于导航，统计信息相当于统计每个道路的车辆(数据量)，使导航能够正确的统计每条路线汽车的数量，以便规划出一条畅通快速且消耗时间较小的导航路线图(执行计划)。希望大家能够从文中对执行计划有简单的认识。

补充：

其他dbms统计信息常用包如下，这里不做详细介绍，各位有兴趣可查看Oracle官方手册。

EXPORT_COLUMN_STATS：导出列的分析信息

EXPORT_INDEX_STATS：导出索引分析信息

EXPORT_SYSTEM_STATS：导出系统分析信息

EXPORT_TABLE_STATS：导出表分析信息

EXPORT_SCHEMA_STATS：导出方案分析信息

EXPORT_DATABASE_STATS：导出数据库分析信息

IMPORT_COLUMN_STATS：导入列分析信息

IMPORT_INDEX_STATS：导入索引分析信息

IMPORT_SYSTEM_STATS：导入系统分析信息

IMPORT_SCHEMA_STATS：导入方案分析信息

IMPORT_DATABASE_STATS：导入数据库分析信息

GATHER_INDEX_STATS：分析索引信息

GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列(索引)信息

GATHER_SCHEMA_STATS：分析方案信息

GATHER_DATABASE_STATS：分析数据库信息

GATHER_SYSTEM_STATS：分析系统信息

weixin_39671964

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 查看索引大小_技术分享|简述Oracle统计信息

于树文云技术管理处在Oracle的11g版本中，统计信息为自动收集功能。在部署安装11g Oracle软件过程中，其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)。有时候在生产环境中，还会对一些对象做手动统计信息的搜集。统计信息对执行计划的准确性有着至关重要的参考，本文介绍自动统计信息搜集及常规操作。01什么是统计信息说统计信息前，先要说下Oracle的优化器。Oracle数...
复制链接

扫一扫