Just to be accurate, thats not actually an Oracle bug. Oracle is working the way it is designed, its the PS implementation that has the problem. On Nov 29, 2007 2:16 PM, Taylor, Chris David <Chris.Taylor@xxxxxxxxxxxxxxx> wrote: > Yes. We run into this often in PeopleSoft. > > Due to all the temp and work tables that app uses. They constantly have > 0 rows. Then the App will insert rows and do joins on the tables that > stats says have 0 rows. > > No fun. > > Here's a perfect example query: > > > DELETE FROM PS_PO_RECON_WK1 WHERE PROCESS_INSTANCE = 999999 > AND EXISTS > ( SELECT 'X' FROM PS_PO_HDR B WHERE B.PO_STATUS = 'C' AND > B.BUSINESS_UNIT = PS_PO_RECON_WK1.BUSINESS_UNIT AND B.PO_ID = > PS_PO_RECON_WK1.PO_ID ) > > The PS_PO_RECON_WK1 table gets 290,000 rows inserted into it, the App > does some stuff with the data and then runs this delete. The delete > takes 1.5 hours to run. > > If I fix the stats on PS_PO_RECON_WK1, the delete takes approximately > 25-30 seconds with a completely different execution plan. > > But you know what? That same app that does the delete does a TRUNCATE > Table, and also reanalyzes the table, so the row_count gets set back to > 0. > > I had to create a trigger that has an autonomous transaction inside it > on inserts to reload stats from a STATTAB. Seems to be working ok for > now. > > > > Chris Taylor > Sr. Oracle DBA > Ingram Barge Company > Nashville, TN 37205 > Office: 615-517-3355 > Cell: 615-354-4799 > Email: chris.taylor@xxxxxxxxxxxxxxx > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Best, David > Sent: Thursday, November 29, 2007 2:00 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: CBO Bug - Tables with 0 rows > > > > Hey all, > > Has anyone heard of CBO bugs on tables with 0 rows or a low row count > (<6)? Appparently sub-optimal plans were generated causing performance > issues. The solution was to delete statistics on those tables. I was > kind of surprised when I heard this so I searched on Metalink and can't > find anything. > > Thanks > -- > http://www.freelists.org/webpage/oracle-l > > > > > -- > http://www.freelists.org/webpage/oracle-l > > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'