手动设定统计数据 set_table_stats

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

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