RE: Find out how often SQL is run
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "GiantPanda@xxxxxxx" <GiantPanda@xxxxxxx>, "Oracle-l@xxxxxxxxxxxxx" <Oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Feb 2010 10:17:44 +0100
Hi,
You can find this information in the awr tables.
The execution times and such will be averages for the executions between the 2
snapshots.
set linesize 120
set pages 999
column avg_elapsed_sec format 99999D99
column disk_reads format 999999999
column buffer_gets format 999999999
select to_char(snap.begin_interval_time, 'DD/MM/YYYY HH24:MI:SS') btime,
to_char(snap.end_interval_time, 'DD/MM/YYYY HH24:MI:SS') etime,
snap.snap_id,
snap.instance_number, sqlstat.executions_delta nbr_executions,
(sqlstat.elapsed_time_delta / sqlstat.executions_delta/1000000)
avg_elapsed_sec,
(sqlstat.disk_reads_delta / sqlstat.executions_delta) disk_reads,
(sqlstat.buffer_gets_delta / sqlstat.executions_delta) buffer_gets
from dba_hist_snapshot snap, dba_hist_sqlstat sqlstat
where snap.snap_id = sqlstat.snap_id(+)
and snap.instance_number = sqlstat.instance_number(+)
and sql_id = '&sql_id'
order by snap.snap_id, snap.instance_number;
Note that you are only allowed to access the awr tables when you have licensed
the diagnostic pack.
Which is not available for Standard Edition, although the tables are there and
awr snapshots are enabled by default (and no, I don't know what the guys at
Oracle where smoking when they came up with this rule).
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ingrid Voigt
Sent: woensdag 17 februari 2010 21:02
To: Oracle-l@xxxxxxxxxxxxx
Subject: Find out how often SQL is run
Hi,
I need to find out how often a particular query (identified by
SQL id) is run. If possible also how long executions take and
if the execution plan varies. Database version is 10.2.0.4 Standard
Edition on Windows.
Do I have to schedule a job to query v$sqlarea regularly? Or is there
something better (Statspack?)
Thanks for your help.
Regards
Ingrid Voigt
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
