Testing Process for Gathering single object stats.
- From: PD Malik <pdthedba@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Jun 2011 22:39:13 +0100
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!
Other related posts:
