Oracle uses TRACE for SQL Performance Analysis

Oracle uses TRACE for SQL Performance Analysis

If the SQL _trace parameter is set to true, the entire instance will be tracked, including all processes: User processes and background processes, which may cause serious performance problems and must be used with caution in the production environment.

1. perform trace operations based on related transactions or SQL:

1) notes before use

1. The initialization parameter timed_statistics = true. Allows SQL trace and other dynamic performance views to collect time-related parameters. Be sure to open it, otherwise the related information will not be collected. This is a dynamic parameter and can be set at the session level.

SQL> alter session set titimed_statistics = true

2. the size limit of the MAX_DUMP_FILE_SIZE trace file. If there are many trace information, you can set it to unlimited. It can be KB, MB, and 9I starts to be unlimited by default. This is a dynamic parameter and can be set at the session level.

SQL> alter system set max_dump_file_size = 300

SQL> alter system set max_dump_file_size = unlimited

2) trace execution process:

1. Start SQL _TRACE: SQL> alter session set SQL _trace = true;

2. perform related transactions or SQL operations: SQL> select * from t;

3. Disable SQL _TRACE: SQL> alter session set SQL _trace = false;

You can also use the system package DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION provided by Oracle. For example, first, use the top command on the OS to find the PID of the process that currently occupies the highest cpu resources; then find the corresponding sid and serial # in the database based on the PID Number #.

SQL> execute dbms_system.set_ SQL _trace_in_session (sid, serial #, true );

SQL> execute dbms_system.set_ SQL _trace_in_session (sid, serial #, false );

2. Obtain the location of the currently generated trace file:

In Oracle 10 Gb, the default path of the trace file generated by SQL _TRACE is $ ORACLE_BASE/admin/SID/udump. At 11 GB, the default path of trace is: $ ORACLE_BASE/diag/rdbms/orcl/trace directory.

You can also query the default path of the trace file: select value from v $ parameter where name = 'user _ dump_dest '(BACKGROUND_DUMP_DEST)

To modify: alter system set user_diagnostic_dest = 'd: \ oracle \ trace ';

Or: select tracefile from v $ process where addr in (select paddr from v $ session where sid in (select sid from v $ mystat ));

In addition, you can use the following SQL statement to directly find the current trace file name.

SELECT d. VALUE | '\' | LOWER (RTRIM (I. INSTANCE, CHR (0) | '_ ora _' | p. spid | '. trc'

AS "trace_file_name"

FROM (SELECT p. spid

FROM v $ mystat m, v $ session s, v $ process p

WHERE m. statistic # = 1 AND s. SID = m. sid and p. addr = s. paddr) p,

(SELECT t. INSTANCE

FROM v $ thread t, v $ parameter v

WHERE v. NAME = 'thread'

AND (v. VALUE = 0 OR t. thread # = TO_NUMBER (v. VALUE) I,

(SELECT VALUE

FROM v $ parameter

Where name = 'user _ dump_dest ') d;

3. Convert and generate the trace file:

SQL _TRACE generates the most primitive trace file with poor readability. Therefore, we usually use the tkprof tool to process the trace file. Tkprof is an Oracle tool used to process the original trace file. It is mainly used to merge and summarize some items in the trace file, standardize the file format, and make the file more readable. Tkprof is system-level and can be executed directly in the system.

Note: The tkprof tool can only be used to process traces generated by SQL _TRACE and 10046 events. Other events such as 10053 cannot be processed.

Format: tkprof tracefile outputfile [optional | parameters]

C:/Users/Administrator/VIDI> tkprof d:/app/administrator/diag/rdbms/orcl/trace/orcl_ora_3048.trc orcl_ora_3048.txt sys = no

Parameters and options:

Explain = user/password: Execute the explain command to put the result in the SQL trace output file.

Sys = [yes/no] determine whether the system lists the SQL statements generated or re-tuned by the sys user. When set to no, the trace file is more readable.

Sort = sort_option sort SQL trace output files in descending order according to the specified Method

Sort_option option: Set sorting options. Multiple options can be separated by commas. By default, the SQL sequence found in the trace file is used.

Sort prscnt by resolution times

Prscpu is sorted by resolution cpu time

Prsela is sorted by the time it has been parsed.

The prsdsk is sorted by the number of physical read operations during resolution.

Prsqry sorts the number of times that data blocks are read in consistent mode during parsing.

Prscu is sorted by the number of currently read data blocks during parsing.

Execnt sort by execution count

Execpu is sorted by cpu time consumed during execution

Exeela is sorted by execution time

Exedsk is sorted by the number of physical read operations performed

Exeqry sorts the number of times data blocks are read in consistent mode during execution.

Execu is sorted by the number of times data blocks are read in the current mode during execution.

Exerow is sorted by the number of records processed during execution.

Exemis is sorted by Library Buffer errors during execution

Fchcnt is sorted by the number of returned data

Fchcpu is sorted by the time consumed by the cpu for the returned data

Fchela is sorted by the time the returned data has elapsed

Fchdsk is sorted by the number of physical read operations when data is returned

Fchqry sorts the number of times data blocks are read in consistent mode when data is returned.

Fchcu sorts the number of data blocks read in the current mode when data is returned.

Fchrow is sorted by the number of data processed when the returned data is returned.

Note: fchdsk, fckchela, and fchqry are frequently used in these sorting. the problematic SQL statements are generally caused by large queries. Of course, full table scans also exist during updates, inserts, and deletions. This requires the following options: exedsk, exeqry, exeela, and so on. Detailed analysis based on the specific situation.

Both the Cpu time and Elapsed time are in seconds, and the two values are basically the same, but I usually use elapsed, which is the user's corresponding time, the time from running SQL statements to obtaining results is more practical.

Meanings of columns in the tkprof output file:

Parse: converts an SQL statement into an execution plan, including checking whether the authorization is correct and whether the Table, column, and other referenced objects need to be obtained exist, the information exists in v $ librarycache. v $ rowcache ..

Execute: statements actually executed by oracle, such as insert, update, and delete, which modify data. For select operations, this is only to determine the number of selected rows.

Fetch: returns the number of rows in the query. Only select statements are collected.

Count: The number of times this statement is run by parse, execute, and fetch.

Cpu: The total cpu time used by all the parse, execute, and fetch statements, in seconds. If TIMED_STATISTICS is disabled, the value is 0.

Elapsed: the total time consumed by all the parse, execute, and fetch statements in seconds. If TIMED_STATISTICS is disabled, the value is 0.

Disk: the number of data blocks read by all the parse, execute, and fetch statements from the data files on the Disk.

Query: In consistent read mode, the number of data blocks obtained by all the parse, execute, and fetch statements in this Statement (this part is read from the memory, that is, logical reading, equivalent to the consistent gets in the execution plan)

Current: in current mode, the number of data blocks obtained by all the parse, execute, and fetch statements in this statement is generally obtained by delect, insert, and update operations in current mode. Data Block

Rows: the number of Rows returned by the statement, excluding the number of records returned in the subquery. For select statements, return in the fetch step. For insert, delete, update operations, the returned records are in the execute step.

4. trace file analysis process:

1. First, find the sq l (sort = fchdsk) with multiple disks, which means full table scan;

2. Find the long running time (sort = fchela), which means that the SQL may be poorly written or the disk may have a large number of logical reads;

3. Find out more consistent reads (sort = fchqry). When the table is not very large (it may be All cached), there is no disk read, but it does not mean that no indexes need to be created, or the SQL needs to be optimized;

4. Find the number of data (sort = exedsk, exeela, exeqry) obtained from the buffer in the current mode. These operations are mainly concentrated in dml statements, check whether it is necessary to optimize the SQL statement or create an index. The reason for sorting is to quickly locate the SQL statement in many cases. If there are few SQL statements, there is no need to sort the SQL statement, however, we need to have an idea to analyze the problem.

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------