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

SQL_TRACE is a powerful assistant diagnostic tool for SQL tracking provided by Oracle. SQL_TRACE is a very common method in daily database problem diagnosis and resolution.

1. Enter the host where the database is located through putty or other host tools.

two。 Log in to oracle. Sqlplus from the command line.

Connect to database command: sqlplus username/password

3. Use SQL_TRACE to parse sql statements.

SQL_TRACE can be enabled globally as an initialization parameter or on a specific session from the command line. (the following cases are all carried out under session)

1)。 Enable globally

Specify in the parameters file (pfile/spfile):

Sql_trace = true

Enabling SQL_TRACE globally will cause the activities of all processes to be tracked, including background processes and all user processes, which will usually lead to serious performance problems, so be cautious in the production environment. This parameter is a dynamic parameter after 10g and can be adjusted at any time, which is very effective in some diagnostics.

Tip: by enabling sql_trace globally, we can track the activities of all background processes, and many abstract instructions in the document. By tracking the real-time changes of the file, we can clearly see the close coordination between the various processes.

Therefore, the above methods are not recommended.

2)。 Set at the current session level

Most of the time we use sql_trace to track the current process. By tracking the current process, you can find the background database recursive activity of the current operation (this is especially effective when studying new database features)

Study the implementation of SQL, find background errors, etc.

Enable tracing for the current session: SQL > alter session set sql_trace=true;Session altered. At this point, the SQL operation will be tracked: SQL > select count (*) from dba_users;-- this sql statement can be replaced with a sql that needs to be tracked and analyzed. COUNT (*)-34 ends tracking: SQL > alter session set sql_trace=false;Session altered.

Or use the DBMS_SESSION package to turn sql_trace on or off

SQL > exec DBMS_SESSION.SET_SQL_TRACE (sql_trace boolean)

Generally put seesion tracking information, corresponding to the system initialization parameter file parameter show parameter user_dump-11g before using user_dump_dest corresponding to its location. SQL > show parameter user_dump-- View the location of trace files in session NAME TYPE VALUE -user_dump_dest string / oracle/diag/rdbms/templatedb/ templatedb/trace SQL > show parameter trace-- View the values of the relevant parameters of trace in the current session The value of sql_trace will change with the opening and closing of trace. The following is for the verification process only. name TYPE VALUE -log_archive_trace integer 0sec_protocol_error_trace_action string TRACEsql_trace boolean FALSEtrace_enabled Boolean TRUEtracefile_identifier stringSQL > alter session set sql_trace=TRUE Session altered.SQL > show parameter traceNAME TYPE VALUE -log_archive_trace integer 0sec_protocol_error_trace_action string TRACEsql_trace boolean TRUEtrace_enabled boolean TRUEtracefile_identifier stringSQL > select value from v$diag_info where name='Default Trace File' -- View the current session default trace file urlVALUE- -/ oracle/diag/rdbms/templatedb/templatedb/trace/templatedb_ora_5581.trc-- sets custom identifiers Otherwise, the string at the end of the current session file name will be changed, such as: templatedb_ora_5581.trc becomes templatedb_ora_5581_testsession.trc, as shown below: SQL > alter session set tracefile_identifier='testsession' Session altered.SQL > select value from v$diag_info where name='Default Trace File' VALUE- -/ oracle/diag/rdbms/templatedb/templatedb/trace/templatedb_ora_5581_testsession.trc

5 exit sqlplus

Use the exit command to exit.

6 according to the identifier just set, you can easily lock the trace file involved in the current session.

[oracle@uatcrvcrtdb trace] $ll * test*

-rw-r- 1 oracle oinstall 2780 Dec 11 11:41 templatedb_ora_10420_testsession.trc

-rw-r- 1 oracle oinstall 136 Dec 11 11:41 templatedb_ora_10420_testsession.trm

7 use tkprof, a command line tool included with Oracle, to generate an easy-to-read text file from the trace file.

[oracle@uatcrvcrtdb trace] $tkprof templatedb_ora_10420_testsession.trc testsession.txt

[oracle@uatcrvcrtdb trace] $ll * test*

-rw-r- 1 oracle oinstall 2780 Dec 11 11:41 templatedb_ora_10420_testsession.trc

-rw-r- 1 oracle oinstall 136 Dec 11 11:41 templatedb_ora_10420_testsession.trm

-rw-r--r-- 1 oracle oinstall 5605 Dec 11 11:44 testsession.txt

Cat testsession.txtTKPROF: Release 11.2.0.4.0-Development on Mon Dec 11 11:44:46 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Trace file: templatedb_ora_10420_testsession.trcSort options: default****count = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time In seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call**** * SQL ID: 61yfbh4s7h6x1 Plan Hash: 2596900044select count (1) from test_random_04call count cpu elapsed disk query current rows-- -Parse 1 0.00 0.00 0 0Execute 1 0.00 0.00 00 0Fetch 2 0.10 0.100 2769 0 1- -total 4 0.10 0.100 2769 0 1Misses in library cache during parse: 0Optimizer mode: ALL_ ROWSParsing user id: 62Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation--1 1 1 SORT AGGREGATE (cr=2769 pr=0 pw=0 time=102729 us) 999999 999999 999999 TABLE ACCESS FULL TEST_RANDOM_04 (cr=2769 pr=0 pw=0 time=192830 us cost=762 size=0 card=999999) *

Oracle performance Optimization: how to read tkprof

CALL: the processing of each SQL statement is divided into the following three parts

Parse: this step converts the SQL statement into an execution plan, which includes checking for the correct authorization and the existence of tables, columns, and other referenced objects.

Execute: this step is really for Oracle to execute the statement. For insert, update, delete operations, this step modifies the data, and for select operations, it simply determines the selected record.

Fetch: returns the records obtained in the query statement, in which only the select statement will be executed.

COUNT: the number of times this statement has been parse, execute, fetch.

CPU: this statement is the time in seconds of cpu consumed by all parse, execute, and fetch.

ELAPSED: all the total time spent in parse, execute, fetch in this statement.

DISK: the number of blocks physically read from a data file on disk. Generally speaking, you want to know more about the data being read from the cache than from the disk.

QUERY: 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: the number of buffer obtained in current mode. Generally, buffer is obtained when performing insert, update or delete operations in current mode. In current mode, if a new cache is found in the cache to be sufficient for the current transaction, the buffer will be read into the cache.

ROWS: the number of records returned by all SQL statements, excluding the number of records returned in the subquery. For select statements, the record is returned at the fetch step, and for insert, update, and delete operations, the record is returned at the execute step.

A, query+current/rows average number of block required per line, if too large (more than 20) SQL statement efficiency is too low

B, Parse count/Execute count parse count should be as close to 1 as possible. If it is too high, SQL will do unnecessary reparse.

C, the size of rows Fetch/Fetch Fetch Array is too small, it does not make full use of the function of batch Fetch, increasing the number of round trips between the client and the server.

D, disk/query+current disk IO accounts for the proportion of logical IO, if too large, it may be that the db_buffer_size is too small (also related to the specific features of SQL)

E, elapsed/cpu too large means that the execution process spent a lot of time waiting for a certain resource

Too large F and cpu Or elapsed means that the execution time is too long, or a lot of CPU time is consumed, so optimization should be considered.

G. The Rows in the execution plan indicates that the number of rows accessed during this processing phase should be reduced as much as possible

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.