Dbms_stats replay performance problems

Dbms_stats replay performance problems

 

When some functions are updated on site, the system determines to check the on-site execution plan and transmit the statistical data using dbms_stats.

Use a user with a DBA role to log on

Create statistical table SQL> exec dbms_stats.create_stat_table ('ahsimis ', 'qik'); -- schema, table name PL/SQL procedure successfully completed

Export the statistical information of a table to a statistical table.

Export the statistical value. You can export the table or the statistical values of schema, database, and column. SQL> exec dbms_stats.export_table_stats ('ahsimis ', 'ac20', '', 'qik'); -- schema, statistical table, null, statistical table name   PL/SQL procedure successfully completed

 

Export table statistics to a file

Use a tool to export data from a Qik table to an SQL statement.

Create a local statistical table with the same name as the schema

Import SQL statements.

Import the contents of the statistical table to the local schema.

The imported user must have the DBA permission. Note that the table structure for exporting and importing statistical values should be consistent; otherwise, an error will be reported during import. SQL> exec dbms_stats.import_table_stats ('ahsimis ', 'ac20', '', 'qik ');   PL/SQL procedure successfully completed

Delete a statistical table

SQL> exec dbms_stats.drop_stat_table ('ahsimis ', 'qik'); -- schema, table name   PL/SQL procedure successfully completed

 

 

Wonder

2008-10-06

 

Appendix 1: For other information about dbms_stats, dbms_stats has a bug.

(Http://yumianfeilong.com/2007/05/28/dbms_stats-vs-analyze2/)

Using dbms_stats for statistics on gather tables and indexes will invalidate index monitoring. 10.2.0 . 1 is normal, but it appears again in 10.2.0.3. Using the analyze statement will not cause this problem.

Appendix 2: three methods for data analysis

L analyze

Analyze table Scott compute statistics;

Analyze table Scott estimate statistics sample 25 percent;

ANALYZE table scott estimate statistics sample 1000 rows;

analyze index sc_idx compute statistics;

analyze index sc_idx validate structure;

L dbms_utility.analyze_schema

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',estimate_rows => 1000);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent 

=> 25);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

L dbms_stats.gather_schema_stats

exec 

DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

exec 

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'

                              ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', 

estimate_percent => 25);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

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

exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');