Re: Oracle 9i (9.2.0.1.0) statistics gathering

Thank you both for taking the time to answer,

No, the tables are not dropped nor recreated by the sp and they're ordinary tables, not temporary. The procedure does an "open <cursor> for select...", that's it!

If I run the select statement itself, it always executes as fast as a "good" run of the procedure, with or without a call to the dbms_stats.import_schema_stats procedure...

What happens when a stored procedure with an 'in out ref cursor' variable is executed? Why can it not benefit from the statistics that exist? I ran the query as Niall suggested and it reports the correct number of rows in the table. The last_executed value is also correct.

Thanks again,
/Björn

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<KFsVa.25183$pK2.39575_at_news.indigo.ie>...
> 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 Wed Jul 30 2003 - 07:14:49 CDT