Re: CBO Bug - Tables with 0 rows

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_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
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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