RE: Testing Process for Gathering single object stats.

This sounds to me like one of those political battles you can't win. Your sr. management has already made up their minds and they have more than enough knowledge to be dangerous. If you manage to convince them to do things differently than they propose and things go wrong again, it's your neck on the line. Why would you risk that? Since they have already designed the solution just build what they propose. If it works, great. It solves everybody's problem. If it doesn't work you know have a chance to save the day with your own solution (provided you are able to solve the problem).

I know this was not your question and not what you wanted to hear, but after 20 years in this business, that's what I would do.

Thanks,
Finn

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of PD Malik Sent: Friday, June 03, 2011 5: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 and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

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