Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the 10046 event in oracle. It is very detailed and has a certain reference value. Interested friends must finish reading it!

Definition of 10046

The sql_trace/10046 event is a means provided by oracle for sql tracking, which includes the parsing process of sql, the execution plan of sql, the use of binding variables, and wait events that occur in the session.

10046 generate statement method:

SQL > alter session set events' 10046 trace name context forever,level 12'

Now inquire about anything, such as:

Select * from dba_users where rownum select * from v$diag_info

There's a Default Trace File in it.

1 Default Trace File

/ u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_28613.trc

At 10g, show parameter dump; is looking for udump.

Level 10046

Objective: to obtain the execution analysis of sql, pl_sql and other related statements.

10046 level: a total of 4 levels are 0, 1, 4, 8, 12

The 10046 incident is an extension of SQL_TRACE and is jokingly called "SQL_TRACE on stimulants".

Valid tracking level:

① level 0: SQL_TRACE=FASLE

② level 1: SQL_TRACE=TRUE, which is the default level

③ level 4: level 1 + bind variables

④ level 8: level 4 + wait event

⑤ level 12: level 4 + 8

Tkprof:oracle built-in tool for formatting trace files

Alter session set events' 10046 trace name context forever, level 12 tracks;-- tracing enabled for the current session

Alter session set events' 10046 trace name context off';-turn off current session tracking

Select sid,serial#,username from v$session where username is not null;-query sid and serial

Execute dbms_system.set_ev (sid,serial#,10046,12,'');-- track other sessions in the current session

Execute dbms_system.set_ev (sid,serial#,10046,0,'');-- turn off other session tracking in the current session

Common parameters of tkprof

Tkprof enter to check the help to know how to use tkprof parsing source tracking files to parse the completed files.

Filename input tracking file generated by SQL trace

Explain plain of the explain SQL statement

Recoed creates a SQL script for non-recursive SQL statements

Waits records a summary of wait events

SORT provides classified data based on one or more items, such as PRSCPU (CPU time Analysis), PRSELA (time spent Analysis), etc.

Table defines the name of the table into which the TKPROF utility temporarily places the execution plan

Sys enables or disables a set of SQL statements given by sys

PRINT lists only the specified number of SQL statements, not all SQL statements

Insert creates a script to store tracking database information

Two parameters related to 10046:

Show parameter max_dump_file_size

Size restrictions on trace files

Show parameter timed_statistics

Whether the collection of important information is turned on

What do you think of 10046?

The tkprof file contains the following:

Sql statement

Analyze the number of times to perform the get call

Number of rows processed

Number of seconds of CPU used

The IO used

Library cache missed

Optional execution plan

List of row source operations

A report that summarizes and analyzes how many similar and completely different statements are in the trace file. If the same statement, the parse column is always a large value, indicating that bound variables are not used.

Count = number of times OCI procedure was executed. OCI is the calling interface of oracle, which provides a set of interface subroutines (functions) that can access ORACLE database. The purpose of accessing ORACLE database can be achieved by calling in the third generation programming language (such as C language).

Cpu = cpu time in seconds executing in seconds

Elapsed = elapsed time in seconds executing elapsed time in seconds

Disk = number of physical reads of buffers from disk physical read

Query = the space obtained by number of buffers gotten for consistent read for consistency.

The number of buffer obtained by all parse, execute, fetch in consistent read mode. Buffer in consistency mode is used to provide a snapshot of a consistent read to a long-running transaction, and the cache actually stores the state in the header.

Current = number of buffers gotten in current mode (usually for update) the number of database block hits, usually for update

The number of buffer obtained in current mode. Generally, buffer is obtained when performing insert, update or delete operations in current mode.

Rows = the total number of rows processed by each call type number of rows processed by the fetch or execute call

And these:

Misses in library cache during parse: the number of hard parsing that occurs in parsing. If it is soft parsing, Misses in library cache during parse will be 0.

Misses in library cache during execute: the number of hard parsing that occurred during the call phase. If hard parsing does not occur when the call is executed, the line Misses in library cache during execute will not exist.

Key reference: http://czmmiao.iteye.com/blog/1493765

Analyze the following code:

SQL ID: 00fqk94bdzqnj Plan Hash: 644658511

Select sid,serial#,username

From

V$session where username is not null

Call count ① cpu elapsed disk query current rows

--

Parse 1 0.01 0.01 0 0 0

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 00 0 2

--

Total 4 0.01 0.01 0 ② 0 0 2

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1

① assumes that the count column, the fetch part executes 17324 times, and the number of rows rows obtained is 259806. By dividing the two, you can get a fetch, how many rows of records can be obtained, and 15 rows of data at a time. It is suspected that an array fetch operation is used.

In theory, ②, elapsed time=CPU time+disk time, that is, if the elapsed time is 1.85 and the CPU is 1.82, then disk may be 3. But it is also possible that there are waiting events, spending a lot of time on waiting events.

③ can judge the disk IO by the proportion of disk IO to logical IO, disk/query+current. If the disk IO is too large, it may be that the db_buffer_size is too small. Of course, this is also related to the specific characteristics of SQL.

④ query+current/rows requires an average number of block per row. If it is too large (more than 20), the SQL statement is too inefficient and the data is too scattered, so you can consider reorganizing objects.

⑤ identifies a new SQL analysis through SQL ID: 06nvwn223659v Plan Hash: 0, and you will find that many of them are the system's own SQL, so you don't have to look at it directly.

The above is all the content of the article "how to use 10046 events in oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.