RE: Find out how often SQL is run

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sat, 20 Feb 2010 10:33:45 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360757D210_at_CWYMSX04.Corp.Acxiom.net>

Does AWR capture all statements?

If you don't change the TOPNSQL setting, then by default the value is 30, which I interpret to mean the top 30 statements per SQL order category in the AWR report. Does that mean AWR would capture the top 30 statements ordered descending for the following categories?: elapsed time, cpu time, gets, reads, parse calls, executions, version count, sharable memory, and cluster wait time. Would any statement that doesn't fall within those cateogries not be captured?

Just curious, as I've always been under the assumption that not all statements are captured. I tried to test this out but still found a few statements that didn't fit within the top 30 per the above categories (for snapshots with the largest amount of SQL statements captured). Maybe I've got the wrong columns from DBA_HIST_SQLSTAT that I'm using.

Anyone know more details about this?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of D'Hooge Freek Sent: Thursday, February 18, 2010 3:18 AM To: GiantPanda_at_gmx.net; Oracle-l_at_freelists.org Subject: RE: Find out how often SQL is run

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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ingrid Voigt Sent: woensdag 17 februari 2010 21:02
To: Oracle-l_at_freelists.org
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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 20 2010 - 10:33:45 CST

This message: [ Message body ]
Next message: Ingrid Voigt: "Re: Find out how often SQL is run"
Previous message: Surachart Opun: "Re: Cleanup Data Pump queues"
In reply to: D'Hooge Freek: "RE: Find out how often SQL is run"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message