Re: Oracle 9i (9.2.0.1.0) statistics gathering

You may find the following of use

http://www.jlcomp.demon.co.uk/faq/sqlplsql.html

Statistics would appear to be a blind alley - since they are clearly there and the plain sql is always efficient.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"Björn Qvarsell" <bq_at_home.se> wrote in message
news:2fbe2ad0.0307300414.6c92a763_at_posting.google.com...


> 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

> > >

> > >