RE: DB performance after upgrade from 9i to 11gR2
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 8 May 2014 12:46:50 -0700
Message-ID: <BLU179-W66A2A8469C866A326142CEEB490_at_phx.gbl>
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 problemO for observations: Collect objective data such as statspack, AWR, RDA, SQLTXPLAIN, etcI 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. etcD 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
Date: Thu, 8 May 2014 12:46:50 -0700
Message-ID: <BLU179-W66A2A8469C866A326142CEEB490_at_phx.gbl>
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 problemO for observations: Collect objective data such as statspack, AWR, RDA, SQLTXPLAIN, etcI 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. etcD 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-lReceived on Thu May 08 2014 - 21:46:50 CEST