Oracle statement execution tracking

Oracle statement execution tracking

An interface on the system often times out to submit data (more than 3 seconds), while I only need 17 ms to execute insert in the background database (Oracle. The client that submits data does not have any debugging logs. It can only trace the background statements to record the database execution time during the actual call. Thus, the most time-consuming stage is discovered.

Install the dbms_support package

SQL> @?\rdbms\admin\dbmssupp.sqlSQL> grant execute on dbms_support to test;

Find the session to be tracked. An interface may create multiple sessions. You need to determine the sessions to be tracked based on the session creation time.

SQL> alter session set nls_date_format = 'yyyy-MM-DD HH24: MI: ss'; SQL> select sid, serial #, logon_time from v $ session where username = & name and program = & program; input name value: 'test' Input program value: 'test.exe'

Use start_trace_in_session for tracking

SQL>exec dbms_support.start_trace_in_session(1157,59729,TRUE,TRUE);

After tracing starts, perform corresponding operations on the client. Use stop_trace_in_session to end the trail

SQL> exec dbms_support.stop_trace_in_session(1157,59729);

Use the following statement to obtain the location of the trace file.

     SELECT    d.VALUE           || '\'           || LOWER (RTRIM (i.INSTANCE, CHR (0)))           || '_ora_'           || p.spid           || '.trc' trace_file_name      FROM (SELECT p.spid              FROM v$mystat m, v$session s, v$process p             WHERE m.statistic# = 1 AND s.SID = &SID AND p.addr = s.paddr) p,           (SELECT t.INSTANCE              FROM v$thread t, v$parameter v             WHERE v.NAME = 'thread'               AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,           (SELECT VALUE              FROM v$parameter             WHERE NAME = 'user_dump_dest') d 

You can use a text editor to directly open the trace file.

This section in the figure is the entire process from parsing a statement to returning data. If you count the time from the start request to the time when the message is returned to the client, directly subtract the two tim in the red box,11592216806504-11592199796058=17010446Unit: 1/1000000 seconds. To obtain the execution time separately, you can subtract the part TIM from the part TIM of EXEC. The difference is the execution time. For descriptions of other fields, refer to this document.

You can also use TKPROFConverts a trace file to the execution plan parsing text.

D: \ app \ oracle \ diag \ rdbms \ wxmesdb \ trace> tkprof wxmesdb_ora_651_trc trace_1.txtTKPROF: Release 12.1.0.1.0-Development on Wednesday January 6 08:30:11 2016 Copyright, oracle and/or its affiliates. all rights reserved.

As shown in, the execution time is 17 seconds, which is consistent with the difference between TIM subtraction in the previous step.

-- EOF --

For more details, please continue to read the highlights on the next page:

1 2 Next Page

[Content navigation]
Page 1: SQL trace for statement tracking	Page 2nd: Use event 10046 to track statements