Oracle 10046 Event Details-Reprint

0046 Event Overview
Oracle's 10046 event tracks the SQL statements executed by the application and gets its number of resolutions. Number of executions, CPU usage, and so on. This is very useful for us to analyze and locate database performance issues.

The 10046 event is one of the most important events Oracle uses for system performance analysis. When this event is activated, the relevant instant information for the Oracle Kernel tracing session is notified and written to the corresponding trace file. These useful information mainly include how SQL is parsed, the usage of bound variables, the waiting events that occur in the session, etc.

The 10046 event can be divided into levels to track useful information that records varying degrees. For these different levels, it should be noted that backward compatibility, that is, the higher-level trace information contains all information below this level.

The 10046event tracking level is roughly:

Level 1: Trace SQL statements, including parsing, execution, extraction, commit, and rollback.

Level 4: Include variable details

Level 8: Including wait events

Level 12: Includes bound variables and wait events

Where level 1 is equivalent to opening the Sql_trace

Prerequisites:
(Ensure that the session environment for the event meets the criteria first)

1, you must ensure that Timed_statistics is true, this parameter can be modified at the session level. An explanation about the parameter timed_statistics
Without timed statistics, Oracle records the reason for each wait before it begins to wait, and when the wait was over, it Records whether it timed out. But with timed statistics enabled, Oracle checks the time just before and after each wait, and also records the time Waite D. The time waited is recorded in hundredths of a second-that are, centiseconds.
True
The statistics is collected and stored in trace files or displayed in the V$sesstats and V$sysstats dynamic performance V Iews.

False

The value of all time-related statistics are set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.

Starting with release 11.1.0.7.0, the value of the Timed_statistics parameter cannot bes set to False if the value of Stati Stics_level is set to typical or all.

My understanding is: The parameter timed_statistics is set to true. Oracle records The wait reason and the time at which the wait event starts and ends, and whether the wait timed out. After Timed_statistics=false, Oracle logs only the information that waits for the event and waits for the event to time out.
At timed_statistics = False, we are only able to know the number of wait times (records the reason for each wait) and the number of wait times timed out (records whether it Tim Ed out)
At timed_statistics = True, we can know when the system waits on a specific wait event (records the time waited) so that we can determine what is causing our system to slow down or track the operation of the database. With these time information, you can determine which time period the wait event occurs more frequently. The time statistics are updated to the V$sesstats and v$sysstats views. If Statistics_level is set to typical or all after 11.1.0.7, the timed_statistics cannot be set to False

2, in order to ensure that the trace output can be complete, but also to adjust the trace file size limit for this session, the limit is generally canceled, the max_dump_file_size is set to unlimited, or set to a large threshold.

Once the above conditions are met, the 10046 event can be enabled for background tracking of the session.
Using the 10046 Event

1. First get spid, sid, Serial#,machine as the name of the machine connected to Oracle
Select B.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr and a.sid = ' 159 '

Keep looking down and you'll know why you found the SPID;

2. Trace opening and closing
Using Sql_trace
Sql> alter system set sql_trace=true; --Turn on system level Sql_trace
Sql> alter session set Sql_trace=true; --Turn on session level Sql_trace
Sql> Execute dbms_system.set_sql_trace_in_session (267,996,true);
The PL/SQL process has completed successfully. --Sql_trace to start other sessions
Sql> alter system set SQL_TRACE=FALSE; --Shut down the system-level Sql_trace
Sql> alter session set Sql_trace=false; --close session-level Sql_trace

Sql> Execute dbms_system.set_sql_trace_in_session (267,996,false);
The PL/SQL process has completed successfully. --Close the sql_trace of other sessions

Using 10046 event Tracking

Sql> alter session SET events ' 10046 Trace name Context forever, Level 12 '; The session has changed. --10046 events to open this session

sql> exec dbms_monitor.session_trace_enable (267,996,waits=>true,binds=>true);
The PL/SQL process has completed successfully. --open 10046 events for other sessions

Sql> alter session SET events ' 10046 Trace name context off ';

The session has changed. --closing 10046 events for this session

sql> exec dbms_monitor.session_trace_disable (267,996);
The PL/SQL process has completed successfully. --Closes 10046 events for other sessions

Parameter description:
15:sid
196:serial#

Note that this must be logged in SYSDBA.

3. Get the directory where the trace files are generated
Sql> Select value from v$parameter where name = ' User_dump_dest ';

Go to this directory to see that a ZFTANG_ORA_596.TRC file was generated, where 596 is the value of the SPID.

4. Convert TRC file using tkprof command
Execute Oracle's tkprof command at the command line to convert ZFTANG_ORA_596.TRC to a text file

Such as:
$ tkprof ora9i_ora_24722.trc Ora9i_ora_24722.sql

In the Zftang_ora_596.sql file, you can see data such as the number of executions, CPU usage, and so on, of the SQL statement executed just before the application was executed.

Trace will consume considerable system resources, so we should be cautious when using trace. For a formal system, trace operations should be performed only when necessary, and should be closed in a timely manner.

********************************************************************************

Count = number of times OCI procedure was executed

CPU = CPU time in seconds executing

elapsed = elapsed time in seconds executing

Disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

Current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

Update test_01 Set Flag1 = ' Test '

Call count CPU Elapsed disk query current rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse 1 0.00 0.01 0 2 0 0

Execute 1 2.35 2.64 358 1865 220043 50782

Fetch 0 0.00 0.00 0 0 0 0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Total 2 2.35 2.65 358 1867 220043 50782

Misses in library cache during parse:1

Optimizer mode:all_rows

Parsing user id:63

How to use Tkprof
http://czmmiao.iteye.com/blog/1493765
Viewing the original trace file
http://czmmiao.iteye.com/blog/1493933
About the ALTER system event
http://czmmiao.iteye.com/blog/1330430
NOTE: If a SQL statement contains data operations through Dblink, we would like to trace the SQL to the local only trace to the local execution of the SQL information, and for the remote SQL statement, because it runs on the remote database, we want to obtain its information, Need to go to the remote database, find the session running the SQL statement, and then do a trace of it. In addition, the execution plan for this SQL statement can only be captured from the remote database.

Refer to: "Let Oracle run Faster" Tan Huaiyuan
Http://www.linuxidc.com/Linux/2011-04/35132p2.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams255.htm#REFRN10218
This article original, reproduced please indicate the source, the author

http://czmmiao.iteye.com/blog/1497509

Oracle 10046 Event Details-Reprint