First, the introduction of Sql_trace
--Open trace file settings and set SQL Trace to True to add a TRC file to the Udump directory.
Alter session set Sql_trace=true;
Show parameter Sql_trace; (SELECT * from V$parameter where name= ' sql_trace ';)
Does it not take effect after modification? Show parameter sql_trace; its parameters are always false
Show parameter sql_trace is to take the corresponding parameters from V$parameter, and V$parameter is the embodiment of the whole init.ora content. The show user should be the parameter of the current session taken.
Sql_trace can be enabled at the system or session level, and produce a lot of output, the system level should be changed directly to Init, session level with ALTER session set Sql_trace = True to take effect. Isses_modifiable=false should be a description at the session level, you cannot enable tracing at the system level
If you modify the parameters of the system, use the following statement:
alter system set sql_trace=true;
At this point the show parameter sql_trace is viewed; the value of sql_trace can be seen as true
--Mark the trace file
Alter session set tracefile_identifier= ' Look_for_me '; --
Trace files marked as Look_for_me can be found under the D:\oracle\product\10.2.0\admin\fgisdb\udump path
--Stop tracing for this session
Alter session set Sql_trace=false;
Tkprof Tool Analysis trace file:
1. CD D:\app\Rayda001\diag\rdbms\orcl\orcl\trace
2, trace>tkprof orcl_j000_10836.trc localhost_recorder3.txt sort=fchela sys=no aggregate=tes waits=yes;
Tkprof is an executable tool for analyzing Oracle trace files and producing a clearer and more reasonable output. If the execution of a system is relatively inefficient, a better approach is to find the problematic SQL statement by tracking the user's session and using the Tkprof tool to format the output using the sort function.
Links to Tkprof related parameters
Analyzing ORACLE trace files using Tkprof
Oracle SQL Tracking and TKPROF tools
Oracle Execution Tracking