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

Description

Encountered SQL statement query error. Yong Huang version prompted to do 10046 event. There has been a vague concept of this event before. Want to sort it out, so write this document. For future use.

Environment RHEL5.4 64 + 10.2.0.4

Explain what 10046 event can do. It is known that this is a system performance analysis event, which can tell the oracle kernel to output the detailed time information of the corresponding session to the trace file.

10046 event level (no translation, the level is limited, I still think the original text is good after a long time)

You can think of the event 10046 "level" attribute associated with an Oracle session as a 4-bit flag whose bits have the following meanings:

Level

FunctionDecimalBinary10001Emit statistics for parse, execute, fetch, commit, and rollback database calls (standard sql_trace) 20010Unknown30100Emit values for SQL bind variables (also called "placeholders") 41000Emit statistics for Oracle kernel internal function calls (also called "wait events") listed in v$event_name

For example, a level-12 trace combines the effects of level-4 and level-8 tracing. Strangely, activating any non-zero tracing level also activates level-1 tracing. Therefore, tracing at levels 4, 8, and 12 are exactly equivalent to tracing at levels 5, 9, and 13, respectively: all these levels include the standard sql_trace output.

Trace file location 11gR1 or 11gR1 above

SQL > show parameter diagnostic_dest

Previous versions of 11gR1

SQL > show parameter user_dump_dest

Naming rules for trace files

Tracefile naming convention: _ ora__.trc

Where pid is the corresponding OS PID,tracedid of session is related to the TRACEFILE_IDENTIFIER parameter of session, and the default TRACEFILE_IDENTIFIER is null.

Eg.

Set TRACEFILE_IDENTIFIER for the current session

ALTER SESSION SET TRACEFILE_IDENTIFIER='TOMS'

The "TOMS" here is the tracedid of _ ora__.trc in the trace file naming convention.

You can query V$PROCESS.TRACEID in the session that sets the TRACEFILE_IDENTIFIER parameter to see the settings of tracefile_identifier.

Cancel the session logo and leave the tracefile_identifier empty.

Alter session set tracefile_identifier=''

Locate the trace file (10g version test passed) 1. The trace file of your current session (you need permission to query v$mystat, v$session and v$process) Normal 0 7.8 lb 0 2 false false false EN-US ZH-CN X-NONE

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.