ORACLE 10046 Trace

trace level of 10046 trace

10046 is an Oracle internal event that enables detailed trace information, such as Oracle internal execution system parsing, invocation, waiting, binding variables, and so on, which is important for analyzing the performance of the system.

By setting the different levels of the 10046 event to get trace information of varying levels of detail, the following lists the corresponding roles for each level:

Binary

Grade	System	function
0	0000	No output
1	0001	Output ****,appname (application name), parsing in cursor,parse error (SQL parsing), EXEC (execute), FETCH (fetch data), Unmap,sort unmap (sort, temporary segment), ERROR, STAT (Execution Plan), Xctend (transaction), etc.
2	0011	Exactly the same as Level 1.
4	0101	Includes level 1 output, plus bind line (binding variable information)
8	1001	Includes the output of level 1 plus the wait line (waiting for event information)
12	1101	All information for output level 1, Level 4, and Level 8

Level 1 of 10046 trace is considered normal SQL Trace, while levels 4, level 8, and level 12 are referred to as Extended SQL Trace,extended SQL Trace includes the most useful wait information, and therefore is used most in practice.

parameters related to SQL Trace

Before you open SQL Trace for 10046 times, set the following parameters first. timed_statistics This parameter determines whether time-related statistics are collected, and if this argument is false, then the result of SQL Trace is largely less useful, and by default this parameter is set to True. The size of the max_dump_file_size dump file, which is the decision to limit the size of the SQL trace file, can generate a lot of information when you do SQL Trace on a busy system. It is therefore best to set this parameter to unlimited at the session level. Tracefile_identifier Sets the identification string for the trace file, which is a very useful parameter that sets an easy-read string to find the trace file faster.

To modify the above parameter in the current session is simple, just use the following command:

1 2 3

Alter session SET timed_statistics= true ALTER session set max_dump_file_size=unlimited ALTER session set Tracefile_identi Fier= ' My_trace_session

Of course, these parameters can be modified at the system level, either in the init file or in SPFile, allowing the system to automatically make global settings when it starts.

If you change the parameters of other sessions dynamically during the system run, you need to use the Dbms_system package, and set the method as follows:

1 2 3 4 5 6 7 8 9

SYS. Dbms_system. Set_bool_param_in_session (: SID,: Serial, ' Timed_statistics ', true) SYS. Dbms_system. Set_int_param_in_session (: SID,: Serial, ' max_dump_file_size ', 2147483647)

Note that Oracle does not provide a set_string_param_in_session function in the Dbms_system package, so Tracefile_ Identifier cannot be modified in another session (at least I haven't found a way to set it up). 10046 Trace Startup method opens the 10046 Trace for the current session using the Sql_trace parameter

Sql_trace should be a quick and easy way to turn on trace, but you can only turn on trace at level 1 through Sql_trace, and you won't be able to open other, more advanced trace.

1 2 3 4 5

--Open Trace ALTER session SET sql_trace= true; --Close Trace ALTER session SET sql_trace= false;

use Set event to open trace

Using Set event to open 10046 Event trace is most commonly used.

1 2 3 4 5

--the number at the end of the trace,level level of 12 sets the trace level ALTER session set EVENTS ' 10046 Trace name Context forever, Levels 12 '--close trace, Any level ALTER session SET EVENTS ' 10046 trace name context off '

Open 10046 Trace for other sessions using the login trigger to turn on trace

We can turn on 10046 trace by writing a login trigger, and using this method to open trace's code is the same as opening the current session, but the opening code is contained within an after logon trigger.

1 2 3 4 5 6 7 8 9 10

--code from "Optimazing Oracle performance" P116 CREATE OR REPLACE TRIGGER trace_test_user after LOGON on DATABASE BEGIN IF      USER like '%\_test ' ESCAPE ' THEN EXECUTE IMMEDIATE ' ALTER session SET timed_statistics= true ';      EXECUTE IMMEDIATE ' ALTER session SET max_dump_file_size=unlimited ';    EXECUTE IMMEDIATE ' ALTER session SET EVENTS ' 10046 the trace name context forever, Level 8 '; End IF; End; /

using the Oradebug tool

Using the Oradebug tool you must know the OS process Pid,os pid of the process you want to process can be obtained using the following statement:

1 2 3 4 5 6

SELECT S.username, P.spid os_process_id, P.pid oracle_process_id from V$session S, v$process p WHERE s.paddr = P . ADDR and S.username = UPPER (' & User_name ');

After you get the PID, you can use the Oradebug tool, and note that you need to log in to the database using SYSDBA:

1 2 3 4 5 6 7 8

--Suppose 9999 is the session's OS PID oradebug setospid 9999; --Set trace file size Oradebug unlimit; --Turn on the trace Oradebug event 10046 trace name context forever with level 12; --Close Trace Oradebug event 10046 trace name context off;

using the Dbms_system package

The Dbms_system package provides two ways to turn on 10046 TRACE, one using the set_sql_trace_in_session process, but the effect of using this process is the same as that of Sql_trace:

1 2 3 4 5

--Opens the trace EXEC SYS. Dbms_system.   Set_sql_trace_in_session (: SID,: serial#, True); --Close trace EXEC SYS. Dbms_system. Set_sql_trace_in_session (: SID,: serial#, false);

Another approach is to use the Set_ev process, which, of course, is not just for setting up 10046 events, but for setting up all other events, using the following method:

1 2 3 4 5 6 7 8

PROCEDURE Set_ev   Argument name                   type                     in/out Default?  -------------------------------------------------------------------  si                               binary_integer          in   se                              binary_integer           in   ev                              binary_integer          in   le                              binary_integer           in   nm                              varchar2                 in

Use examples:

1 2 3 4 5

--Opens trace EXEC SYS of level 12. Dbms_system.   Set_ev (: SID,: Serial, 10046, 12, '); --Close trace EXEC SYS. Dbms_system. Set_ev (: SID,: Serial, 10046, 0, ');

using the Dbms_support package

Dbms_support packages are not included in the database by default and need to be installed after you run $oracle_home/rdbms/admin/dbmssupp.sql installation.

You can dbms_support a package to open its own process or trace of another process.

To open its own process:

1 2 3 4 5 6 7 8 9 10 11 12 13 14-15 16

--use method DESC dbms_support   PROCEDURE start_trace   Argument name       & nbsp;          type                     in/out Default?  -------------------------------------------------------------------  waits                            boolean                  in     DEFAULT   binds                           boolean                  in      DEFAULT PRocedure stop_trace  --instance--opens TRACE EXEC SYS with level 12. Dbms_support. Start_trace (True, true);  --Turn off TR