2.1.1.2_14 Oracle数据字典 142-90 SYS.DBMS_STATS.set_index_stats

---

相关链接

• 【官】142-90 DBMS_STATS.SET_INDEX_STATS
• 1.Excel目录
• 2.如何找到Oralce存储过程——官方文档
• 3. Oracle执行计划之2_统计信息（Statistic）
  • 3.1 Oralce 收集统计信息方式1_SYS.DBMS_STATS
    • 3.1.1 Oracle SYS.DBMS_STATS存储过程整理
    • 3.1.2 Oracle SYS.DBMS_STATS 传入参数整理
  • 3.2. Oralce收集统计信息方式2_Analyze
• 4. 142-10 SYS.DBMS_STATS.delete_column_stats
• 5. 142-12 SYS.DBMS_STATS.delete_database_stats
• 6. 142-15 SYS.DBMS_STATS.delete_index_stats
• 7. 142-18 SYS.DBMS_STATS.delete_schema_stats
• 8. 142-21 SYS.DBMS_STATS.delete_table_stats
• 9. 142-39 SYS.DBMS_STATS.gather_database_stats
• 10. 142-42 SYS.DBMS_STATS.gather_index_stats
• 11. 142-43 SYS.DBMS_STATS.gather_schema_stats
• 12. 142-45 SYS.DBMS_STATS.gather_table_stats
• 13. 142-87 SYS.DBMS_STATS.set_column_stats
• 14. 142-90 SYS.DBMS_STATS.set_index_stats
• 15. 142-95 SYS.DBMS_STATS.set_table_stats

---

一、Summary of SET_INDEX_STATS Procedure

Sets index-related information
设置索引相关的信息

---

二、SET_INDEX_STATS Procedure

These procedures set index-related information. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.
此过程设置与索引相关的信息。

---

三、Constants 常量

The DBMS_STATS package uses the constants shown in Table 142-1:
Table 142-1 DBMS_STATS Constants

Name 名称	Type 类型	Value 值	Description 描述
AUTO_CASCADE	BOOLEAN	NULL	Lets Oracle decide whether to collect statistics for indexes or not 让Oracle决定是否收集索引的统计信息
AUTO_DEGREE	NUMBER	32768	Lets Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters. For definition of default parallel degree, see “Degree of Parallelism” in Oracle Database VLDB and Partitioning Guide. 让Oracle根据对象的大小、cpu的数量和初始化参数选择并行度。有关默认并行度的定义，请参阅Oracle数据库VLDB和分区指南中的"并行度"。
AUTO_INVALIDATE	BOOLEAN	NULL	Lets Oracle decide when to invalidate dependent cursors 让Oracle决定何时使依赖游标失效
AUTO_SIMPLESIZE	NUMBER	0	Indicates that auto-sample size algorithms should be used 表示自动样本大小应该使用的算法

---

四、SET_INDEX_STATS

4.1 Syntax 语法

参数有default值：可以不传参，使用默认值
参数无default值：必须传参才可调用

DBMS_STATS.SET_INDEX_STATS (
   ownname       VARCHAR2, 
   indname       VARCHAR2,
   partname      VARCHAR2  DEFAULT NULL,
   stattab       VARCHAR2  DEFAULT NULL, 
   statid        VARCHAR2  DEFAULT NULL,
   numrows       NUMBER    DEFAULT NULL, 
   numlblks      NUMBER    DEFAULT NULL,
   numdist       NUMBER    DEFAULT NULL, 
   avglblk       NUMBER    DEFAULT NULL,
   avgdblk       NUMBER    DEFAULT NULL, 
   clstfct       NUMBER    DEFAULT NULL,
   indlevel      NUMBER    DEFAULT NULL, 
   flags         NUMBER    DEFAULT NULL,
   statown       VARCHAR2  DEFAULT NULL,
   no_invalidate BOOLEAN   DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   guessq        NUMBER    DEFAULT NULL,
   cachedblk     NUMBER    DEFAULT NULL,
   cachehit      NUMBER    DEFAULT NULL,
   force         BOOLEAN   DEFAULT FALSE);

Use the following for user-defined statistics:
使用以下用户自定义统计

DBMS_STATS.SET_INDEX_STATS (
   ownname       VARCHAR2, 
   indname       VARCHAR2,
   partname      VARCHAR2  DEFAULT NULL,
   stattab       VARCHAR2  DEFAULT NULL, 
   statid        VARCHAR2  DEFAULT NULL,
   ext_stats     RAW,
   stattypown    VARCHAR2 DEFAULT NULL, 
   stattypname   VARCHAR2 DEFAULT NULL, 
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   cachedblk     NUMBER    DEFAULT NULL,
   cachehit      NUMBER    DEFAULT NULL,
   force         BOOLEAN   DEFAULT FALSE);

4.2 执行存储过程

1.如果是命令窗口就用exec 存储过程名，举个例子：

EXEC  procedure;--procedure是存储过程名

2.如果是PL/SQL窗口就用 begin 存储过程名 end; 举个例子：

begin
  procedure;--procedure是存储过程名
end;

3.如果是程序中调用就用 call 存储过程名 ，举个例子：

hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");//存储过程proc_stuInfo

• plsql测试语句

declare
  -- Boolean parameters are translated from/to integers: 
  -- 0/1/null <--> false/true/null 
  no_invalidate boolean := sys.diutil.int_to_bool(:no_invalidate);
  force boolean := sys.diutil.int_to_bool(:force);
begin
  -- Call the procedure
  sys.dbms_stats.set_index_stats(ownname => :ownname,
                                 indname => :indname,
                                 partname => :partname,
                                 stattab => :stattab,
                                 statid => :statid,
                                 numrows => :numrows,
                                 numlblks => :numlblks,
                                 numdist => :numdist,
                                 avglblk => :avglblk,
                                 avgdblk => :avgdblk,
                                 clstfct => :clstfct,
                                 indlevel => :indlevel,
                                 flags => :flags,
                                 statown => :statown,
                                 no_invalidate => no_invalidate,
                                 guessq => :guessq,
                                 cachedblk => :cachedblk,
                                 cachehit => :cachehit,
                                 force => force);
end;

or

declare
  -- Boolean parameters are translated from/to integers: 
  -- 0/1/null <--> false/true/null 
  no_invalidate boolean := sys.diutil.int_to_bool(:no_invalidate);
  force boolean := sys.diutil.int_to_bool(:force);
begin
  -- Call the procedure
  sys.dbms_stats.set_index_stats(ownname => :ownname,
                                 indname => :indname,
                                 partname => :partname,
                                 stattab => :stattab,
                                 statid => :statid,
                                 ext_stats => :ext_stats,
                                 stattypown => :stattypown,
                                 stattypname => :stattypname,
                                 statown => :statown,
                                 no_invalidate => no_invalidate,
                                 cachedblk => :cachedblk ,
                                 cachehit => :cachehit ,
                                 force => force);
end;

4.3 Parameters 参数

Table 142-90 SET_INDEX_STATS Procedure Parameters

Ser 序号	Parameter 参数名称	Type 类型	Default	IO TYPE	Note 参数说明	Range 取值范围
1	ownname	VARCHAR2		IN	name of the schema. 方案名	[Any Schema]
2	indname	VARCHAR2		IN	Name of the index 此列所属的表名	[Any Index]
3	partname	VARCHAR2	Y	IN	Name of the index partition in which to store the statistics. If the index is partitioned and if partname is NULL, then the statistics are stored at the global index level. 用于存储统计信息的索引分区名称。如果索引是分区的，且partname=NULL，则统计信息存储在索引全局级别（global index level）。	NULL default [Any index partition]
4	stattab	VARCHAR2	Y	IN	User statistics table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary. 存储统计信息的目标表名。	NULLdefault 　　如果指定参数为NULL，统计信息将直接更新到数据字典。 [Any Stattab] 　　指定要存储统计信息的表。
5	statid	VARCHAR2	Y	IN	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL) statid表明stattab这个数据集的主键，可以理解为stattab的一个分区。(只有当stattab不是NULL时设置statid才有效)	NULLdefault 　　不指定分区。 [Any Statid] 　　指定要存储统计信息的表的分区。
6	ext_stats	RAW		IN	User-defined statistics 用户定义的数据	NULLdefault
7	stattypown	VARCHAR2	Y	IN	Schema of the statistics type 统计信息类型的方案（Schema）	NULLdefault
8	stattypname	VARCHAR2	Y	IN	Name of the statistics type 统计信息类型的名称	NULLdefault
9	numrows	NUMBER	Y	IN	Number of rows in the index (partition) 索引(分区)中的行数	NULLdefault
10	numlblks	NUMBER	Y	IN	Number of leaf blocks in the index (partition) 索引(分区)中的叶块数量	NULLdefault
11	numdist	NUMBER	Y	IN	Number of distinct keys in the index (partition) 索引(分区)中的不同distinct键的数量	NULLdefault
12	avglblk	NUMBER	Y	IN	Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from numlblks and numdist. 每个键distinct后出现在该索引(分区)中的叶块的平均整数量。如果没有提供，则该值来自numlblks和numdist。	NULLdefault
13	avgdblk	NUMBER	Y	IN	Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from clstfct and numdist. 表中由索引(分区)的键distinct后所指向的数据块的平均整数数。如果没有提供，则该值来自clstfct和numdist。	NULLdefault
14	clstfct	NUMBER	Y	IN	See clustering_factor column of the all_indexes view for a description 有关描述，请参见all_indexes视图的clustering_factor列 all_indexes => clustering_factor Indicates the amount of order of the rows in the table based on the values of the index. 　　● If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks. 　　● If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks. For bitmap indexes, this column is not applicable. 集群因子,是衡量数据在数据块中存放的好坏与整齐有序。 该数值越大(越接近行数)，则表示数据大多随机排序，同一叶块的索引项不太可能指向同一数据块中的行。 该值越小（越接近块数），则表示数据排序非常好，单个叶块中索引大多指向相同数据块中的行。	NULLdefault
15	indlevel	NUMBER	Y	IN	Height of the index (partition) 索引(分区)的高度	NULLdefault
16	flags	NUMBER	Y	IN	For internal Oracle use (should be left as NULL) 用于内部Oracle使用(应该保留为NULL)	NULLdefault [Any Number]
17	statown	VARCHAR2	Y	IN	Schema containing stattab (if different than ownname) statown表明stattab在哪个方案（schema）下，如果statown=当前schema，则可以不指定此参数。	NULLdefault [Any Schema] 　　指定要存储统计信息的方案。
18	no_invalidate	BOOLEAN	Y	IN	Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.	DBMS_STATS.AUTO_INVALIDATEdefault 　　默认值（取Oracle常量）。让Oracle决定何时使依赖游标失效。 TRUE 　　依赖游标有效。 FALSE 　　依赖游标失效。
19	guessq	NUMBER	Y	IN	Guess quality. See the pct_direct_access column of the all_indexes view for a description. 猜测质量。有关描述，请查看 all_indexes 视图的 pct_direct_access 列。 all_indexes => pct_direct_access For a secondary index on an index-organized table, the percentage of rows with VALID guess 对于索引组织的表上的次要索引，猜测有效的行的百分比	NULLdefault
20	cachedblk	NUMBER	Y	IN	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) 该段(索引/表/索引分区/表分区)的缓冲区缓存中的平均块数	NULLdefault
21	cachehit	NUMBER	Y	IN	The average cache hit ratio for the segment (index/table/index partition/table partition) 段(索引/表/索引分区/表分区)的平均缓存命中率	NULLdefault
22	force	BOOLEAN	Y	IN	Sets the values even if statistics of the index are locked 当这个参数的值为TRUE时，即使锁表也会强制删除表的统计信息。	TRUE FALSE default

Most of the DBMS_STATS procedures include the three parameters statown, stattab, and statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
大多数DBMS_STATS过程包括三个参数statown、stattab和statid。这些参数允许您将统计信息存储在自己的表中(在字典之外)，这不会影响优化器。因此，您可以维护和试验统计数据集。
The stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown parameter is specified). You can create multiple tables with different stattab identifiers to hold separate sets of statistics.
stattab参数指定用于保存统计信息的表的名称，并且假设它与为其收集统计信息的对象驻留在同一个方案（用户）中(除非指定了statown参数)。您可以使用不同的stattab标识符创建多个表，以保存不同的统计数据集。
The statown, stattab, and statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
statown、stattab和statid参数指示包在收集新的统计信息之前备份指定表中的当前统计信息。

---

4.4 Exceptions 异常

ORA-20000:Object does not exist or insufficient privileges
　　　　　　对象不存在或权限不足
ORA-20001: Invalid input value
　　　　　　输入值无效
ORA-20005: Object statistics are locked
　　　　　　对象统计信息被锁定

---

4.5 Usage Notes 使用方式

 ● To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.　　　　
调用这个过程，你必须是表的所有者，或 ANALYZE ANY 权限。
对于SYS拥有的对象，你需要是表的所有者，或 ANALYZE ANY DICTIONARY 权限或 SYSDBA 权限。
 ● The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
优化器（Optimizer）使用缓存数据来预估访问 索引 / 统计表 时需要的缓存块数量。总代价(cost) 包括 【磁盘读取非缓存块的I/O成本】、【从缓冲区缓存获取缓存块的CPU成本】以及【处理数据的CPU成本】。
 ● Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a ‘confidence factor’ for each cachehit and a cachedblk for each object. If the ‘confidence factor’ for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
Oracle始终维护（20）cachedblk 和（21）cachehit，但只有当用户在自动模式下调用 DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS 过程或在手动模式下调用 DBMS_STATS.GATHER_SYSTEM_STATS 时，才使用相应的缓存统计信息作为表和索引统计信息的一部分进行优化。为了防止用户使用不准确和不可靠的数据，优化器将计算每个cachehit的“置信因子”和每个对象的cachedblk。如果值的“置信因子”满足置信标准，则将使用此值，否则将使用缺省值。
 ● The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other “minor” workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
自动维护算法假设系统只有一个主要工作负载来进行对象的缓存统计信息，并根据这个工作负载调整统计信息，忽略其他“较小”的工作负载。如果需要调整，则必须使用手动模式来维护对象缓存统计信息。
 ● The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
用于自动模式的对象缓存统计信息维护算法阻止您在下列情况下使用统计信息
  ○ When not enough data has been analyzed, such as when an object has been recently create
当分析的数据不够时，比如最近创建了一个对象
  ○ When the system does not have one major workload resulting in averages not corresponding to real values.
当系统没有一个主要工作负载时，导致平均值与实际值不对应。

---

20/11/02

M