Two useful Oracle commands to gather table and schema statistics.

smallwei 3:31 pm on October 18, 2009 | Answers: 1    
Tags: oracle (136), oracle commands (3), schema (5), statistics (2)

Rate Knowledge:

 Loading ...

• smallwei 3:36 pm on October 18, 2009
  

  1. To gather statistics on a table:

  sqlplus into the database as sys or system and run the following dbms package. Make sure you replace "SCHEMA_NAME" and "TABLE_NAME".

  >
  begin

  dbms_stats.gather_table_stats(
  ownname=> 'SCHEMA_NAME',
  tabname=> 'TABLE_NAME' ,
  estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade=> DBMS_STATS.AUTO_CASCADE,
  degree=> null,
  no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
  granularity=> 'AUTO',
  method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

  end;
  /

  ==============================================

  2. A quick command to gather statistics on a schema:

  sqlplus into the database as sys or system and simply run the following command:

  >
  exec dbms_stats.gather_schema_stats('SCHEMA_NAME');