首页 > 其他 > 详细

手动设定统计数据 set_table_stats

时间:2019-02-28 18:27:27      阅读:183      评论:0      收藏:0      [点我收藏+]
PROCEDURE SET_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 NUMROWS                        NUMBER                  IN     DEFAULT
 NUMBLKS                        NUMBER                  IN     DEFAULT
 AVGRLEN                        NUMBER                  IN     DEFAULT
 FLAGS                          NUMBER                  IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 CACHEDBLK                      NUMBER                  IN     DEFAULT
 CACHEHIT                       NUMBER                  IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT


建立表进行测试分析:
create table test2 as select * from dba_objects ;

begin
dbms_stats.gather_table_stats(
OWNNAME => SCOTT,
tabname => TEST2,
cascade => true,
no_invalidate => false);
end;
/

select owner,table_name,tablespace_name,NUM_ROWS from dba_tables where table_name = TEST2 and owner = SCOTT 

OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SCOTT                          TEST2                          USERS                               86372



begin
dbms_stats.set_table_stats(
OWNNAME =>SCOTT,
tabname =>TEST2,
numrows =>3
);
end;
/


select owner,table_name,tablespace_name,NUM_ROWS from dba_tables where table_name = TEST2 and owner = SCOTT ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SCOTT                          TEST2                          USERS                                   3


还需要手动锁定此表的统计数据:

PROCEDURE LOCK_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT


SQL> begin
dbms_stats.lock_table_stats(ownname =>SCOTT,tabname =>TEST2);
end;
/ 

PL/SQL procedure successfully completed.

SQL> select owner,table_name,tablespace_name,NUM_ROWS from dba_tables where table_name = TEST2 and owner = SCOTT ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SCOTT                          TEST2                          USERS                                   3

SQL> begin
dbms_stats.gather_table_stats(
OWNNAME => SCOTT,
tabname => TEST2,
cascade => true,
no_invalidate => false);
end;
/    
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 2

再进行收集,表示对象统计数据已经锁定了,就不会重复收集统计数据信息。

 

手动设定统计数据 set_table_stats

原文:https://www.cnblogs.com/chendian0/p/10452143.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!