RE: CBO Bug - Tables with 0 rows
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: <andrew.kerber@xxxxxxxxx>, <Chris.Taylor@xxxxxxxxxxxxxxx>
- Date: Fri, 30 Nov 2007 00:14:31 -0000
I can suggest two possibly more reliable and easier to implement options to
deal with stats on temporary working storage tables.
i) I assume that this is a PeopleSoft Appliation Engine program. The
PeopleSoft solution is to have your developers analyze the table by adding
the PeopleSoft %UpdateStats macro to the code.
ii) The generic Oracle solution is to delete stats on the table, _and_ set
OPTIMIZER_DYNAMIC_SAMPLING to 2 (this defaults to 1 in 9i, and 2 in 10g - 1
isn't usually enough). In Oracle 10g you can also lock the statistics to
prevent statistics being inadvertantly gathered.
If there are no statistics, Oracle will generate CBO statistics on the fly
at parse time based on a small sample of the table. There are also ways to
do this just for the PORECON process.
see also
http://blog.psftdba.com/2007/05/updatestats-v-optimizer-dynamic.html
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
<http://www.psftdba.com/>
DBA Blogs: PeopleSoft: http://blog.psftdba.com <http://blog.psftdba.com/> ,
Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk/>
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Andrew Kerber
Sent: Thursday, November 29, 2007 8:39 PM
To: Chris.Taylor@xxxxxxxxxxxxxxx
Cc: David.Best@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: CBO Bug - Tables with 0 rows
Importance: High
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 <mailto:chris.taylor@xxxxxxxxxxxxxxx>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto: <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
<//www.freelists.org/webpage/oracle-l>
--
//www.freelists.org/webpage/oracle-l
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
