oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集统计信息呢_part3

最新推荐文章于 2023-03-25 15:20:54 发布

clg10051

最新推荐文章于 2023-03-25 15:20:54 发布

阅读量127

点赞数

个人简介：

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院

河北廊坊新奥集团公司

项目经验:

中国电信3G项目AAA系统数据库部署及优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

联系方式：

手机：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/

前言

dbms_stats包括众多的过程及函数，功能强悍，本文沿用之前的思路与流程，继续探讨dbms_stats包的其它过程.

测试目标

熟悉与掌握lock_schema_stats,unlock_schema_stats的语法及语义

领悟lock_schema_stats,unlock_schema_stats的应用场景

熟悉与掌握lock_table_stats,unlock_table_stats的语法及语义

领悟lock_table_stats,unlock_table_stats的应用场景

掌握在10g r2版本中与这些相关过程使用的触发bug条件及如何规避它们

测试步骤

1,连接测试用户

SQL> conn tbs_11204/system

Connected.

2,创建测试表

SQL> create table t_lock_stat(a int,b int);

Table created.

3,插入数据

SQL> insert into t_lock_stat select level,mod(level,3) from dual connect by level<=100000;

100000 rows created.

SQL> commit;

Commit complete.

4,收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true);

PL/SQL procedure successfully completed.

5,查看表的统计信息

SQL> col table_name for a15

SQL> set linesize 300

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 100000 244 0 NO

6，lock_table_stats的官方说明

LOCK_TABLE_STATS Procedure

This procedure locks the statistics on the table.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

Parameters

Table 103-48 LOCK_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema
tabname	The name of the table

Usage Notes

When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.
The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.

小结：@如果锁定了表的统计信息，即表的列，柱状图，索引的统计信息同时也被锁定

@如果锁定了表的统计信息，使用set_*,delete_*,import_*,gather_*想去修改某个表的统计信息，会触发一个报错

@如果使用gather_schema_stats类似的过程同时收集多个对象的统计信息，会自动略过锁定统计信息的表，当然，

你可以通过force选项来强制收集被锁定表统计信息的表

@此过程即可以暂时冻结表上面的统计信息，也可以清空表上面的统计信息，以使用动态解样

（注：动态解样技术后续我们会讲解）

@如果使用export_*_stats过程导入对象的统计信息时，表上面的锁定或已解出锁定的状态信息不会被导出

6，锁定上述的测试表

SQL> exec dbms_stats.lock_table_stats(user,'t_lock_stat');

PL/SQL procedure successfully completed.

7,查询表的统计信息

注意：列stattype_locked由空变成ALL

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 100000 244 0 ALL NO

stattype_locked列的3个取值

STATTYPE_LOCKED

VARCHAR2(5)

Type of statistics lock:

DATA
CACHE
ALL

8,锁定表的统计信息后插入数据

SQL> insert into t_lock_stat select level,level from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

9，默认方式收集表的统计信息

默认选项收集表的统计信息，会报错提示表统计信息被锁定

SQL> exec dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true);

BEGIN dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true); END;

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 15027

ORA-06512: at "SYS.DBMS_STATS", line 15049

ORA-06512: at line 1

10，强制方式收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true,force=>true);

PL/SQL procedure successfully completed.

11，查看表的统计信息

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 1100000 5158 0 ALL NO

12,默认方式修改表的统计信息

报错因为表统计信息已锁定

SQL> exec dbms_stats.set_table_stats(user,'t_lock_stat',numrows=>13);

BEGIN dbms_stats.set_table_stats(user,'t_lock_stat',numrows=>13); END;

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 1722

ORA-06512: at "SYS.DBMS_STATS", line 2378

ORA-06512: at "SYS.DBMS_STATS", line 6535

ORA-06512: at line 1

13,强制方式修改表的统计信息

SQL> exec dbms_stats.set_table_stats(user,'t_lock_stat',numrows=>13,force=>true);

PL/SQL procedure successfully completed.

14,查看表的统计信息

可见表的记录变为手工指定的13,其它参数同理，不再复述

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 13 5158 0 ALL NO

15，默认方式收集表所属用户的统计信息

SQL> insert into t_lock_stat select level,level from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

查看表当前记录个数

SQL> select count(*) from t_lock_stat;

COUNT(*)

----------

1130000

收集用户统计信息（大家可以理解为收集当前用户所有对象的统计信息，不止表哟，可能还有索引等等）

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

16，查看表的统计信息

发现被锁定表的统计信息仍旧没有变化，但是stale_stats变成yes,表明表上的统计信息已是陈旧，需要收集新的统计信息

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 13 5158 0 ALL YES

17，强制方式收集表所属用户的统计信息

SQL> exec dbms_stats.gather_schema_stats(user,force=>true);

PL/SQL procedure successfully completed.

18,查看表的统计信息

现在表的记录数已更新过来,stale_stats也更新为NO,表明表统计信息为最新，不用收集

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 1130000 5158 0 ALL NO

19，unlock_table_stats官方说明

UNLOCK_TABLE_STATS Procedure

This procedure unlocks the statistics on the table.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

Parameters

Table 103-71 UNLOCK_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema
tabname	The name of the table

Usage Notes

When statistics on a table is locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.

小结：

和lock_table_stats一致

20,解除对表的锁定，即可以继续收集表的统计信息

SQL> exec dbms_stats.unlock_table_stats(user,'t_lock_stat');

PL/SQL procedure successfully completed.

21,查看表的统计信息

可知列stattype_locked变为空，恢复正常

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 1130000 5158 0 NO

22，lock_schema_stats官方说明

LOCK_SCHEMA_STATS Procedure

This procedure locks the statistics of all tables of a schema.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
   ownname    VARCHAR2);

Parameters

Table 103-47 LOCK_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema to lock

Usage Notes

See "Usage Notes" for LOCK_TABLE_STATS Procedure.

SQL> exec dbms_stats.lock_schema_stats(user);

PL/SQL procedure successfully completed.

23,收集表的统计信息

提示报错，因为锁定了整个用户所有表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true);

BEGIN dbms_stats.gather_table_stats(user,'t_lock_stat',cascade=>true); END;

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 15027

ORA-06512: at "SYS.DBMS_STATS", line 15049

ORA-06512: at line 1

24,收集用户统计信息

SQL> insert into t_lock_stat select level,mod(level,30) from dual connect by level<=30;

30 rows created.

SQL> commit;

Commit complete.

表当前是1130030条记录

SQL> select count(*) from t_lock_stat;

COUNT(*)

----------

1130030

收集用户统计信息

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

表的统计信息没有更新，说明整个用户统计信息被锁定

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 1130000 5158 0 ALL NO

25，unlock_schema_stats官方说明

UNLOCK_SCHEMA_STATS Procedure

This procedure unlocks the statistics on all the tables in schema.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
   ownname    VARCHAR2);

Parameters

Table 103-70 UNLOCK_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema

Usage Notes

When statistics on a table is locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.

小结;

@同上，不再复述

SQL> exec dbms_stats.unlock_schema_stats(user);

PL/SQL procedure successfully completed.

26,查看表的统计信息

可见stattype_locked已恢复为空，可以继续收集表的统计信息了

SQL> select table_name,object_type,num_rows,blocks,empty_blocks,stattype_locked,stale_stats from user_tab_statistics where lower(table_name)='t_lock_stat';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS STATT STA

--------------- ------------ ---------- ---------- ------------ ----- ---

T_LOCK_STAT TABLE 1130000 5158 0 NO

27，lock_table_stats及unlock_table_stats的应用场景

@如果某些业务表变更很有规律，可以锁定这些表的统计信息，确保sql使用某个固定的执行计划

@如果某些业务表是静态表，即数据变更很缓慢，也可以锁定表统计信息,确保sql使用某个固定的执行计划

这样就节省了oracle收集统计信息的工作量，减少了不必要的资源消耗

@如果升级或迁移数据库时，可以通过锁定表统计信息，保存表的某个时间点的统计信息，然后方便迁移

28，lock_schema_stats及unlock_schema_stats的应用场景

@同上

@确认某些ORACLE用户的表变更很有规律或基本是静态表，可以锁定整个用户的统计信息

29,与上述过程相关的bug