Oracle Sql_trace Simple Application Introduction

Sql_trace is a powerful auxiliary diagnostic tool provided by Oracle for SQL tracing. In the diagnosis and resolution of daily database problems, Sql_trace is a very common method.

1. Enter the host of the database with Putty or other host tools.

2. Log in to Oracle via sqlplus at the command line.

?? ? Connect Database command: Sqlplus Username/password

3. Parse the SQL statement using Sql_trace.

The Sql_trace can be enabled globally as an initialization parameter, or it can be enabled at a specific session by command line mode. (The following cases are performed under the session )

? 1). In the global enabled ?

? specified in the parameter file (pfile/spfile):

? sql_trace =true

> Tip: By enabling sql_trace globally, we can track the activity of all background processes, many abstract descriptions in the documentation, and by tracking the real-time changes in the file, we can clearly see the close coordination between each process.

So it is not recommended to use the above method

?2). at the current session level, set

? Most of the time we use Sql_trace to track the current process. The background database recursive activity of the current operation can be discovered by tracing the current process (which is especially effective when studying the new features of the database),
Study SQL execution, find background errors, etc.

Enable trace:sql>?alter?session?set?sql_trace=true; for the current session session?altered. The SQL operation will be tracked at this point: sql>?select?count (*)? from?dba_users;--This SQL statement can be replaced by SQL that requires trace analysis. COUNT (*)----------???????? 34 End Trace:sql>?alter?session?set?sql_trace=false; Session?altered.

or use ? Dbms_session package? to turn sql_trace on or off
Sql> exec dbms_session. Set_sql_trace (Sql_trace Boolean);

? ?

General seesion tracking information, corresponding to the system initialization parameter file parameters Show?parameter?user_dump??? --11g before using user_dump_dest corresponds to its location. Sql>?show?parameter?user_dump?--View the location of the trace file in the session name????????????????????????????????? TYPE?????????????????????????????? VALUE------------------------------------?---------------------------------?------------------------------User _dump_dest??????????????????????? String???????????????????????????? /oracle/diag/rdbms/templatedb/?????????????????????????????????????????????????????????????????????????????????????? Templatedb/trace???? Sql>?show?parameter?trace?--View the value of the trace's associated parameter in the current session, the value of sql_trace changes as the trace is turned on and off, as follows for the validation 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--setting a custom identifier, or not setting it, will change the string at the end of the current session file name, such as: Templatedb_ora_5581.trc? 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

You can exit using the Exit command.

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

[email protected] trace]$ ll *test*

-RW-R-----1 Oracle oinstall 2780 DEC 11:41 templatedb_ora_10420_testsession.trc

-RW-R-----1 Oracle Oinstall? 136 Dec 11:41 TEMPLATEDB_ORA_10420_TESTSESSION.TRM

7? using a command-line tool that comes with Oracle tkprof, generate a readable text file from the trace file

[Email protected] trace]$ tkprof templatedb_ora_10420_testsession.trc testsession.txt

[email protected] trace]$ ll *test*

-RW-R-----1 Oracle oinstall 2780 DEC 11:41 templatedb_ora_10420_testsession.trc

-RW-R-----1 Oracle Oinstall? 136 Dec 11:41 TEMPLATEDB_ORA_10420_TESTSESSION.TRM

-rw-r--r--1 Oracle oinstall 5605 DEC 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:?61yfbh3s7h5x1? Plan? Hash:?2596900044select?count (1)? From?test_random_04call????? Count??????? Cpu???? Elapsed??????? Disk?????? Query???? Current???????? Rows-------?------?? --------?----------?----------?----------?----------?? ----------Parse???????? 1?????? 0.00??????? 0.00?????????? 0?????????? 0?????????? 0??????????? 0Execute?????? 1?????? 0.00??????? 0.00?????????? 0?????????? 0?????????? 0??????????? 0Fetch???????? 2?????? 0.10??????? 0.10?????????? 0??????? 2769?????????? 0??????????? 1-------?------?? --------?----------?----------?----------?----------?? ----------Total???????? 4?????? 0.10??????? 0.10?????????? 0??????? 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 Understand Tkprof

Call ?: Each SQL statement is processed into the following three sections
??Parse: This step transforms the SQL statement into an execution plan, including checking for proper authorization and the existence of the tables, columns, and other referenced objects that are needed.
??Execute: This step is really executed by Oracle to execute the statement. For INSERT, UPDATE, delete operations, this step modifies the data, and for the select operation, this step is to determine the selected record.
??Fetch: Returns the record obtained in the query statement, only the SELECT statement is executed.
COUNT: The number of times this statement was parse, execute, Fetch.
CPU: The time, in seconds, of the CPU consumed by this statement for all parse, execute, and fetch.
ELAPSED: This statement is all consumed in the total time of parse, execute, Fetch.
DISK: The number of blocks that are physically read from the data file on disk. In general, you want to know what data is being read from the cache instead of the data that is being read from the disk.
QUERY: In consistent read mode, the number of buffer obtained for all parse, execute, Fetch. The buffer of the consistency mode is used to provide a consistent read snapshot of a long-running transaction, and the cache actually stores the state in the head.
Current: The number of buffer obtained in current mode. In general, the INSERT, update, and delete operations in current mode will get buffer. In the current mode, if a new cache is found in the cache that is sufficient to present the transaction, these buffers will be read into the buffer zone.
ROWS: The number of records returned by all SQL statements, but does not include the number of records returned in the subquery. For the SELECT statement, the return record is the fetch step, and for the INSERT, UPDATE, delete operation, the return record is the Execute step.

?

A, query+current/rows average number of blocks required per line, too large (more than) SQL statement efficiency is too low
B, parse Count/execute count parse count should be as close as 1, if too high, SQL will make unnecessary reparse
C, rows fetch/fetch fetch array size, too small to take advantage of the bulk Fetch function, increase the data between the client and the server round-trip times.
D, disk/query+current disk IO accounted for the proportion of logical IO, too large may be db_buffer_size too small (also related to the specific characteristics of SQL)
E, elapsed/cpu too large to indicate that the execution of the process spent a lot of time waiting for some kind of resources
F, CPU or elapsed too large to indicate that the execution time is too long, or consumes a lot of CPU time, should consider optimization
G, rows in the execution plan indicate the number of rows accessed during the processing phase to minimize

Oracle Sql_trace Simple Application Introduction