Oracle 9i (9.2.0.1.0) statistics gathering

I have a pl/sql stored procedure that takes forever to run (I have run it from sqlplus and over jdbc). It selects data from two joined tables and "returns" the selected data as an in/out ref cursor variable. One of the tables that is involved in the query has about 50000+ rows in it but hasn't been analyzed or had its statistics gathered since it contained much fewer rows.

If i run the dbms_stats procedure gather_schema_stats for the schema (cascade => true as only other parameter set) the procedure executes sufficiently fast but what I just realised is that these statistics disappear after the database has been shut down. This is perhaps not so strange, I admit.

I have tried to keep the statistical data by creating a table for it with the create_stat_table procedure and then calling export_schema_stats. After restart of the db I run the import_schema_stats and rerun the stored procedure but with the same negative result as without any statistics gathered. If I instead of running the import_schema_stats procedure immediately after startup try to run my stored procedure (that takes forever) and when it returns run the import_schema_stats procedure, all other calls to my slow stored procedure run as fast as I want them to.

My question is how I can get the benefits of the gathered statistics already after db startup without having to run problem queries once and then importing the statistics.

Thanks,
Björn Qvarsell Received on Tue Jul 29 2003 - 03:49:44 CDT