RE: CBO Bug - Tables with 0 rows

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 30 Nov 2007 14:39:10 -0000
Message-ID: <014c01c8335e$c62f7340$0a0a0a0a@GOFASTER4>

Yes, if you are inside a loop, AE suppresses %UpdateStats because it implies a commit.
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_at_go-faster.co.uk
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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 30 2007 - 08:39:10 CST