ORACLE数据表分析

 

一、性能数据的存储过程：

性能数据的收集包含这样几个存储过程：

GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息

 

二、GATHER_TABLE_STATS： 分析表、字段和索引：

我们分析时最常用到的就是GATHER_TABLE_STATS，dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

procedure gather_table_stats
  (ownname varchar2, tabname varchar2, partname varchar2 default null,
   estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
   block_sample boolean default FALSE,
   method_opt varchar2 default DEFAULT_METHOD_OPT,
   degree number default to_degree_type(get_param('DEGREE')),
   granularity varchar2 default  DEFAULT_GRANULARITY,
   cascade boolean default DEFAULT_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,
   -- the context is intended for internal use only.
   context dbms_stats.CContext default null);

method_opt:决定histograms直方图信息是怎样被统计的。method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):

  • FOR ALL COLUMNS:统计所有列的HISTOGRAMS.
  • FOR ALL INDEXED COLUMNS:统计所有INDEXED列的HISTOGRAMS.
  • FOR ALL HIDDEN COLUMNS:统计你看不到列的HISTOGRAMS
  • FOR COLUMNS <LIST> SIZE <INTEGER> | REPEAT | AUTO |
  • INTEGER指的直方图的BUCKETS数量，取值范围为[1,254]。
  • REPEAT上次统计过的HISTOGRAMS。
  • AUTO:ORACLE根据列数据的分布及相关列的访问量来决定收集直方图的列。
  • SKEWONLY:ORACLE 根据列的数据分布来决定哪些列收集直方图

在 gather_table_stats 存储过程的所有参数中，除了 ownname 和 tabname，其他的参数都有默认值。

dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');

 

三、DBMS_STATS 包管理功能

1、查找表最后执行分析的日期

select table_name,num_rows,blocks,last_analyzedfrom all_tables where table_name='WORK_LIST';

2、其他存储过程

  • CREATE_STAT_TABLE ：创建分析数据表
  • DROP_STAT_TABLE： 删除分析数据表
  • GATHER_TABLE_STATS：执行分析表
  • GET_TABLE_STATS ：获取分析数据
  • SET_TABLE_STATS ： 设置分析数据
  • EXPORT_TABLE_STATS： 导出分析数据
  • IMPORT_TABLE_STATS： 导入分析数据
  • LOCK_TABLE_STATS ：锁定分析数据
  • UNLOCK_TABLE_STATS ：解锁分析数据
  • RESTORE_TABLE_STATS： 恢复分析数据
  • delete_table_stats ：删除表统计信息

 

四、自动收集CBO的统计信息

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。
该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB，然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

所以建议最好关闭这个自动统计信息收集功能

方法之一:

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
--恢复自动分析:
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二：

alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;

Pfile可以直接修改初始化参数文件，重新启动数据库。

关于ORACLE数据表分析的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程宝库。

 一、概述在一个SQL语句中嵌套另一个SQL语句成为子查询。包括单行子查询，多行子查询，多列子查询。注意，当在DDL语句中引用子查询时，可以带有Order By子句；但是当在where子句、 ...