Re: Testing Process for Gathering single object stats.
Date: Fri, 3 Jun 2011 22:54:31 +0100
Message-ID: <BANLkTimUNYB2n5EN-tgqmvk-JY+AFbDFaw_at_mail.gmail.com>
Problem is that the 'good' stats are not good forever in a changing DB as you'd know .. its a live system and data constantly keeps changing and increasing so needs regular gathering like any normal shop.
Thanks.
On Fri, Jun 3, 2011 at 10:49 PM, Lange, Kevin G <kevin.lange_at_ppoone.com>wrote:
> Just curious on whether the good stats can be locked to avoid the
> processes changing them.
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *PD Malik
> *Sent:* Friday, June 03, 2011 4:39 PM
> *To:* Oracle-L Group
> *Subject:* Testing Process for Gathering single object stats.
>
> Hello Oracle Experts,
>
> I work a critical system and due to some high stakes all and every change
> is very heavily scrutinized here whatever the level is. And one of such
> changes which is currently under scrutiny is gathering object stats for
> single objects. Just to give you a background its an Oracle eBusiness site
> so fnd_stats is used instead of usual dbms_stats and we've an inhouse job
> that depending on the staleness of the objects gather stats on them using
> FND_STATS. (RDBMS : 10.2.0.4 Apps Release 12i).
>
> Now, we've seen that occasionally it leaves some of the objects that should
> ideally be gathered so they need to be gathered individually and our senior
> technical management wants a process around it - for gathering this single
> object stats (I know!). I think I need to explicitly mention here that this
> need to gather stale object stats has emerged becs one of the plans has gone
> pretty poor (from 2 ms to 90 mins) and sql tuning task states that stats are
> stale and in our PROD copy env (where the issue exists) gathering stats
> reverts to original good plan! So we are not gathering just because they are
> stale but instead because that staleness is actually causing a realtime
> problem!
>
> Anyway, my point is that it has been gathered multiple times in the past on
> that object and also it might get gathered anytime by that automatic job
> (run nightly). There arguments are:
>
> i. There may be several hundred sql plans depending on that object and we
> never know how many, and to what, those plan change and it can change for
> worse causing unexpected issues in the service!
> ii. There may be related objects whose objects have gone stale as well (for
> example sales and inventory tables both see related amount of changes on
> column stock_level) and if we gather stats only on one of them and since
> those 2 cud be highly related (in queries etc.) that may mess up the
> join cardinality etc. messing up the plans etc.
>
> Now, you see they know Oracle as well !
>
> My Oracle (and optimizer knowledge) clearly suggests me that these
> arguments are baseless BUT want to keep an open mind. So my questions are :
>
> i. Do the risks highlighted above stand any ground or what probably do
> you think is there of happening any of the above?
> ii. Any other point that I can make to convince the management.
> iii. Or if those guys are right, Do you guys use or recommend any testing
> strategy/process that you can suggest to us pls?
>
> Another interesting point is that, they are not even very clear at this
> stage how they are gonna 'test' this whole thing as the 'cost' option like
> RAT (Real Application Testing) is out of question and developing an inhouse
> testing tool still need analyzing in terms of efforts, worth and
> reliability.
>
> In the end, Can I request top experts from the 'Oak Table' furniture shop
> to make a comment so that I can take their backings!? Well I am hoping here
> they'll back me up but that may not necessarily the case and
> I obviously want an honest expert assessment of the situation and not merely
> my backing.
>
> Thanks so much in advance!
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity
> to which it is addressed. If the reader of this e-mail is not the intended
> recipient or his or her authorized agent, the reader is hereby notified
> that any dissemination, distribution or copying of this e-mail is
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and delete this e-mail immediately.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 03 2011 - 16:54:31 CDT