RE: CBO Bug - Tables with 0 rows

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

Other related posts: