RE: Testing Process for Gathering single object stats.
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "'pdthedba@xxxxxxxxx'" <pdthedba@xxxxxxxxx>, 'Oracle-L Group' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Jun 2011 14:57:48 -0400
PD,
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
