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_at_ingrambarge.com> 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_at_ingrambarge.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Best, David Sent: Thursday, November 29, 2007 2:00 PM To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 30 2007 - 06:54:02 CST