Skip to Main Content
  • Questions

  • Capture Microsecond execution of SQL Execution

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Delboy.

Asked: May 19, 2016 - 5:15 pm UTC

Last updated: May 20, 2016 - 2:47 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

I want to know if a SQL did run in my database. All the tables I look at are capturing either a second or more of the execution time. What if I want to capture a sql that executed so quick it was below a second? Which view would help?

I've looked at DBA_HIST_ACTIVE_SESSION_HISTORY table but the samples are in a second and not in microseconds. I think the closest I have come to is a combination of the dba_hist_sqlstat table and the dba_hist_snapshot table.

There is a SQL that I am convinced doesn't either run on certain days or is so quick to be captured but need proof that either scenario may be the case?

Many thanks,

Delboy

and Connor said...

Check V$SQLSTATS and DBA_HIST_SQLSTAT

It's cumulative data, but assuming the SQL stayed in the library cache for a little while, we probably would have picked it up.

If you want to catch it in future, you could trace just that SQL

alter system set events 'sql_trace [sql:xxxxxxxxx]';

where xxxxxxxxx is the SQL_ID

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Delboy K, May 20, 2016 - 1:27 pm UTC

Hi,

Thanks but then I ran the following SQL;

14:24:02 SQL> set lines 110
14:24:08 SQL> sho user
USER is "SCOTT"
14:24:18 SQL> select * from emp;

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80     800      20
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30
      7566 JONES      MANAGER       7839 02-APR-81    2975      20
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850      30
      7782 CLARK      MANAGER       7839 09-JUN-81    2450      10
      7788 SCOTT      ANALYST       7566 19-APR-87    3000      20
      7839 KING       PRESIDENT     17-NOV-81    5000      10
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30
      7876 ADAMS      CLERK       7788 23-MAY-87    1100      20

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81     950      30
      7902 FORD       ANALYST       7566 03-DEC-81    3000      20
      7934 MILLER     CLERK       7782 23-JAN-82    1300      10

14 rows selected.

Elapsed: 00:00:00.00
14:24:25 SQL>


But then I can't if anywhere and when you look at the time it took, it probably barely registered in anything. How do I get this when I don't have the SQL_ID to able to set up the trace?

Many thanks,

D

Connor McDonald
May 20, 2016 - 2:47 pm UTC

You can find the SQL_IDs from v$sql (assuming it's still in the cursor cache):

select sql_id, sql_text from v$sql
where  sql_text like 'your query here';

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.