Analyse

I need some advice on the use of the PL/SQL dbms_stats package.

I am able to analyze the tables in a database, however, I want to keep the current table stats in case there is performance degradation after the analyze is performed. I have been testing the use of the dbms_stats package and I am able to create the stats table and export the current stats in a test database. However, when I imported the stats back, I noticed that the empty_blocks, avg_space, avg_space_freelist_blocks and num_freelist_blocks in dba_tables were all set to zero. I checked this table before the export and the values of these fields were non-zero. I'd like to know if this is the normal behaviour of the dbms_stats.export/import or should I expect to see the original values in these columns?

I found the following statement in the Oracle 8.1.7 Performace & Tuning Guide:

"To verify that table statistics are available, execute the following against the DBA_TABLES:

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,    TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY H24:MI:SS') FROM DBA_TABLES
WHERE TABLE_NAME IN ('TABLE1', 'TABLE2');" The above makes me think that as long as the table_name, num_rows, blocks, avg_row_len and last_analyzed values are imported correctly (which in my tests were) ie same values as before the export, then the optimizer should behave as it did before the analyze tables was performed ie using the former table stats. Would this be a reasonable assumption?

These are the commands I executed:

1. To create the stats table and export current stats: dbms_stats.create_table ('oracle', 'curr_stats'); dbms_stats.export_table_stats ('oracle', 'emp', null, 'curr_stats',
   '23JUL02 1030', true, 'oracle');
   
2. To import the previous stats: dbms_stats.delete_table_stats ('oracle', 'emp'); dbms_stats.import_table_stats ('oracle', 'emp', null, 'curr_stats',
   '23JUL02 1030', true, 'oracle');
   

Monica

PS. I'm using Oracle Server Enterprise 8.1.6.3 on Solaris 2.6 platform. Received on Tue Jul 23 2002 - 11:06:34 CEST