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

I noticed that the parameter cursor_sharing is set to 'FORCED', is your app not using Bind Variables?

Sent on my BlackBerry® from Vodafone

-----Original Message-----

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com> Sender: oracle-l-bounce_at_freelists.org Date: Thu, 8 May 2014 21:59:28
To: baitha_at_itradenetwork.com<baitha_at_itradenetwork.com>; oracle-l_at_freelists.org<oracle-l_at_freelists.org> Reply-To: iggy_fernandez_at_hotmail.com Subject: RE: DB performance after upgrade from 9i to 11gR2

I agree. That's why it is such a difficult situation. We can't solve a problem without data. Did you send me any explain plans? I haven't received any. BTW, it is perfectly normal and expected for query plans to be different in QA and prod (the statistics are different after all, even if the collection method is the same), and it is perfectly normal and expected for query plans to change from day to day. The only way to force query plans to stay the same is to eliminate the causes of variability such as nightly collection of statistics and bind variable peeking. Or to use techniques such as stored outlines or sql plan management. Also, it is perfectly normal and expected for small changes in data distribution to make big changes in query plans. I'm willing to look at some query plans but I would question what you expect to achieve. Your environments are not identical. QA has different data than prod, different bind variables than prod, different CPU count than prod. Also EXPLAIN PLAN is essentially useless unless it is produced by actually running the query i.e. DBMS_XPLAN.DISPLAY_CURSOR. And, as Carlos would say, a query plan is not enough; one needs all the ancillary information that SQLTXPLAIN collects. Carlos has offered to look at a SQLTXPLAIN collection from QA and prod. I noticed from one of your blog posts that Oracle support is focusing on differences in query plans; they are focusing on ensuring that you have given the optimizer the information it needs (and then hope for the best). Iggy
From: baitha_at_itradenetwork.com
To: iggy_fernandez_at_hotmail.com; oracle-l_at_freelists.org Subject: RE: DB performance after upgrade from 9i to 11gR2 Date: Fri, 9 May 2014 00:31:35 +0000

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

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 Received on Fri May 09 2014 - 07:04:06 CEST