1.执行收集统计信息操作之后，查询某张表名，查询系统中记录该表的统计信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off

SELECT ID,
 COLID,
 T_FLAG,
 T_TOTAL,
 N_SMAPLE,
 N_DISTINCT,
 N_NULL,
 V_MIN,
 V_MAX
 FROM SYSSTATS
 WHERE ID IN
 (
  SELECT OBJECT_ID
   FROM DBA_OBJECTS
   WHERE OBJECT_TYPE='TABLE'
   AND upper(OBJECT_NAME) = upper('&v_tab_name')
 );

2.更新统计信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

prompt
prompt
prompt /************************************************************************************************/
prompt /* PARTNAME        : [partname =>'xxx'], use to collect  part of a table,no default value       */ 
prompt /* ESTIMATE_PERCENT: [0-100],default: AUTO_SAMPLE_SIZE                                          */ 
prompt /* BLOCK_SAMPLE    : [TRUE|FALSE],default: FALSE                                                */ 
prompt /* METHOD_OPT      : [FOR ALL COLUMNS SIZE 1|AUTO],default: 'FOR ALL COLUMNS SIZE 1'            */ 
prompt /* DEGREE          : [2 - CPU_COUNT],default NULL                                               */ 
prompt /* CASCADE         : [TRUE|FALSE|AUTO_CASCADE],default DBMS_STATS.AUTO_CASCADE                  */ 
prompt /* NO_INVALIDATE   : [TRUE|FALSE|AUTO_INVALIDATE],default FALSE                                 */ 
prompt /* GRANULARITY     : [ALL|AUTO|GLOBAL|GLOBAL AND PARTITION|PARTITION|SUBPARTITION],default AUTO */ 
prompt /* FORCE           : [TRUE|FALSE],default FALSE                                                 */ 
prompt /************************************************************************************************/ 

select 'exec dbms_stats.gather_table_stats(ownname => '''||'&v_owner'||''''||',tabname =>'''||'&v_tabname'||''''||',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => '||''''||'FOR ALL COLUMNS SIZE 1'||''''||',degree => &v_degree,cascade => TRUE,no_invalidate => FALSE,granularity=>'||''''||'AUTO'||''''||',FORCE=> FALSE);' from dual;