Finding Sql Execution Statistics by Date

I had client recently with an interesting problem. He was running replication from his primary site in one state, to standby server in another state several hundred miles away. He was paying for DSL speed replication to his destination site to keep his costs down. His problem was that all of a sudden the amount of data that was being copied to his remote site had increased, and he did not know why. He had not made an application change, and the application had not been recently upgraded, so he did not understand why the amount of data being replicated had suddenly increased.
In the past, on many occasions I have seen cases where single Oracle scripts were executed hundreds or thousands of times in very short time frames, simply because the programmer had nested the statement inside a loop instead of outside the loop. This is not even truly careless programming, often it’s just overlooking a simple error because the code does what is supposed to do. Catching code that is simply being run more often than it should be is not a simple task.
The first item to look at is the AWR or statspack report. They will both show SQL by number of executions. I have seen execution counts of tens of thousands per hour, when the code should only be run once per hour.
In the case of this particular client, he knew the date that the problem had started (which really pointed to an undocumented change in procedure or the application). So I wrote a SQL script to count executions by date. Using this, the client was able to determine the source of his increased IO, and then he had a little chat with his developers about letting him know about application changes. As it turns out, it was a programming problem that was subsequently fixed.
The script is below. This script can easily be modified to accept a date. It lists by sql_id the number of executions of each sql statement before and after the specified date. Once you have the sql_id, it is a simple matter to access dba_hist_sqltext by sql_id to get the full text. Obviously this is for Oracle 10g and above with the tuning pack. Getting this same information from statspack may be possible, but would be much more difficult.

select
s.sql_id,
sum(case
when begin_interval_time = to_date(’14-feb-2009 1100′,’dd-mon-yyyy hh24mi’) then s.executions_total
else 0
end) sum_after,
(sum(case
when begin_interval_time >= to_date(’14-feb-2009 1100′,’dd-mon-yyyy hh24mi’) then s.executions_total
else 0
end) –
sum(case
when begin_interval_time < to_date(’14-feb-2009 1100′,’dd-mon-yyyy hh24mi’) then s.executions_total
else 0
end)) difference
from dba_hist_sqlstat s,
dba_hist_snapshot sn
where sn.begin_interval_time between to_date(’05-nov-2008 0001′,’dd-mon-yyyy hh24mi’) and to_date(’05-nov-2008 2359′,’dd-mon-yyyy hh24mi’) and
sn.snap_id=s.snap_id
group by s.sql_id
order by difference desc;

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: