How to understand the collection of trace information

This article shows you how to understand the collection of trace information, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can gain something through the detailed introduction of this article.

Whenever we encounter performance related to SQL, we always need to collect 10046 to view and diagnose the problem.

Because 10046 of the true response of the SQL statement execution of the real information, parsing, execution, access to the time consumption, row source operation specific conditions.

Specific waiting events, specific time consumption of each time, and so on. I hope one of the following Case can help you.

EVENT: 10046 "enable SQL statement tracing (including binds/waits)" (Doc ID 21154.1)

Interpreting Raw SQL_TRACE output (Doc ID 39817.1)

General SQL_TRACE / 10046 trace Gathering Examples (Doc ID 1274511.1)

=

SQL performance is commonly used:

All versions

10046 on session/system

To start tracing:

Alter session/system (with caution) set events' 10046 trace name context forever, level 12'

/ * execute your selects to be traced * /

To stop tracing

Alter session/system (use with caution) set events' 10046 trace name context off'

More than 11g

1. Event++ specifies sql_id at the system level, which is valid for new and current sessions, but not for other existing sessions.

SQL > alter system set events' sql_trace [sql: 5qcyrymp65fak] level=12'

Note: the current event is valid for the current session and the newly created session, but not for other session that already exists.

Close event + +:

SQL > alter system set events' sql_trace [sql: 5qcyrymp65fak] off'

2. Event + + specifies the sql_id of a process

SQL > oradebug setospid