---

相关链接

• 1.首页导航
• 【官】142-39 DBMS_STATS.GATHER_DATABASE_STATS

---

一、Summary of GATHER_DATABASE_STATS Procedure

Gathers statistics for all objects in the database
收集数据库中所有对象的统计信息

---

二、GATHER_DATABASE_STATS Procedure

This procedure gathers statistics for all objects in the database.
此过程收集数据库中所有对象的统计信息。

---

三、Constants 常量

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

---

四、GATHER_DATABASE_STATS

4.1.a Syntax 1 语法

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

DBMS_STATS.GATHER_DATABASE_STATS (
   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,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.1.b Syntax 2 语法

DBMS_STATS.GATHER_DATABASE_STATS (
   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,
   options          VARCHAR2 DEFAULT 'GATHER',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.2 执行存储过程

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

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

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

begin
  procedure;--procedure是存储过程名
end;
1
2
3

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

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

• plsql测试语句 a

declare
  -- Boolean parameters are translated from/to integers: 
  -- 0/1/null <--> false/true/null 
  block_sample boolean := sys.diutil.int_to_bool(:block_sample);
  cascade boolean := sys.diutil.int_to_bool(:cascade);
  gather_sys boolean := sys.diutil.int_to_bool(:gather_sys);
  no_invalidate boolean := sys.diutil.int_to_bool(:no_invalidate);
  gather_temp boolean := sys.diutil.int_to_bool(:gather_temp);
  gather_fixed boolean := sys.diutil.int_to_bool(:gather_fixed);
  -- Non-scalar parameters require additional processing 
  objlist sys.dbms_stats.objecttab;
  obj_filter_list sys.dbms_stats.objecttab;
begin
  -- Call the procedure
  sys.dbms_stats.gather_database_stats(estimate_percent => :estimate_percent,
                                       block_sample => block_sample,
                                       method_opt => :method_opt,
                                       degree => :degree,
                                       granularity => :granularity,
                                       cascade => cascade,
                                       stattab => :stattab,
                                       statid => :statid,
                                       options => :options,
                                       objlist => objlist,
                                       statown => :statown,
                                       gather_sys => gather_sys,
                                       no_invalidate => no_invalidate,
                                       obj_filter_list => obj_filter_list);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

• plsql测试语句 b

declare
  -- Boolean parameters are translated from/to integers: 
  -- 0/1/null <--> false/true/null 
  block_sample boolean := sys.diutil.int_to_bool(:block_sample);
  cascade boolean := sys.diutil.int_to_bool(:cascade);
  gather_sys boolean := sys.diutil.int_to_bool(:gather_sys);
  no_invalidate boolean := sys.diutil.int_to_bool(:no_invalidate);
  gather_temp boolean := sys.diutil.int_to_bool(:gather_temp);
  gather_fixed boolean := sys.diutil.int_to_bool(:gather_fixed);
  -- Non-scalar parameters require additional processing 
  obj_filter_list sys.dbms_stats.objecttab;
begin
  -- Call the procedure
  sys.dbms_stats.gather_database_stats(estimate_percent => :estimate_percent,
                                       block_sample => block_sample,
                                       method_opt => :method_opt,
                                       degree => :degree,
                                       granularity => :granularity,
                                       cascade => cascade,
                                       stattab => :stattab,
                                       statid => :statid,
                                       options => :options,
                                       statown => :statown,
                                       gather_sys => gather_sys,
                                       no_invalidate => no_invalidate,
                                       obj_filter_list => obj_filter_list);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

4.3 Parameters 参数

Table 142-39 GATHER_DATABASE_STATS Procedure Parameters

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: Insufficient privileges
　　　　　　权限不足
ORA-20001: Bad input value
　　　　　　输入参数错误

4.5 Usage Notes 使用方式

To run this procedure, you need to have the SYSDBA role or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
调用这个过程，你有SYSDBA角色，或 ANALYZE ANY DICTIONARY + ANALYZE ANY SYSTEM 权限。

---

20/10/26

M