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

The 10046 event of Oracle can track the SQL statements executed by the application and get the number of parses. Information such as the number of execution, CPU usage time and so on. This is very useful for us to analyze and locate database performance problems.

10046 event is one of the most important events when oracle is used for system performance analysis. When this event is activated, real-time information about the trace session is notified to oracle kernel and written to the appropriate trace file. This useful information mainly includes how sql is parsed, the use of binding variables, wait events that occur in the session, and so on.

10046 event can be divided into different levels (level) to track and record different degrees of useful information. For these different levels, it should be noted that they are backward compatible, that is, trace information at a higher level contains all information below that level.

The tracking levels of 10046event are roughly as follows:

Level 1: tracks sql statements, including parsing, execution, extraction, commit, and rollback.

Level 4: includes details of variables

Level 8: including wait events

Level 12: including binding variables and wait events

Where level 1 is equivalent to opening sql_trace

Prerequisites:

(first make sure that the session environment to be event meets the criteria)

1. You must make sure that timed_statistics is TRUE, which can be modified at the session level. An explanation of the parameter timed_statistics

Without timed statistics, Oracle records the reason for each wait before it begins to wait, and when the wait is 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 waited. The time waited is recorded in hundredths of a second- that is, centiseconds.

True

The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.

False

The value of all time-related statistics is 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 be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.

My understanding is: if the parameter timed_statistics is set to true, Oracle records the wait reason of waiting for the event and the point in time to wait for the event to start and end, as well as whether the wait times out. After timed_statistics=false, Oracle only records information about the wait event and whether the wait event timed out.

When timed_statistics = false, we can only know the number of times the waiting time (records the reason for each wait) and the number of times the waiting time timed out (records whether it timed out)

When timed_statistics = true, we can know the time (records the time waited) that the system waits on a specific wait event, so we can determine exactly what is causing our system to slow down or track the operation of the database, with this time information can determine at which time period the wait event occurs more frequently. Time statistics are updated to the V$SESSTATS and V$SYSSTATS views. 11.1.0.7 if STATISTICS_LEVEL is set to typical or all, timed_statistics cannot be set to false

2. In order to ensure that the trace output can be completed, but also to adjust the limit on the size of the trace file in this session, generally remove this limit, that is, set the max_dump_file_size to UNLIMITED, or set it to a large threshold.

Once the above conditions are met, you can enable 10046 event to trace the session in the background.

Use 10046 events

1. First get spid, sid and serial#,machine as the names of the machines that connect to oracle.

Select b.spidrecovera. Sidmae a. Serialfields. Machine from v$session a recitals vandals process b where a.paddr = b.addr and a.sid = '159'

Keep looking down and you can see why you found SPID.

2. Opening and closing of trace

Use sql_trace

SQL > alter system set sql_trace=true;-- enable system-level sql_trace

SQL > alter session set sql_trace=true;-- enable session-level sql_trace

SQL > execute dbms_system.set_sql_trace_in_session (267, 996, and true)

The PL/SQL process completed successfully. -- SQL_TRACE that starts other sessions

SQL > alter system set sql_trace=false;-- turn off sql_trace at the system level

SQL > alter session set sql_trace=false;-- turn off session-level sql_trace

SQL > execute dbms_system.set_sql_trace_in_session (267, 996, and false)

The PL/SQL process completed successfully. -- close the SQL_TRACE of other sessions

Use 10046 event tracking

SQL > alter session set events' 10046 trace name context forever, level 12'; session has changed. -- the 10046 event that opened this session

SQL > exec dbms_monitor.session_trace_enable (267996Jet waits = > true,binds= > true)

The PL/SQL process completed successfully. -- 10046 events that open other sessions

SQL > alter session set events' 10046 trace name context off'

The session has changed. -- close the 10046 event of this session

SQL > exec dbms_monitor.session_trace_disable (267996)

The PL/SQL process completed successfully. -- 10046 events that close other sessions

Parameter description:

15:SID

196:SERIAL#

Note that you must log in as sysdba here.

3. Get the directory where the resulting trace file is located

SQL > select value from v$parameter where name = 'user_dump_dest'

Go to this directory and you can see that a zftang_ora_596.trc file is generated, where 596 is the value of SPID.

4. Use the tkprof command to convert TRC files

Execute the tkprof command of oracle from the command line to convert zftang_ora_596.trc to a text file

Such as:

$tkprof ora9i_ora_24722.trc ora9i_ora_24722.sql

At this point, in the zftang_ora_596.sql file, you can see the number of sql statements executed when the application was executed, CPU usage time, and so on.

TRACE consumes a considerable amount of system resources, so we should be careful when using TRACE. For formal systems, TRACE should be performed only when necessary and should be shut down 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 00

Execute 1 2.35 2.64 358 1865 220043 50782

Fetch 0 0.00 0.00 00 00

--

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

View the original trace file

Http://czmmiao.iteye.com/blog/1493933

About alter system event

Http://czmmiao.iteye.com/blog/1330430

Note: if a SQL statement contains data operations through DBLINK, we want to trace trace this SQL. We can only trace to locally executed SQL information locally, while for remote SQL statements, because it runs on a remote database, we need to go to the remote database, find the session running this SQL statement, and then Trace it. In addition, the execution plan for this SQL statement can only be captured from the remote database. This article is transferred from: http://czmmiao.iteye.com/blog/1497509

If this article infringes on the copyright of the author, please let me know and delete it!

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.