How to find at what time of day a select statement was executed and the elapsed time? In sga trace I get only the sum of the executions

Toad for Oracle & DevOps Toolkit
1

Hello,

I need to find at what time of day a select statement was executed and the elapsed time? In sga trace screen I get only the sum of all the executions for this select.

We have a 3 tier application : web client (connection pool), application server and database server.

Working on Oracle 12.1 and Toad 12.7

2

Evening mpniel,

you might see something in the DBA_HIST_SQLSTAT view, however, BEWARE as

that is part of ASH and as such, requires the licencing of the

Diagnostic & Tuning packs.

I think that’s the view name, I’m away from my databases just now, but

it’s definitely DBA_HIST_SQL :wink:

ASH runs a snapshot every second, or so, and collects details of sqls

that are running. OEM is pretty good an deep diving into the ASH tables

when you look into the database’s Performance Home Page, and pages

around there.

Happy hunting.

By the way, it helps if you have the SQL_ID or PLAN_HASH_VALUE when

looking. And a rough idea of the time it was executing.

HTH

–

Cheers,

Norm. [TeamT]

3

I appear to have written, above:

I think that’s the view name, I’m away from my databases just now, but

it’s definitely DBA_HIST_SQL :wink:

However, what I actually wrote had less than and greater than symbols, which appear to have been removed. It should have read (something) like the following:

I think that’s the view name, I’m away from my databases just now, but

it’s definitely DBA_HIST_SQLsomething :wink:

I can confirm, it’s DBA_HIST_SQLSTAT and to get the time, you join it to DBA_HIST_SNAPSHOT using the snapshot id.

HTH

4

Evening All,

this might help. It lists the SQL statements from the ASH history tables (remember, ASH needs licencing!) and shows the times that the snapshots were running:

select
dhss.sql_id,
dhss.executions_delta,
sn.begin_interval_time,
sn.end_interval_time,
sq.sql_text
from
dba_hist_sqlstat dhss,
dba_hist_snapshot sn,
dba_hist_sqltext sq
where
dhss.snap_id = sn.snap_id
and
sq.sql_text like ‘SELECT COUNT%’
and
sq.sql_id = dhss.sql_id;

I’m only interested in SELECT COUNT statements here, for example, your checks might vary.

The results look a little like:

SQL_ID EXECUTIONS_DELTA BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_TEXT

96fqh1b19dh67 1 18-OCT-17 13.12.55.000 18-OCT-17 13.23.54.523 SELECT COUNT(DISTINCT(batch#)) FROM sqllog$

760kzp1j1b8ds 1 18-OCT-17 13.12.55.000 18-OCT-17 13.23.54.523 SELECT COUNT(*) FROM (SELECT FILETYPE_ID, FILETYPE_NAME FROM V$IOSTAT_FILE MINUS

96fqh1b19dh67 1 18-OCT-17 15.32.36.000 18-OCT-17 15.43.34.996 SELECT COUNT(DISTINCT(batch#)) FROM sqllog$

Yes, I know, it’s been a long while since I was last on this database! :wink:

You can see part of the SQL text and the ASH snapshot times when the statement was known to have been running. If you know the SQL_ID, you can search for that, rather then trying to find the SQL text. You should note that the full text of the SQL is not necessarily going to be present in the DBA_HIST_SQLTEXT table.

HTH