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@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
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.
--