RE: CBO Bug - Tables with 0 rows

There seems to be a case for having no statistics on those tables and setting 
optimizer_dynamic_sampling to level 2 (the default if OPTIMIZER_FEATURES_ENABLE 
is set to 10.0.0 or higher). YMMV of course.

"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

Other related posts: