Re: Collecting table statistics

From: Tom Pall <tpall_at_cdproc.com>
Date: Fri, 21 Jul 2000 14:43:27 -0500
Message-Id: <10565.112680@fatcity.com>

This is from Metalink:

Note:102334.1

PURPOSE To describe the Oracle 8i new feature MONITORING within the ALTER TABLE command.

SCOPE & APPLICATION For DBA's requiring to automate the updating of statistics as tables are updated.

Under Oracle7 and 8.0, in order to get up-to-date statistics so that the Cost-Based Optimizer can generate accurate execution plans, the DBA must run regular ANALYZE commands whenever significant new amounts of data are created, as there is no way of knowing which tables have been updated and therefore which tables need to be re-analyzed. This is expensive.

Under Oracle8i, the DBA can set a table to be automatically detected when any modification occurs. When enabled, whenever any modification (insert, update, delete, and direct load) occurs on the table or on one of its partitions, the server maintains information in the SGA about how many rows are affected. Therefore, the DBA is now able to re-analyze tables only when changes have occurred on a table.

Periodically (every 3 hours or after a clean shutdown), SMON incorporates the information stored in the SGA into the data dictionary.

These changes are visible through the DBA_TAB_MODIFICATIONS view. Oracle uses this view to identify tables with stale statistics.

The DBA can set on a regular basis a job that executes the procedure DBMS_STATS.GATHER_SCHEMA_STATS or DBMS_STATS.GATHER_DATABASE_STATS with the options parameter set to GATHER STALE to gather statistics for tables with stale statistics.

Note: Using the GATHER option can incur significantly greater overhead

        since this option will likely gather statistics for a greater number
        of tables than GATHER STALE.

The frequency of collection intervals balances the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process on a regular basis.

--------------------------------------------------------------------
Technical Method and Explanation
--------------------------------------------------------------------

1/ Enable the monitoring functionality by issuing the following commands:

     > ALTER TABLE <tablename> MONITORING;
     > CREATE TABLE <tablename> MONITORING;

  2/ Set a job that periodically calls DBMS_STATS.GATHER_SCHEMA_STATS or
     DBMS_STATS.GATHER_DATABASE_STATS with GATHER STALE option, to have
     the database generate statistics on those objects listed in
     DBA_TAB_MODIFICATIONS.

  *** --------------------------------------------------------------------

Example
--------------------------------------------------------------------
- Analyze EMP table to examine current statistics
  SQL> analyze table SCOTT.EMP compute statistics; Table analyzed.

SQL> select table_name, num_rows, blocks, avg_row_len 2 from dba_tables where table_name='EMP';

  TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
  ------------------------------ ---------- ---------- -----------
  EMP                                    14          1          40



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

Enable the monitoring functionality on EMP table
SQL> alter table SCOTT.EMP monitoring; Table altered.

Check that EMP table is monitored
SQL> select table_name, monitoring from dba_tables 2 where owner='SCOTT' 3 order by 2;

  TABLE_NAME                     MON
  ------------------------------ ---
  BONUS                          NO
  DEPT                           NO
  DUMMY                          NO
  PLAN_TABLE                     NO
  STUDENTS                       NO
  SALGRADE                       NO
  LOBTAB                         NO
  EMP                            YES

8 rows selected.

Apply changes on EMP table
SQL> insert into scott.emp select * from scott.emp; 14 rows created. SQL> commit; Commit complete.

Check DBA_TAB_MODIFICATIONS after some time (or after a shutdown)
SQL> select * from sys.dba_tab_modifications;

  TABLE_OWNER                    TABLE_NAME
  ------------------------------ ------------------------------
  PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS
  ------------------------------ ------------------------------ ----------
     UPDATES    DELETES TIMESTAMP TRU
  ---------- ---------- --------- ---
  SCOTT                          EMP
                                                                        14
           0          0 21-MAR-00 NO



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

  -- Execute GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedure

Use a script or job scheduling tool for these procedures to execute
SQL> execute DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', TRUE, NULL, NULL, 'GATHER STALE', 'LIST' ); PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT',

                                               NULL,
                                               FALSE,
                                               'FOR ALL COLUMNS SIZE 1',
                                               NULL,
                                               'DEFAULT',
                                               TRUE,
                                               NULL,
                                               NULL,
                                               'GATHER STALE',
                                               'LIST' );

SQL> select * from sys.dba_tab_modifications; no rows selected.

Re-analyze EMP table to reexamine current statistics
SQL> select table_name, num_rows, blocks, avg_row_len 2 from dba_tables where table_name='EMP';

  TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
  ------------------------------ ---------- ---------- -----------
  EMP                                    28          1          37

NOTE

Whenever you need to stop the monitoring on a table, use the following command: > ALTER TABLE <tablename> NOMONITORING;

RELATED DOCUMENTS

Oracle8i Tuning Release 8.1.5 A67775-01 Chapter 7 ----- Original Message -----
From: Chuck Hamilton
To: Multiple recipients of list ORACLE-L Sent: Friday, July 21, 2000 1:57 PM
Subject: Collecting table statistics

Some time ago I thought I saw something on the list about 8i having the ability to automatically collecet statistics on tables with needing to issue the ANALYZE TABLE command. Can someone tell me how this works or point me to a document that describes it? Is this Received on Fri Jul 21 2000 - 14:43:27 CDT