Re: Analyse

Your assumptions are right. However, there were known issues/limitations with dbms_stats in 8.1.6. We have been on 8.1.7 for some time so I am not aware of the details. I have been using this package for some time and had good success.

The only problems I came across while using the statspack is if the index_names were system assigned. For example, if a testing db has an index on a table called sys_yxy and the production db has the same index on the same table called sys_xyx, then importing of stats from testing to production is not possilbe unless you alter the export file itself! The reason the index names are system generated is because we have a third-party application that doesn't the dba's control over the creation of these.

"Jon" <jonov_at_iprimus.com.au> wrote in message news:3d3d1a71$1_1_at_news.iprimus.com.au...
>
> 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:
>
> a) 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');
>
> b) 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');
>
> Thanks.
>
> Monica
>
> PS. I'm using Oracle Server Enterprise 8.1.6.3 on Solaris 2.6 platform.
>
>
>
>
>
Received on Sat Jul 27 2002 - 18:57:35 CDT