There is no way that you can guarantee a capture of all SQL statement with
times.
You could try sampling v$sqlstats (or v$sql_stats_plan_hash if you have a
version that offers it) regularly, and maybe extract the plan from v$sql_plan
with the stats; but it's almost inevitable that you would some of the
information unless you sampled so frequently that you added too much overhead
to the system.
One strategy you could adopt - which would give you timings - is to enable
event 10132 system wide for 24 hours (or more) before an upgrade so that you
could capture trace files holding the execution plan of every statement
optimised in the interval. If you discovered "newly slow" statement you could
at least find out out what their execution path had been before the upgrade.
Some notes here - and do read the last couple of comments:
https://jonathanlewis.wordpress.com/2006/11/27/event-10132/
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Ram Raman [veeeraman@xxxxxxxxx]
Sent: 07 September 2015 21:24
To: ORACLE-L
Subject: sql timings before and after upgrade
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.
--
