RE: sql timings before and after upgrade
Date: Mon, 7 Sep 2015 15:08:44 -0700
Message-ID: <BLU179-W602D62D9D1752513A1FCFEB540_at_phx.gbl>
You could capture the SQL ID and the last execution metrics once every 5 minutes for one week. That will give you lots of data ponts. re: any longer running SQLs
A reasonable expectation is that you will see lots of such SQLs unless you use stored outlines or optimizer_features_enable.
Date: Mon, 7 Sep 2015 15:24:46 -0500
Subject: sql timings before and after upgrade
From: veeeraman_at_gmail.com
To: oracle-l_at_freelists.org
List,
We are considering upgrading couple of our systems to newer version of oracle. If we see any *longer* running SQLs after the upgrade showing up in the top, we would like to know how long that same SQL took before the upgrade. I did some research on the awrddrpt.sql, but it looks like it only lists the TOP 10 SQLs. We can adjust the top N SQLs captured, but I am thinking that could incur more overhead; I dont know how much more.
If there are SQLs that I see in the top of the list after the upgrade that were not in the list before the upgrade, I want to know the time the SQL(s) took before the upgrade. I saw a few awr reports last week that captured only 7%, 9% or 12% of the top SQLs that ran in the report interval. If I have an old report like that with 90% of the SQLs' information not captured, I dont know how I can say for sure the time some new SQL that shows up after upgrade performed before the upgrade.
Is the only way to see the timings of all SQLs is to increase the TopN sql retention setting? v10.2.
Thanks,
Ram.
--
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 08 2015 - 00:08:44 CEST