SQL Script

Gather Table Statistics

Updated: 24-Nov-2004
Version: 3.0

Description

Dynamically analyzes all tables for an owner using the gather_schema_stats procedure. This uses the 9i new feature of auto estimating the sample size.

This script also takes a copy of the existing stats in a table STATS_BACKUP in case this is required for either exporting to another database or restoring back.

Parameters

1 - Table Owner

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


REM This script stores the current statistics in a table SYSTEM.STATS_BACKUP in case they
REM are required

set echo on
set feed on
set timing on
set verify off
set serveroutput on

ACCEPT own PROMPT 'Enter value for table owner : '

set timing on

DECLARE

l_statown VARCHAR2(30) := 'SYSTEM';
l_stattab VARCHAR2(30) := 'STATS_BACKUP';
l_statid  VARCHAR2(30) := '&own._STATS';
l_stat_tspace VARCHAR2(30) := 'TOOLS';

BEGIN

begin
   dbms_stats.create_stat_table ( ownname => l_statown
                                , stattab => l_stattab
                                , tblspace => l_stat_tspace );
exception when others then 
   null;
end;

dbms_stats.gather_schema_stats  
                    (ownname  => '&own'
                    , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
                    , degree => 5 
                    , cascade => true
                    , method_opt => 'for all columns size 75' 
                    , statown => l_statown
                    , stattab=>l_stattab 
                    , statid=>l_statid) ;

END;
/

Site Map

SQL Script

Gather Table Statistics

Updated: 24-Nov-2004Version: 3.0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 24-Nov-2004
Version: 3.0