Re: DB performance after upgrade from 9i to 11gR2

+1

Without some trace data or AWR/ASH reports it's merely guesswork as to what the problem is and, as a result, what the solution should be.  

I'll suggest again generating 10053 traces for both QA and Prod for the same query and see where the optimizer may be going wrong.  'Bad' statistics and guesses for missing stats should show up in the trace file.  Seeing the queries can also help (provided that's not a breach of company rules).  I can think of a number of possibilities that could cause this but not knowing the statements or the decision trees the optimizers make to arrive at the plans it isn't wise to make such speculation.


 
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"

On Thursday, May 8, 2014 1:48 PM, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:
 
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
To: baitha_at_itradenetwork.com
CC: mark.powell2_at_hp.com; 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-l