Re: Oracle 9i (9.2.0.1.0) statistics gathering

"Björn Qvarsell" <bq_at_home.se> wrote in message news:2fbe2ad0.0307290049.6130d0b1_at_posting.google.com...
> 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.

The stats do not disappear between instance restarts. To verify whether the stats are 'disappearing' run

select num_rows from dba_tables where table_name='<your large table>'; num_rows should be non-null.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK