RE: DB performance after upgrade from 9i to 11gR2
Date: Fri, 9 May 2014 00:31:35 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423EEF359893_at_plt-exch-01.Itradenetwork.com>
Iggy,
Thanks for offering to help and regarding your comments, I believe you must know that company policies restrict sharing certain information. BTW, did you get a chance to look at the explain plans I provided?
Thanks
BA
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iggy Fernandez
Sent: Thursday, May 08, 2014 12:47 PM
To: oracle-l_at_freelists.org
Subject: RE: DB performance after upgrade from 9i to 11gR2
It's a real temptation to construct solutions without getting the full history of the problem, not reviewing diagnostics (statspack, AWR, RDA, SQLTPLAIN, etc), and without a determination of the root cause. It's also very tempting to let the patient tell the doctor what the root cause is.
The best book on Oracle performance that I ever read was The Art of SQL Tuning by Chris Lawson. The technical content is outdated now but I've never forgotten the problem solving methodology.
P for problem: Get as much information and history as you can from the person reporting the problem O for observations: Collect objective data such as statspack, AWR, RDA, SQLTXPLAIN, etc I for implications: Make a judgement on the root cause based on the wealth observations collected in the previous step. S for solution: Finally, we construct a solution. E for execution: Execution may not be a slam dunk. Change control. Minimize downtime. etc D for documentation: Document to help others down the road. Take credit.
In this case, the patient is giving us information in dribbles and telling us that the root cause is changed plans without presenting any evidence to back up the claim. Oracle support seems to be making random guesses too.
Apologies for being preachy instead of making a guess based on little information. Apologies especially to Bheemsen. I know that it is an extremely hard place to be in and management is probably belching fire at this point.
Iggy
Date: Thu, 8 May 2014 21:21:33 +0200
Subject: Re: DB performance after upgrade from 9i to 11gR2 From: exriscer_at_gmail.com<mailto:exriscer_at_gmail.com> To: baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com> CC: mark.powell2_at_hp.com<mailto:mark.powell2_at_hp.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> hi
the first thing you should do is get rid of histograms then check in 11g it's simple to restore statistics so when things goes wrong you can always go back quickly
or
use the pending statistics feature, gather stats and dont publish them then fire a couple of sqlplus sessions with optimizer_use_pending_statistics set to TRUE and test your queries, if they are good then publish, if not good jsut delete them with dbms_stats thanks
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 09 2014 - 02:31:35 CEST