DBA Notes: 2011/09/26

Cheng Li

Oracle database Statistics

Before database migration or upgrade, we should collect and restore database statistics to compare performance before and after image and track performance issue. 

–Collect database stats
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

–Collect schema stats
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

–Collect table stats
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

–collect index stats
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

–delete database 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’);

–-create stats table
begin
dbms_stats.create_stat_table(USER,stattab => ‘STAT_TABLE’);
end;

–-Export table stats
BEGIN
dbms_stats.export_table_stats(USER,tabname => ‘FEI_T’,stattab => ‘STAT_TABLE’);
END;

–-Delete table stats
BEGIN
DBMS_STATS.delete_table_stats(USER,tabname => ‘FEI_T’);
END;

–-Import table Stats
dbms_stats.IMPORT_TABLE_STATS(USER,’FEI_T’,stattab => ‘STAT_TABLE’);
END;

Reference:

http://www.xifenfei.com/1064.html

http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_stats.htm#i997763