In Oracle 11g, I want to monitor the response time of a specific sql (I know the sql_id of the sql). Meaning, I want to know the response time of the sql every time it is executed. I would "turn it on" for some time and then would like to see a data something like:
For sql_id 'abcdefg', following were the execution time (in ms)
10
12
10
13
10
10
10
240
230
10
9
12
…
…
We can ignore the exact format I would like to see the output, but is there any way for me to monitor such that I can get the response time for every execution for my sql?
Thanks.
Firstly, you should trace your sql (assume sql_id:6b0z40gs9m759):
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:6b0z40gs9m759]';
[ SQL> ALTER SYSTEM SET EVENTS 'sql_trace [sql:&&sql_id] bind=true, wait=true'; ] -- or use this as alternative.
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "TrcSqlParag"; -- adds this text to your trace file and makes it more recognizable by you.
SQL> SHOW PARAMETER user_dump_dest -- trace files are produced in this directory
-- For 11g DB, you may easily query v$diag_info view to see your file destination :
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
-- You can identify the trace file for a specific session using the V$SESSION and V$PROCESS views :
SQL> SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = &ses_id;
-- You may finish tracing whenever you want :
SQL> ALTER SESSION SET sql_trace=FALSE;
Let's have such a dump file :
/u01/app/oracle/admin/MYDB11G/udump/mydb11g_ora_TrcSqlParag.trc
The trace file is not easy to read, we need tkprof utility to read it from OS :
$ cd /u01/app/oracle/admin/MYDB11G/udump/
$ tkprof mydb11g_ora_TrcSqlParag.trc TrcSqlParag_Translated1.txt explain=<username>/<pwd>@mydb11g table=sys.plan_table sys=no waits=yes