Oracle Database 12c DBMS_STATS详解(一)

作为一个dba,性能优化是少不了的事情,而这其中,dbms_stats这个包,则更是绕不过去的东西。
因此我就打算把12c官方文档Oracle® Database PL/SQL Packages and Types Reference 12c Release 1 (12.1)中的第153章的内容翻译一下,并加一些说明和例子进去,以便日后参考。
原文链接:
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#ARPLS059153章 DBMS_STATS

利用DBMS_STATS这个包,你可以查看或者修改优化器为数据库对象收集的统计信息。
你也可以参考oracle 数据库sql优化指南: http://docs.oracle.com/cd/E16655_01/server.121/e15858/toc.htm

本章包含如下主题
如何使用DBMS_STATS
概述
被丢弃的子程序
类型
常量
操作时的注意事项
被丢弃的子程序
例子
数据结构
DBMS_STATS 子程序概述

如何使用DBMS_STATS
本部分包含如下与使用DBMS_STATS相关的主题:
概述
被丢弃的子程序
类型
常量
操作时的注意事项
被丢弃的子程序

概述
oracle数据库允许你收集各种不同类型的统计信息,从而来辅助提升数据库性能。但是DBMS_STATS则只关注优化器使用的统计信息。默认情况下,oracle会自动收集这些统计信息,因此,DBMS_STATS包更倾向于在一些特殊场景下使用。
这些统计信息既可以储存在数据库的字典表里面,也可以存储在你自己创建的用户表中。 通过使用该系统包,你也可以收集或者管理用户自己定义的关于表或者域索引的统计信息。
例如,当你在删除标准的统计信息的时候调用了
DELETE_COLUMN_STATS子程序,该列上的用户自定义的统计信息也将会被删除。
需要注意的是,只有存储在数据库字典表中的统计信息,才会影响到cbo优化器。
当然,在使用DBMS_STATS收集统计信息的时候,你也可以采用并行操作来加快执行速度。

想了解更多关于“管理优化器统计信息”的内容,请参阅:
http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_stats.htm#TGSQL389

被丢弃的子程序
注意:oracle建议你不要再使用这些已经被丢弃的子程序,oracle支持这些被丢弃的特性只是为了向后兼容罢了。
如下的子程序在12c以及以后的版本中已被丢弃:
GENERATE_STATS 该过程已被 GATHER_INDEX_STAT代替。

类型
本章用到的类型如下:
TYPE numarray IS VARRAY(2050) OF NUMBER;
TYPE datearray IS VARRAY(2050) OF DATE;
TYPE chararray IS VARRAY(2050) OF VARCHAR2(4000);
TYPE rawarray IS VARRAY(2050) OF RAW(2000);
TYPE fltarray IS VARRAY(2050) OF BINARY_FLOAT;
TYPE dblarray IS VARRAY(2050) OF BINARY_DOUBLE;

TYPE ObjectElem IS RECORD (
ownname VARCHAR2(30), — owner
objtype VARCHAR2(6), — ‘TABLE’ or ‘INDEX’
objname VARCHAR2(30), — table/index
partname VARCHAR2(30), — partition
subpartname VARCHAR2(30)); — subpartition
type ObjectTab is TABLE of ObjectElem;

该类型是为了展示统计信息差别报告:
TYPE DiffRepElem IS RECORD (report CLOB,
— stats difference report
maxdiffpct number);
— max stats difference (percentage)
type DiffRepTab is table of DiffRepElem;

常量
DBMS_STATS用到的常量如下
名称 类型 值 说明
AUTO_CASCADE BOOLEAN NULL 让oracle自己决定是否收集索引的统计
信息
AUTO_DEGREE NUMBER 32768 让oracle自己决定在执行操作时的并行
度。该并行度会基于对象的大小、cpu的
个数和初始化参数来设置。欲了解数据库
默认的并行度设置,请参阅 Oracle
Database VLDB and Partitioning Guide
中的“并行度”部分。
AUTO_INVALIDATE BOOLEAN NULL 让oracle自己决定什么时候让依赖游标
(dependent cursors)无效。
AUTO_SAMPLE_SIZE NUMBER 0 表明将使用自动采样大小算法。

操作时的注意事项
DBMS_STATS中的子程序可以完成如下一般操作:
收集优化器使用的统计信息;
设置或者获取统计信息;
删除统计信息;
传输统计信息;
锁定或者解锁统计信息;
恢复或者清除历史统计信息;
用户自定义统计信息;
pending统计信息;
比较统计信息;
扩展统计信息。

DBMS_STATS中的大部分过程,基本都包含statown,stattab和statid三个参数。这些参数允许你将统计信息存储到你自己的表中(而非系统字典表中),这些统计信息不会影响cbo。因此,你可以维护这些存储在你自己的表中的统计信息,并利用它们来做一些实验。
stattab参数指定存储统计信息的表名,并且它假设该表与被收集统计信息的对象在同一个schema下面(除非指定了statown参数)。你可以利用不同的stattab来创建多个表,从而来存储统计信息的各种不同的集合。
另外,你也可以通过指定statid参数,来在一个stattab中维护多个不同集合的统计信息,这样可以避免由于多个用户造成的混乱。
对于以SET和GET打头的过程,如果没有指定stattab(也就是你在调用这些过程时,该参数为null),那么这些操作就会直接针对系统字典表中的统计信息进行处理;因此,如果你只是打算处理系统字典表中的统计信息,你就没有必要创建用户表来来存储统计信息。但是,如果stattab不是null,那么这些以SET和GET打头的过程会针对用户特定的统计信息表进行处理,而不是针对系统字典表。
通过使用DBMS_STATS中的SET_DATABASE_PREFS、SET_GLOBAL_PREFS、SET_SCHEMA_PREFS和SET_TABLE_PREFS过程,你也可以改变一些默认参数的值。
DBMS_STATS中的大部分过程,都会提交当前事物,然后执行操作,然后再次提交。
大部分过程都有一个参数:force,该参数允许你覆盖任意被锁定的统计信息。无论何时,只要数据库字典表中的统计信息被修改,那么旧版本的统计信息都将会被系统自动保存,以备将来恢复时使用。

收集优化器使用的统计信息
可以使用如下的子程序来收集某些类别的统计信息,与使用ANALYZE命令相比,这些子程序在收集统计信息的时候可能会带来性能上的提升:
GATHER_DATABASE_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS

这些GATHER_*过程也可以收集用户为列和域索引设置的自定义统计信息。
statown,stattab,statid参数会让DBMS_STATS在收集新的统计信息之前将当前的统计信息备份到指定的表当中。
当你在一些相关的对象上拥有足够的统计信息时,oracle也指定了如下的过程来生成派生对象的统计信息:
GATHER_STAT

Posted in Oracle.