RE: CBO Bug - Tables with 0 rows

1. Yes, if you are inside a loop, AE suppresses %UpdateStats because it
implies a commit.
3. There is a workaround described in the blog for locking statistics.  You
need to wrap DBMS_STATS inside another PL/SQL procedure that handles the
ORA-20005 produced by gathering statistics on a table with locked stats.
The version in the blog deals with PT>8.48, the download from the book deals
with PT<=8.47.x


1.)     Yes this is an App Engine program in my case.  The %UpdateStats
macro only works (I believe) if the App Engine does a commit.  If the App
Engine doesn't do a commit prior to this, it disallows %UpdateStats.  That
might not be exactly right, but there is some caveat about using
%UpdateStats that causes it not to work in this particular step.

2.)     I'll have to look at the OPTIMIZER_DYNAMIC_SAMPLING parameter.  That
might be that I need.

3.)     Locking stats (I found out) was NOT a good idea.  Because the App
Engine apparently does do an UpdateStats later in the process and it causes
it to fail with an ORA-? Error about table stats being locked.

4.)     I'm going to check out that blog now.

 

Thanks,

 



regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
<http://www.psftdba.com/> 
DBA Blogs: PeopleSoft: http://blog.psftdba.com <http://blog.psftdba.com/> ,
Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk/> 
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

Other related posts: