Re: Oracle 9i (9.2.0.1.0) statistics gathering

I had a look at the link and it led me in the right direction! I started playing around with hints in the sql that caused the long execution time and after a couple of tries I got lucky. The RULE hint made the procedure almost as fast as the plain sql itself. I wouldn't say I have solved the problem because I do not think that hints should be necessary for the optimizer but still, it saves me a lot of execution time.

It would be interesting to now what the status is with Oracle optimization in pl/sql. Apparently there has been som changes in the optimizer behaviour at least since earlier releases of 8i.

What still bothers me though, is the strange fact that I needed to run the procedure once, then run the import_schema_stats procedure before the procedure ran sufficiently fast. Without the first "slow" call to the procedure, it didn't benefit from the statistics.

/Björn

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f27d284$0$18491$ed9e5944_at_reading.news.pipex.net>...
> 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
> > > >
> > > >
Received on Thu Jul 31 2003 - 07:55:40 CDT