DBMS_STATS

DBMS_STATS

EXPORT STATS:
------------------------------------------------------

DBMS_STATS.EXPORT_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   cascade  BOOLEAN  DEFAULT TRUE,
   statown  VARCHAR2 DEFAULT NULL);

execute DBMS_STATS.export_table_stats('SYSTEM','MSWMART1.MTH_CUSTOMER_SELLOUT',NULL,'DBMS_STATS_TABLE',NULL,TRUE,NULL);

PL/SQL procedure successfully completed.

DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname VARCHAR2,
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

SQL> exec dbms_stats.export_schema_stats('MSWMART1','DBMS_STATS_TABLE','MSWMART1','SYSTEM');

PL/SQL procedure successfully completed.

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

TRANSFER STATS:
---------------------------------------------------------

1. Create the statistics table.
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM' ,stat_tab => 'DBMS_STATS_TABLE' , tblspace => 'TOOLS');
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'DBMS_STATS_TABLE');

2. Export statistics to statistics table
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');

3. Import statistics into the data dictionary.
exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');

4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

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

GATHER STATS:
---------------------------------------------------------

EXEC dbms_stats.gather_table_stats('MSWMART1','MTH_ST_INVOICE',estimate_percent => dbms_stats.auto_sample_size,degree => 8, cascade => true);

EXEC dbms_stats.gather_schema_stats('DW1_DBA', cascade=>TRUE);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

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

To Delete Stats:
select 'exec dbms_stats.delete_schema_stats('''|| username || ''');' from dba_users where username not in ('SYS','SYSTEM');

To Export Stats:

select 'exec dbms_stats.export_schema_stats('''||username||''',''STATS_TABLE'','''||username||''',''SYSTEM'');' from dba_users where username != 'SYS' order by username

To Import Stats:

select distinct 'exec dbms_stats.import_schema_stats('''||statid||''',''STATS_TABLE'','''||statid||''',''SYSTEM'');' from system.stats_table;

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

Scheduling Stats:
---------------------------------------------------------
Scheduling the gathering of statistics using DBMS_Job is the easiest way to make sure they are always up to date:

SET SERVEROUTPUT ON
DECLARE
  l_job  NUMBER;
BEGIN

  DBMS_JOB.submit(l_job,
                  'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
                  SYSDATE,
                  'SYSDATE + 1');
  COMMIT;
  DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/
The above code sets up a job to gather statistics for SCOTT for the current time every day. You can list the current jobs on the server using the DBS_JOBS and DBA_JOBS_RUNNING views.

Existing jobs can be removed using:

EXEC DBMS_JOB.remove(X);
COMMIT;

Where 'X' is the number of the job to be removed.

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

DELETE STATS:
--------------------------------------------------------

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

--------------------------------------------------------
GATHER STATS:
--------------------------------------------------------

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

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