Received: (qmail 7204 invoked from network); 6 Jun 2011 13:59:41 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 6 Jun 2011 13:59:28 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A32ABE29319; Mon, 6 Jun 2011 14:59:16 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1307386756; bh=jmG0LWsFI4Fha8ey8EcK/YFkld7PmtowGXGQe4S4 ys8=; h=From:To:Date:Subject:Message-ID:References:In-Reply-To: Content-Type:MIME-Version:Sender:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=qW5SQ6Ch0JBwtK6EInhEL6YpFgIpBpsNC8bTWcgwz6f2aWzMbU v5rVft7QPYJFNXeFZQ2WGrzX7QdJQeTkNfhpb77ZsxSNAa9MdfbE79iG9MplmBdND1s mFoqwlXmY4hFjNQR4YJtYs0u/+ovwYevNI+trnJHQtFGpgSLjbQQew= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JuiSdG3ez38L; Mon, 6 Jun 2011 14:59:16 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 440AEE292E1; Mon, 6 Jun 2011 14:58:33 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 06 Jun 2011 14:57:51 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F905E292E0 for ; Mon, 6 Jun 2011 14:57:51 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JpmOHsO84IEG for ; Mon, 6 Jun 2011 14:57:51 -0400 (EDT) Received: from smtp02.constellation.com (smtp02.constellation.com [216.99.187.52]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 91CC3E291F7 for ; Mon, 6 Jun 2011 14:57:50 -0400 (EDT) Received: from profgen.ceg.corp.net (HELO EXH-OMF-01.Ceg.Corp.Net) ([10.103.87.52]) by smtp02.constellation.com with ESMTP/TLS/RC4-MD5; 06 Jun 2011 14:57:49 -0400 Received: from EXM-OMF-04.Ceg.Corp.Net ([10.103.87.102]) by EXH-OMF-01.Ceg.Corp.Net ([10.103.87.52]) with mapi; Mon, 6 Jun 2011 14:57:49 -0400 From: "Jorgensen, Finn" To: "'pdthedba@gmail.com'" , 'Oracle-L Group' Date: Mon, 6 Jun 2011 14:57:48 -0400 Subject: RE: Testing Process for Gathering single object stats. Thread-Topic: Testing Process for Gathering single object stats. Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF02445527D93E@EXM-OMF-04.Ceg.Corp.Net> References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_9CE162BC5ED2C643956B526A7EDE46FF02445527D93EEXMOMF04Ceg_" MIME-Version: 1.0 X-archive-position: 36595 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Finn.Jorgensen@constellation.com Precedence: normal Reply-To: Finn.Jorgensen@constellation.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_9CE162BC5ED2C643956B526A7EDE46FF02445527D93EEXMOMF04Ceg_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable PD, This sounds to me like one of those political battles you can't win. Your s= r. management has already made up their minds and they have more than enoug= h knowledge to be dangerous. If you manage to convince them to do things di= fferently than they propose and things go wrong again, it's your neck on th= e line. Why would you risk that? Since they have already designed the solut= ion 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 yo= ur 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 afte= r 20 years in this business, that's what I would do. Thanks, Finn From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@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 i= s very heavily scrutinized here whatever the level is. And one of such chan= ges 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 dep= ending 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 senio= r technical management wants a process around it - for gathering this singl= e object stats (I know!). I think I need to explicitly mention here that th= is 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 sta= ts are stale and in our PROD copy env (where the issue exists) gathering st= ats reverts to original good plan! So we are not gathering just because the= y are stale but instead because that staleness is actually causing a realti= me 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 n= ever know how many, and to what, those plan change and it can change for wo= rse 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 c= olumn stock_level) and if we gather stats only on one of them and since tho= se 2 cud be highly related (in queries etc.) that may mess up the join card= inality etc. messing up the plans etc. Now, you see they know Oracle as well ! My Oracle (and optimizer knowledge) clearly suggests me that these argument= s 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 yo= u 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 s= trategy/process that you can suggest to us pls? Another interesting point is that, they are not even very clear at this sta= ge 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 te= sting 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 t= o 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 wa= nt 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 t= he addressee. If you are not the intended recipient, do not use the informati= on in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2 --_000_9CE162BC5ED2C643956B526A7EDE46FF02445527D93EEXMOMF04Ceg_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

PD,<= /o:p>

 

This sounds to me like one of those political batt= les you can’t win. Your sr. management has already made up their mind= s and they have more than enough knowledge to be dangerous. If you manage t= o convince them to do things differently than they propose and things go wr= ong 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 i= t works, great. It solves everybody’s problem. If it doesn’t wo= rk you know have a chance to save the day with your own solution (provided = you are able to solve the problem).

 

I know th= is was not your question and not what you wanted to hear, but after 20 year= s in this business, that’s what I would do.

 

Thanks,

Fin= n

 =

From: oracle-l-bounce@freelists.org [mailto= :oracle-l-bounce@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.

 

Hell= o Oracle Experts,

 =

I work a critical system and due to som= e high stakes all and every change is very heavily scrutinized he= re whatever the level is. And one of such changes which is currently under&= nbsp;scrutiny is gathering object stats for single objects. Just to gi= ve you a background its an Oracle eBusiness site so fnd_stats is used inste= ad of usual dbms_stats and we've an inhouse job that depending on the stale= ness of the objects gather stats on them using FND_STATS. (RDBMS : 10.2.0.4= Apps Release 12i).

&nbs= p;

Now, we've seen that occas= ionally it leaves some of the objects that should ideally be gathered = so they need to be gathered individually and our senior technical managemen= t wants a process around it - for gathering this single object stats (I kno= w!). I think I need to explicitly mention here that this need to gather sta= le object stats has emerged becs one of the plans has gone pretty poor (fro= m 2 ms to 90 mins) and sql tuning task states that stats are stale and in o= ur PROD copy env (where the issue exists) gathering stats reverts to origin= al good plan! So we are not gathering just because they are stale but inste= ad 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 fo= r worse causing unexpected issues in the service! 

ii. There may be related objects whose objects h= ave gone stale as well (for example sales and inventory tables both see rel= ated amount of changes on column stock_level) and if we gather stats only o= n one of them and since those 2 cud be highly related (in queries etc.) tha= t may mess up the join cardinality etc. messing up the plans etc.=  

 

<= /div>

Now, you see they know Oracle as well !=

 

<= p class=3DMsoNormal>My Oracle (and optimizer knowledge) clearly suggests me= that these arguments are baseless BUT want to keep an open mind. So my que= stions are :

 

i.   Do the risks highlighted abo= ve 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?<= o:p>

 

Another interesting point is that, they are not eve= n very clear at this stage how they are gonna 'test' this whole thing as th= e '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 tha= t may not necessarily the case and I obviously want an honest exp= ert assessment of the situation and not merely my backing.

 

Thanks so much in advance!

>>> This e-mail and any attachments are confidential, m= ay contain legal,
professional or other privileged information, and are intended solely for t= he
addressee.  If you are not the intended recipient, do not use the info= rmation
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP= 2 --_000_9CE162BC5ED2C643956B526A7EDE46FF02445527D93EEXMOMF04Ceg_-- -- http://www.freelists.org/webpage/oracle-l