As Niall said...
However does the application drop and recreate the tables en-passant .. ?
are they temp. tables ?
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3f26491f$0$18489$ed9e5944_at_reading.news.pipex.net...
> "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
>
>
Received on Tue Jul 29 2003 - 06:13:45 CDT
