Shulou(Shulou.com)06/01 Report--

This article is about how to open 10046 Trace in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Various methods of opening 10046 Trace in Oracle

Tracking level of 10046 trace

10046 is an internal event (event) of Oracle. By setting this event, you can get detailed trace information such as system parsing, calling, waiting, binding variables and so on, which plays a very important role in analyzing the performance of the system.

Different levels of trace information can be obtained by setting different levels of the 10046 event. The corresponding functions of different levels are listed below:

Hierarchical binary action

0 0000 No output

1 0001 output *, APPNAME (application name), PARSING IN CURSOR,PARSE ERROR (SQL parsing), EXEC (execution), FETCH (get data), UNMAP,SORT UNMAP (sort, temporary period), ERROR,STAT (execution plan), XCTEND (transaction), etc.

2 0011 is exactly the same as level 1

4 0101 includes the output of level 1, plus BIND lines (bind variable information)

8 1001 includes the output of level 1, plus WAIT lines (waiting for event information)

12 1101 outputs all information about levels 1, 4, and 8

10046 trace of level 1 is considered a normal SQL Trace, while levels 4, 8, and 12 are called Extended SQL Trace,Extended SQL Trace, which contain the most useful WAIT information, so they are also used the most in practice.

Top parameters related to SQL Trace

Before opening the 10046-time SQL Trace, set the following parameters.

Timed_statistics

This parameter determines whether time-related statistics are collected. If this parameter is FALSE, then the result of SQL Trace is of little use. By default, this parameter is set to TRUE.

Max_dump_file_size

The size of the dump file, that is, deciding whether to limit the size of the SQL Trace file, SQL Trace on a busy system may generate a lot of information, so it is best to set this parameter to unlimited at the session level.

Tracefile_identifier

Set the identification string to the Trace file, which is a very useful parameter. Setting an easy-to-read string can find the Trace file faster.

To modify the above parameters in the current session, simply use the following command:

one

two

3 ALTER SESSION SET timed_statistics=true

ALTER SESSION SET max_dump_file_size=unlimited

ALTER SESSION SET tracefile_identifier='my_trace_session

Of course, these parameters can be modified at the system level, or they can be loaded into an init file or in spfile, allowing the system to make global settings automatically at startup.

If you dynamically modify these parameters of other sessions while the system is running, you need to use the package DBMS_SYSTEM, which is set as follows:

one

two

three

four

five

six

seven

eight

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 function for set_string_param_in_session in the dbms_system package, so tracefile_identifier cannot be modified in other sessions (at least I haven't found a way to set it up so far).

Top10046 Trace startup method

Top opens 10046 Trace of the current session

Top uses the sql_trace parameter

Sql_trace should be a simple and quick way to open Trace, but only Trace with level 1 can be enabled through sql_trace, but not other more advanced Trace.

one

two

three

four

5-enable Trace

ALTER SESSION SET sql_trace=true

-- close Trace

ALTER SESSION SET sql_trace=false

Top uses set event to open Trace

Using set event to open the 10046 event Trace is the most common.

one

two

three

four

5-the number after Trace,level with level 12 on sets the level of Trace

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'

-- close Trace, at any level

ALTER SESSION SET EVENTS '10046 trace name context off'

10046 Trace for top to open other sessions

Top uses login triggers to open Trace

We can open 10046 Trace by writing a login trigger. The code to open Trace in this way is the same as the one that opens the current session, except that the opening code is contained in an after logon trigger.

one

two

three

four

five

six

seven

eight

nine

10-the code comes 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 trace name context forever, level 8''

END IF

END

/

Top uses the oradebug tool

When using the oradebug tool, you must know that the OS process PID,OS PID of the process you are dealing with can be obtained using the following statement:

one

two

three

four

five

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. Note that you need to log in to the database using sysdba:

one

two

three

four

five

six

seven

8-suppose 9999 is the OS PID of the session

Oradebug setospid 9999

-- set the Trace file size

Oradebug unlimit

-- enable Trace with level 12

Oradebug event 10046 trace name context forever, level 12

-- close trace

Oradebug event 10046 trace name context off

Top uses DBMS_SYSTEM packages

The DBMS_SYSTEM package provides two ways to turn on 10046 Trace, one is to use the SET_SQL_TRACE_IN_SESSION procedure, but the effect of using this procedure is the same as sql_trace:

one

two

three

four

5-enable 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 way is to use the SET_EV procedure, which, of course, is used not only to set the 10046 event, but also to set all other events by:

one

two

three

four

five

six

seven

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

Examples of use:

one

two

three

four

5-enable Trace of level 12

EXEC SYS.DBMS_SYSTEM.SET_EV (: sid,: serial, 10046, 12,'')

-- close Trace

EXEC SYS.DBMS_SYSTEM.SET_EV (: sid,: serial, 10046, 0,'')

Top uses DBMS_SUPPORT packages

The DBMS_SUPPORT package is not included in the database by default and needs to be used by running the $ORACLE_HOME/rdbms/admin/dbmssupp.sql installation.

You can use the DBMS_SUPPORT package to start the Trace of your own process or another process.

Start your own process:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

16-method of use

DESC DBMS_SUPPORT

PROCEDURE START_TRACE

Argument Name Type In/Out Default?

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PROCEDURE STOP_TRACE

-- examples

-- enable Trace with level 12

EXEC SYS.DBMS_SUPPORT.START_TRACE (true, true)

-- close Trace

EXEC SYS.DBMS_SUPPORT.STOP_TRACE ()

Start the Trace of other processes:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

20-method of use

PROCEDURE START_TRACE_IN_SESSION

Argument Name Type In/Out Default?

SID NUMBER IN

SERIAL NUMBER IN

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PROCEDURE STOP_TRACE_IN_SESSION

Argument Name Type In/Out Default?

SID NUMBER IN

SERIAL NUMBER IN

-- examples

-- enable Trace with level 12

EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION (: sid,: serial, true, true)

-- close Trace

EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION (: sid,: serial)

Top uses DBMS_MONITOR packages

The emergence of the DBMS_MONITOR package in Oracle 10g has changed the history of opening only one session at a time, and ushered in a new era of batch enabling of 10046 Trace.

Track a single session

First, take a look at the Trace method similar to the previous one for opening a single session, which is almost no different from the DBMS_ support package:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

25-process definition

PROCEDURE SESSION_TRACE_DISABLE

Argument Name Type In/Out Default?

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

PROCEDURE SESSION_TRACE_ENABLE

Argument Name Type In/Out Default?

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

-- examples

-- Open Trace with level 12, the current session

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE (WAITS= > true,BINDS= > true)

-- Open Trace with level 12, and other sessions

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE (: sid,: serial, true, true)

-- close Trace, current session

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE ()

-- close Trace, other sessions

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE (: sid,: serial)

Tracking according to Client Identifier

Using the DBMS_MONITOR package may be the same as Trace multiple different sessions according to client identifier. Client identifier can be seen in the client_identifier field in V$SESSION, as follows:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

16-find out the client_identifier information for Trace

SQL > SELECT sid, program, client_identifier FROM V$SESSION

SID PROGRAM CLIENT_IDENTIFIER

-

71 sqlplus@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com

72 rman@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com

75 rman@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com

Suppose you want all sessions for which Trace client_identifier is "oracle@orainst.desktop.mycompany.com"

-- Open a Trace for level 12 using the following statement

SQL > EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ('oracle@orainst.desktop.mycompany.com', true, true)

PL/SQL procedure successfully completed.

-- stop using the following statement

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ('oracle@orainst.desktop.mycompany.com')

Tracking according to Service, Module and Action

The SERV_MOD_ACT_TRACE_ENABLE procedure of the DBMS_MONITOR package is used to open the Trace of multiple sessions based on the three attributes of Service, Module and Action.

Before Trace, make sure that your application sets these three corresponding properties. Oracle provides the package DBMS_APPLICATION_INFO to set module, action and other information, as follows:

one

two

3 EXEC dbms_application_info.SET_MODULE ('Trace Test',' No Trace')

EXEC DBMS_APPLICATION_INFO.SET_ACTION (''No Trace')

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('A Haaaa')

After the setup is complete, we can see these messages in V$SESSION:

one

two

three

four

five

six

seven

eight

nine

ten

11 SQL > COL ACTION FOR A10

SQL > COL MODULE FOR A45

SQL > COL SERVICE_NAME FOR A12

SQL > COL SID FOR 999

SQL > SELECT SID, service_name, module, action FROM v$session WHERE TYPE'BACKGROUND'

SID SERVICE_NAME MODULE ACTION

-

67 SYS$USERS SQL Developer

71 SYS$USERS sqlplus@orainst.desktop.mycompany.com (TNS V1-V3)

72 SYS$USERS Trace Test No Trace

Know the appropriate information and use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable Trace for the appropriate session:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

17-enable Trace for all sessions for which service name is "SYS$USERS"

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (service_name = > 'SYS$USERS'

Waits = > TRUE, binds = > FALSE)

-- stop the Trace that is opened

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (service_name = > 'SYS$USERS')

-- enable Trace for a specific action

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (service_name= > 'SYS$USERS'

Module_name= > 'Trace Test'

Action_name = > 'Trace'

Waits = > TRUE, binds = > FALSE)

-- stop the Trace that is opened

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (service_name= > 'SYS$USERS'

Module_name= > 'Trace Test'

Action_name = > 'Trace')

Open Trace at the database level

DBMS_MONITOR can also open Trace at the database level.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

18-definition

PROCEDURE DATABASE_TRACE_DISABLE

Argument Name Type In/Out Default?

INSTANCE_NAME VARCHAR2 IN DEFAULT

PROCEDURE DATABASE_TRACE_ENABLE

Argument Name Type In/Out Default?

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

INSTANCE_NAME VARCHAR2 IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

-- Open the Trace of level 12 at the database level

EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE (true, true)

-- stop Trace at the database level

EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE ()

This function affects the entire database, very powerful, use with caution!

Attention points for top to open multiple sessions of Trace

Trace that uses DBMS_MONITOR to open multiple sessions is dynamic. For example, when you want to track the Trace of a particular action, you do not need to make sure that the session corresponding to that particular action is running before you can open the corresponding Trace. On the contrary, once the action of a process meets the conditions of the currently opened Trace, that session will start to output Trace information. When the action of the session changes. The output of the Trace information will also stop, so here is a simple test.

Start with a process for Trace whose module is "Trace Test" and action is "Trace":

one

two

three

four

five

6 SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (service_name= > 'SYS$USERS'

Module_name= > 'Trace Test'

Action_name = > 'Trace'

Waits = > TRUE, binds = > FALSE)

PL/SQL procedure successfully completed.

Then execute the following sequence of statements in another process:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

23-set the module and action of the current session

SQL > EXEC DBMS_APPLICATION_INFO.SET_MODULE ('Trace Test',' Trace')

PL/SQL procedure successfully completed.

-- execute a simple query

SQL > select 'trace' from dual

TRACE

-

Trace

-- change the action of the current session

SQL > EXEC DBMS_APPLICATION_INFO.SET_MODULE ('Trace Test',' No Trace')

PL/SQL procedure successfully completed.

-- execute another query

SQL > select'no trace' from dual

NOTRACE

-

No trace

When you open the Trace file after execution, you will find that the statements for the first execution of module and action settings and the second execution of "select'no trace' from dual;" do not appear in the Trace file. The contents of the Trace file are as follows:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

45 Unix process pid: 8900, image: oracle@orainst.desktop.mycompany.com (TNS V1-V3)

* * 2010-04-13 06 Fran 55 24. 247

* SESSION ID: (72.604) 2010-04-13 0615 5524.247

* CLIENT ID: (oracle@orainst.desktop.mycompany.com) 2010-04-13 0615 5524.247

* SERVICE NAME: (SYS$USERS) 2010-04-13 0615 5524.247

* MODULE NAME: (Trace Test) 2010-04-13 0615 5524.247

* ACTION NAME: (Trace) 2010-04-13 0615 5524.247

=

PARSING IN CURSOR # 1 len=69 dep=0 uid=0 ct=47 lid=0 tim=1271141724247208 hv=297401484 ad='9eb182c8' sqlid='1ckkjdn8vmz4c'

BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('Trace Test',' Trace'); END

END OF STMT

EXEC # 1 Vera cantilever 0Magol eL13 pendant 0MIQULIZOMIZHY 1MIQUOLING 1MIQULTHY 0MIHULING 0MIQUOTHY 0MIHULTHY 0TIMERES 1271141724247201

WAIT # 1: nam='SQL*Net message to client' ela= 6 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1271141724247735

* * 2010-04-13 06 Fraser 5515 32. 913

WAIT # 1: nam='SQL*Net message from client' ela= 8666025 driver id=1650815232 # bytes=1 p3Secret0 obj#=-1 tim=1271141732913790

CLOSE # 1 Vera cymbals 0 mentions eBay 43 coverages 0 coverages 0 coverages timelines 1271141732913937

=

PARSING IN CURSOR # 2 len=25 dep=0 uid=0 ct=3 lid=0 tim=1271141732932795 hv=660028772 ad='9eb12b10' sqlid='70t5xg4mpfgb4'

Select 'trace' from dual

END OF STMT

PARSE # 2 Vera cantilever 1000 repertory 18805 pamphlet crackers 0rem cuttle0reagle mismatch 1phae0phae0pyrrhen0phae0phae0pyrrhen0phae0pyrrhen0pyrrhen0phae0phae0pyrrhenode0pyrrhenode0pyrrhenode0wereoglle PARSE # 2VOBG 1388734953 ~ timed 1271141732932792

EXEC # 2 Vulcan Centro Magistral eLTHEROGRAPHY 47MICOLING 0MIQULIZOMIZOMIZOMIANG 0MIXOMIANG 0MIXINOMIANG 0MIXINOMIANG 0MIXING 0MIANG 0MIAND 0MIAND 0MIAND 0MIREDIZOWELEOOMIZOOMIAND OGTHY

WAIT # 2: nam='SQL*Net message to client' ela= 4 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1271141732932989

FETCH # 2 Vulcan Centro Magistracy 17 Percience pendant 0 Magistracy crushing 0 recollection Cuttle0 Magi Mishi 0 Magi ronomy 1 depravity Ogosphere 1 cognac 1388734953 Timeline 1271141732933035

STAT # 2 id=1 cnt=1 pid=0 pos=1 bj=0 p='FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'

WAIT # 2: nam='SQL*Net message from client' ela= 151driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1271141732933287

FETCH # 2 Vulcan Centro Magistracy 3Phen0Phen0Magicol Cuppet 0MIQIZOQIAN0MIQIAN0MIQIAN0MIZHY 0MIAND 0MIAND 0MIAND 0MUBLING 0MICROLING 0MICROLING 0MICROLING 0MICRON0MICROLING 0OGHING 0MICHING 0MICHANOMETHORE plhages 1388734953

WAIT # 2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1271141732933364

* * 2010-04-13 06 Fran 55! 48.413

WAIT # 2: nam='SQL*Net message from client' ela= 15480453 driver id=1650815232 # bytes=1 p3400 obj#=-1 tim=1271141748413833

CLOSE # 2 Vera cymbals 0 mentions eBay 26 coverages 0 coverages 0 coverages timelines 1271141748413963

=

PARSING IN CURSOR # 1 len=72 dep=0 uid=0 ct=47 lid=0 tim=1271141748415935 hv=2176830839 ad='9eb0ec80' sqlid='ar765n60vzmbr'

BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('Trace Test',' No Trace'); END

END OF STMT

PARSE # 1 Vulcan 1999 Ensemble 1919 pamphlet 0memoir cuttle0reagle misunderstanding 1pyrrhagogic gregarious glossary 0memoria1271141748415932

* MODULE NAME: (Trace Test) 2010-04-13 0615 55purl 48.416

* ACTION NAME: (No Trace) 2010-04-13 0615 55purl 48.416

EXEC # 1 Vera Centro Magazine eBay 93, pendant 0, crackers 0, cube 0, Mishi 0, ritual, depravity, ogee, plhong, 0, timeline, 1271141748416134

Thank you for reading! This is the end of the article on "how to open 10046 Trace in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.