1. Trace文件的位置
1）11gR1以后
Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。
可以用以下命令，获取DIAGNOSTIC_DEST的位置：
show parameter diagnostic_dest
2）11gR1以前
如果是用户进程，10046 trace文件会被生成在user_dump_dest下；如果是后台进程，trace文件会被生成在background_dump_dest下。
下面的命令可以显示user_dump_dest:
show parameter user_dump_dest
注：通过设定tracefile_identifier设置可以帮助我们更容易的找到生成的trace文件
2. 在Session级打开trace
适用于SQL语句可以在新的session创建后再运行。
2.1）在session级收集10046 trace：
alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever,level 12'; -- 执行需要被trace的SQL -- select * from dual; quit; -- 如果不退出当前session, 可以用以下命令关闭trace: -- alter session set events '10046 trace name context off';
注意，如果session没有被彻底地关闭并且跟踪被停止了，某些重要的trace信息的可能会丢失。
注意：这里我们将"statistics_level"设置为all，这是因为有可能这个参数在系统级不是默认值"TYPICAL"（比如 BASIC）。为了收集性能相关问题的信息我们需要打开某个级别的statistics。我们推荐在 session 级将这个参数设置成 ALL 以便于收集更多的信息，尽管这不是必须的。
2.2） 跟踪一个已经开始的进程
如果需要跟踪一个已经存在session，可以用 oradebug连接到session上，并发起10046 trace。
确定被跟踪session：
-- 1. 已知目标session id，找出OS的进程ID(spid): select s.inst_id, p.PID, p.SPID, s.SID, s.serial# from gv$process p, gv$session s where s.paddr = p.addr and s.inst_id = p.inst_id; -- SPID 是操作系统的进程标识符(os pid) -- PID 是Oracle的进程标识符(ora pid) -- 2. 未知 session id，找出OS的进程ID(spid): set lines 168 pages 999 column line format a79 set heading off select 'ospid: ' || p.spid || ' # sess:''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' ' || 'osuser: ' || s.osuser || ' machine: ' || s.machine || ' dbuser: ' || s.username || ' program: ' || s.program line from gv$session s, gv$process p where p.addr = s.paddr -- except server processes and s.username <> ' '; -- and s.username = '' ;
2.3）跟踪命令
-- 1. 通过OS PID进行跟踪 connect / as sysdba oradebug setospid 9834 -- <<<< 假设 [OS PID=9843] oradebug unlimit oradebug event 10046 trace name context forever,level 12; -- 2. 通过oracle pid 跟踪 connect / as sysdba oradebug setorapid 9834 -- <<<< 假设Oracle进程标识符[PID=9843] oradebug unlimit oradebug event 10046 trace name context forever,level 12; -- 3. 关闭跟踪 oradebug event 10046 trace name context off; 备注：如果是使用了12c的multi thread下，那么需要使用v$process中新的列stid来找到对应的thread, 因为Oracle把多个processes放进了一个单独的 ospid 中。如果想找到特定的thread, 使用下面的语法: oradebug setospid <spid> <stid> oradebug unlimit tracefile -- 名字会是 <instance><spid>_<stid>.trc 的格式.
3. 实例层的跟踪
注意: 在实例层设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响。它只会对新建立的连接进行跟踪。
使用场景：系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候。在这种情况下，可以打开系统层跟踪一小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息。
3.1 通过alter命令设置跟踪
-- 用以下命令打开系统层的跟踪： alter system set events '10046 trace name context forever,level 12'; -- 用以下命令关闭在所有session中的10046跟踪： alter system set events '10046 trace name context off';
3.2 通过初始化参数设置跟踪
-- 设置以下参数并重新启动实例后，实例上所有的session都会打开跟踪。 event="10046 trace name context forever,level 12" -- 移除这个参数并且重启实例， 或者使用下面的alter system命令可以关闭跟踪。 alter system set events '10046 trace name context off';
3.3 通过logon trigger设置跟踪
-- 跟踪某个特定用户的操作时，可以使用logon trigger来打开跟踪 CREATE OR REPLACE TRIGGER SYS.set_trace AFTER LOGON ON DATABASE WHEN (USER like '&USERNAME') DECLARE lcommand varchar(200); BEGIN EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger'''; EXECUTE IMMEDIATE 'alter session set statistics_level=ALL'; EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED'; EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'''; END set_trace; / -- 注：为了能打开跟踪session, 执行trigger的用户需要被显式地授予'alter session' 权限. grant alter session to <USERNAME> ;
3.4 通过SQLTXPLAIN (SQLT)收集trace跟踪(Doc ID 215187.1)
-- 参考：http://m.blog.itpub.net/28539951/viewspace-2109691/
-- SQLT requires no license and is FREE
-- 1) 安装&卸载 -当前版本12.2.180331
-- 安装时六个地方交互，包括创建密码、指定表空间等，推荐创建独立表空间提供于SQLT使用，最小50M。
-- SQLT工具安装时会创建2个用户(SQLTXPLAIN,SQLTXADMIN)和一个角色(SQLT_USER_ROLE)：
SQLTXPLAIN 用户具备系统权限：
CREATE SESSION
CREATE TABLE
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN -- in 12c
SQLTXADMIN 用户具备系统权限：
ADMINISTER SQL MANAGEMENT OBJECT
ADMINISTER SQL TUNING SET
ADVISOR
ALTER SESSION
ANALYZE ANY
SELECT ANY DICTIONARY
SELECT_CATALOG_ROLE
SQLT_USER_ROLE 用户具备系统权限：
ADVISOR
SELECT_CATALOG_ROLE
# unzip sqlt_122180331.zip
SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms. For full "Applies to:" information, refer to Document 215187.1
Installation steps:
1. Uninstall a prior version (optional).
This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql
2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql
During the installation you will be asked to enter values for these parameters:
1. Optional Connect Identifier (mandatory when installing in a Pluggable Database)
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.
The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.
2. SQLTXPLAIN password.
Case sensitive in most systems.
3. SQLTXPLAIN Default Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.
4. SQLTXPLAIN Temporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.
5. Optional Application User.
This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.
6. Licensed Oracle Pack. (T, D or N)
You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.
If a silent installation is desired, there are three options to pass all 6 installation parameters:
1. In a file.
Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql instead of sqlt/install/sqcreate.sql.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdefparams.sql
SQL> START sqcsilent.sql
2. In-line.
Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T
3. Internal installation at Oracle.
Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sql followed by sqlt/install/sqcsilent.sql.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcinternal.sql
Uninstalling SQLT
Uninstalling SQLT removes the SQLT repository and all SQLTXPLAIN/SQLTXADMIN schema objects. SQLTXPLAIN and SQLTXADMIN users also gets dropped. To uninstall SQLT simply execute sqlt/install/sqdrop.sql connected as SYS.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql
Upgrading SQLT
If you have a prior version of SQLT already installed in your system, you can upgrade SQLT to its latest version while partially preserving most objects of your existing SQLT repository. The new migrated SQLT repository can then be used to restore CBO statistics or to perform a COMPARE between old and new executions of SQLT.
To upgrade SQLT, simply do an installation without performing the optional uninstall step.
If the upgrade fails, then it is possible the prior SQLT version was too old to be upgraded. In such case please proceed to uninstall SQLT first, followed by a clean installation.
-- 2) SQLT使用
Before attempting using any of the SQLT main methods, be sure SQLT has been installed and that your SQLT user has been granted the SQLT_USER_ROLE.
If SQLT has been installed bypassing SQL*Net (meaning that you did not input any value for the Connect Identifier during the installation), then prior to running any of the SQLT main methods from a remote client, you will need to manually set the connect_identifier parameter. Ie. if you connected using sqlplus scott/tiger@myprod then you will need to execute: EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@myprod');
SQLT provides 7 main methods that generate diagnostics details for one SQL statement: XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT and XPREXC. While XTRACT, XECUTE, XTRXEC, XTRSBY, XPREXT and XPREXC handle bind variables and understand about bind peeking, XPLAIN does not. This is because XPLAIN is based on the EXPLAIN PLAN FOR command which is blind to bind peeking. For this reason avoid using XPLAIN if possible.
Besides the bind peeking limitation on XPLAIN all 7 main methods provide enough diagnostics details to make an initial assessment of a SQL performing poorly or generating wrong results. If the SQL still resides in memory or in the Automatic Workload Repository (AWR) use then XTRACT or XTRXEC, else use XECUTE. For Data Guard or standby read-only databases use XTRSBY. Use XPLAIN only if the other methods are not feasible. XPREXT and XPREXC are similar to XTRACT and XECUTE but they disable some SQLT features in order to improve SQLT performance.
XTRACT Method
Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XECUTE. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace (above the SQL text and identified by the "hv=" token).
If the SQL is still in memory, or it has been captured by AWR, then XPREXT finds it and provides a set of diagnostics files, else XPREXT errors out.
Important performance statistics, like actual number of rows per execution plan operation, will be available if the SQL was parsed while parameter STATISTICS_LEVEL was set to ALL when the SQL was hard-parsed. You can also produce same valuable performance statistics by including the following CBO hint in your SQL: /*+ GATHER_PLAN_STATISTICS */. On 11g you may want your SQL to contain the following CBO Hints for enhanced diagnostics: /*+ GATHER_PLAN_STATISTICS MONITOR */
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XPREXT.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use this XPREXT method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/sqltxtract.sql script passing the SQL_ID or HASH_VALUE.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtract.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtract.sql 2524255098 sqltxplain_password
XECUTE Method
This method provides more detail than XTRACT. As the name XECUTE implies, it executes the SQL being analyzed, then it produces a set of diagnostics files. Its major drawback is that if the SQL being analyzed takes long to execute, this method will also take long.
As a rule of thumb, use this method only if the SQL takes less than 1hr to execute, else use XTRACT.
Before you can use this XECUTE method, you have to create a text file that contains your SQL text. If the SQL includes bind variables, your file must contain the bind variable declaration and assignment. Use sqlt/input/sample/script1.sql as an example. Your SQL should contain the token /* ^^unique_id */ which should be spelled out exactly as it shows here. In other words: please do not change it.
If your SQL requires binds with data types not allowed by SQL*Plus, or if it uses collections, you may be restricted to embed your SQL into an anonymous PL/SQL block. In such case use sqlt/input/sample/plsql1.sql as an input example to this method.
For statements that modify data, i.e. INSERT/UPDATE/DELETE, a savepoint is created prior to statement execution and the transaction is rolled back to the savepoint at the conclusion of the session. For further information regarding SAVEPOINT please consult the Oracle Concepts reference manual.
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XECUTE.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use the XECUTE method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/sqltxecute.sql script passing the name of the text file that contains your SQL text and its bind variables. You may want to place this file into the sqlt/input directory and run XECUTE while standing on the sqlt main directory, as shown below.
# cd sqlt
# sqlplus apps
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxecute.sql input/sample/script1.sql sqltxplain_password
XTRXEC Method
This method combines the features of XTRACT and XECUTE. Actually, XTRXEC executes both methods serially. The XTRACT phase generates a script that contains the extracted SQL together with the binds declaration and assignment for an expensive plan found for the requested SQL statement. XTRXEC then executes the XECUTE phase using the script created by the first.
The selection of the values of the bind variables used by XTRACT to create the script is based on the peeked values at the moment the most expensive plans in memory were generated. Expensive plans are selected according to their average elapsed time.
If XTRXEC errors out having executed only the 1st phase (XTRACT), you may need to review the script used during the 2nd phase (XECUTE) and adjust the bind variables accordingly. This is specially true when uncommon data types are used.
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XTRXEC.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use this XTRXEC method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/sqltxtrxec.sql script passing the SQL_ID or HASH_VALUE.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password
XTRSBY Method
Use this method if you need to analyze a SQL executed on a Data Guard or stand-by read-only database. You need to know the SQL_ID or the HASH_VALUE of the SQL to be analyzed.
Create on Primary database a link to read-only database connecting as any user that has access to the data dictionary. A DBA account would be fine:
CREATE PUBLIC DATABASE LINK V1123 CONNECT TO mydba IDENTIFIED by mydba_password
USING '(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)
(HOST=coesrv14.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SID = V1123)))';
If the SQL is still in memory in the read-only database, then XTRSBY finds it and provides a set of diagnostics files, else XTRSBY errors out.
Important performance statistics, like actual number of rows per execution plan operation, will be available if the SQL was parsed while parameter STATISTICS_LEVEL was set to ALL when the SQL was hard-parsed in the read-only database. You can also produce same valuable performance statistics by including the following CBO hint in your SQL: /*+ GATHER_PLAN_STATISTICS */. On 11g you may want your SQL to contain the following CBO Hints for enhanced diagnostics: /*+ GATHER_PLAN_STATISTICS MONITOR */
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XTRSBY.
XTRSBY takes 3 parameters: the SQL id, the DB_LINK id, and the SQLTXPLAIN password.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use this XTRSBY method, be sure SQLT has been installed on the Primary first, and replicated into the read-only database. Then connect into SQL*Plus in Primary and execute the sqlt/run/sqltxtrsby.sql script passing the SQL_ID or HASH_VALUE followed by the DB_LINK.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
SQL> START sqltxtrsby.sql 0w6uydn50g8cx sqltxplain_password V1123
SQL> START sqltxtrsby.sql 2524255098 sqltxplain_password v1123
In addition to XTRSBY you may want to execute sqlt/utl/sqlhc.sql or sqlt/utl/sqlhcxec.sql directly from the read-only database. These two read-only scripts do not install anything on the database nor they execute DML commands. They provide additional information that is not available in XTRSBY.
XPLAIN Method
This method is based on the EXPLAIN PLAN FOR command, therefore it is blind to bind variables referenced by your SQL statement. Use this method only if XTRACT or XECUTE are not possible.
Before using the XPLAIN method, you have to create a text file that contains your SQL text. If the SQL includes bind variables, you have two options: leave the SQL text "as is", or carefully replace the binds with literals of the same datatype. Use sqlt/input/sample/sql1.sql as an example.
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XPLAIN.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use this XPLAIN method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/sqltxplain.sql script passing the name of the text file that contains your SQL text. You may want to place this file into the sqlt/input directory and run XPLAIN while standing on the sqlt main directory, as shown below.
# cd sqlt
# sqlplus apps
SQL> START [path]sqltxplain.sql [path]filename [sqltxplain_password]
SQL> START run/sqltxplain.sql input/sample/sql1.sql sqltxplain_password
XPREXT Method
Use this method if you have used XTRACT and you need a faster execution of SQLT while disabling some SQLT features. Script sqlt/run/sqltcommon11.sql shows which features are disabled.
Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XPREXC. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace (above the SQL text and identified by the "hv=" token).
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XPREXT.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use this XPREXT method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/sqltxprext.sql script passing the SQL_ID or HASH_VALUE.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxprext.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxprext.sql 2524255098 sqltxplain_password
XPREXC Method
Use this method if you have used XECUTE and you need a faster execution of SQLT while disabling some SQLT features. Script sqlt/run/sqltcommon11.sql shows which features are disabled.
As a rule of thumb, use this method only if the SQL takes less than 1hr to execute, else use XPREXT.
Before you can use this XPREXC method, you have to create a text file that contains your SQL text. If the SQL includes bind variables, your file must contain the bind variable declaration and assignment. Use sqlt/input/sample/script1.sql as an example. Your SQL should contain the token /* ^^unique_id */ which should be spelled out exactly as it shows here. In other words: please do not change it.
If your SQL requires binds with data types not allowed by SQL*Plus, or if it uses collections, you may be restricted to embed your SQL into an anonymous PL/SQL block. In such case use sqlt/input/sample/plsql1.sql as an input example to this method.
For statements that modify data, i.e. INSERT/UPDATE/DELETE, a savepoint is created prior to statement execution and the transaction is rolled back to the savepoint at the conclusion of the session. For further information regarding SAVEPOINT please consult the Oracle Concepts reference manual.
When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XPREXC.
This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
To use the XPREXC method, be sure SQLT has been installed first, then connect into SQL*Plus as the application user that executed the SQL to be analyzed and execute the sqlt/run/>sqltxprexc.sql script passing the name of the text file that contains your SQL text and its bind variables. You may want to place this file into the sqlt/input directory and run XPREXC while standing on the sqlt main directory, as shown below.
# cd sqlt
# sqlplus apps
SQL> START [path]sqltxprexc.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxprexc.sql input/sample/script1.sql sqltxplain_password
Special Methods
Besides the main methods SQLT provides some special methods.
The most popular special method is COMPARE. This method takes as input two prior executions of SQLT (any of the main methods) and produces a report with a gap analysis.
The other special methods are: TRCANLZR, TRCAXTR, TRCASPLIT and XTRSET. The first three act on a SQL Trace and the latter on a set of SQL statements.
COMPARE Method
Use this COMPARE method when you have two similar systems (SOURCES) and the same SQL statement performs fine in one of them but not in the other. This method helps to pin-point the differences between the two SOURCES in terms of plans, metadata, CBO statistics, initialization parameters and bug fix-control. SQLT has to be installed first in both, and any of the main methods must have been used on the same SQL in both systems.
The compare can take place on any of the two SOURCES or on a 3rd COMPARE system. The latter should contain the SQLT repositories of the two SOURCES. To import a SQLT repository use the syntax provided in the sqlt_99999_readme.html file generated by any of the main methods.
Once the COMPARE system contains the repositories from both SOURCES, execute sqlt/run/sqltcompare.sql connecting as SYS or the application user. A list of STATEMENT_ID is presented, from which you choose which two SQLT stored executions you want to compare. Once you indicate the two STATEMENT_ID you are asked next for particular PLAN_HASH_VALUE from both SOURCES.
# cd sqlt
# sqlplus sqltxplain
SQL> START [path]sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2]
SQL> START run/sqltcompare.sql 92263 72597
SQL> START run/sqltcompare.sql
TRCANLZR Method
This method takes as input a SQL Trace filename and proceeds to analyze this file. The actual trace must be located in the TRCA$INPUT1 directory, which defaults to the USER_DUMP_DEST directory during installation.
The TRCANLZR method also has the capability of analyzing simultaneously several related traces, treating them as a set. This functionality is needed when analyzing parallel execution PX traces. In such case, create a control.txt file with a list of traces (one filename per line and without path specification) and place this control.txt into the TRCA$INPUT1 or TRCA$INPUT2 directories. These two directories default to USER_DUMP_DEST and BACKGROUND_DUMP_DEST respectively during installation. TRCANLZR will then read the control.txt file from one of the two input directories, and it will find the set of traces in either one of those two directories.
TRCANLZR is like TKPROF but with extended functionality. When it analyzes a trace or a set of traces, it also includes schema object characteristics like CBO statistics and some other valuable performance metrics.
To use this TRCANLZR method, be sure SQLT has been installed first. Then, start SQL*Plus connecting as the application user that generated the trace and execute the sqlt/run/sqltrcanlzr.sql script passing the name of the trace to be analyzed, or the name of the control.txt file populated with filenames. Do not include any path specification.
# cd sqlt
# sqlplus [application_user]
SQL> START [path]sqltrcanlzr.sql [SQL Trace filename|control.txt]
SQL> START run/sqltrcanlzr.sql V1122_ora_24292.trc
SQL> START run/sqltrcanlzr.sql control.txt
TRCAXTR Method
This method does the same than TRCANLZR but when the trace analysis completes, it continues with a XTRACT for the Top SQL found in the trace. Basically it consolidates all the generated reports by TRCANLZR and XTRACT on the Top SQL.
To use this TRCAXTR method, be sure SQLT has been installed first. Then, navigate to the sqlt/run directory and start SQL*Plus connecting as the application user that generated the trace. From there, execute the sqlt/run/sqltrcaxtr.sql script passing the name of the trace to be analyzed, or the name of the control.txt file populated with filenames. Do not include any path specification.
# cd sqlt/run
# sqlplus [application_user]
SQL> START sqltrcaxtr.sql [SQL Trace filename|control.txt]
SQL> START sqltrcaxtr.sql V1122_ora_24292.trc
SQL> START sqltrcaxtr.sql control.txt
TRCASPLIT Method
This method takes as input a SQL Trace filename which was created by EVENT 10046 and some other EVENT(s) (usually 10053). Then it proceeds to split this input trace file into two output files. One with the trace lines corresponding to EVENT 10046 and the other with its complement. In other words, the second file contains those trace lines that are not part of the EVENT 10046 grammar. So if the input trace was created using simultaneously EVENT 10046 and EVENT 10053, the resulting output files would be the 10046 trace and the 10053 trace. The actual input trace must be located in the TRCA$INPUT1 directory, which defaults to the USER_DUMP_DEST directory during installation.
To use this TRCASPLIT method, be sure SQLT has been installed first. Then, start SQL*Plus connecting as any SQLT user and execute the sqlt/run/sqltrcasplit.sql script passing the name of the trace to be splitted. Do not include any path specification.
# cd sqlt
# sqlplus [sqlt_user]
SQL> START [path]sqltrcasplit.sql [SQL Trace filename]
SQL> START run/sqltrcasplit.sql V1122_ora_24292.trc
XTRSET Method
The XTRSET extracts from memory or AWR a list of SQL statements identified by their SQL_ID or HASH_VALUE then it executes the XTRACT on each of the SQL statements. At the end it consolidates all the SQLT files into a single compressed file. This XTRSET is used when benchmarking the same set of SQL statements over a series of tests.
When this method is used, it asks once for the SQLTXPLAIN password, which is needed to export the SQLT repository for each execution of XTRACT on the list of SQL statements.
To use this XTRSET method, SQLT has to be installed first. Navigate to the sqlt/run directory and start SQL*Plus connecting as the application user that issued all or most of the SQL statements. From there, execute the sqlt/run/sqltxtrset.sql script. When asked, pass the comma-separated list of SQL statements identified by their SQL_ID or HASH_VALUE, and the password for SQLTXPLAIN.
# cd sqlt/run
# sqlplus [application_user]
SQL> START sqltxtrset.sql
List of SQL_IDs or HASH_VALUEs: 2yas208zgt5cv, 6rczmqdtg99mu, 8w8tjgac6tv12
Advanced Methods and Modules
SQLT provides some additional functionality beyond the main methods and the special methods. Use these advanced methods and modules only if requested by Oracle Support: PROFILE, XGRAM, XPLORE and XHUME. The latter is for exclusive use of Oracle Support and only in an internal testing environment.
PROFILE Method
This PROFILE method allows a quick fix on 10g when a SQL performing poorly happens to have a known better-performing plan. This better plan can be in memory in the same or different system, or in AWR in the same or different system. In other words, if a better plan is available, this method allows "pinning" this plan using a custom SQL Profile. Before you use this method you have to use any of the main methods on the SQL for which you want to extract and pin its plan. On 11g or higher you want to use SQL Plan Management (SPM) instead of this method.
Be aware that PROFILE uses a DBMS_SQLTUNE API, which is part of the SQL Tuning Advisor, therefore licensed through the Oracle Tuning pack. Use this PROFILE method only if your site has a license for the Oracle Tuning pack.
To use this PROFILE method, be sure SQLT has been installed and used in the SOURCE system, then connect into SQL*Plus as SYS or SQLTXPLAIN and execute the sqlt/utl/sqltprofile.sql script. It will ask for the STATEMENT_ID out of a list of prior SQLT executions. After a STATEMENT_ID is selected, it will ask for a PLAN_HASH_VALUE out of a list of available plans. These plans were captured and stored by SQLT when XTRACT or XECUTE were used on the SQL of concern.
SQLT does not have to be installed in the TARGET system where the custom SQL Profile is implemented.
There are basically 4 steps in this PROFILE method.
1. Use XTRACT or XECUTE on the SOURCE system.
2. Execute sqlt/utl/sqltprofile.sql in SOURCE to generate a script with the custom SQL Profile.
3. Review the generated script and adjust the SQL text if needed. For example, to remove the comment caused by the /* ^^unique_id */ if XECUTE was used.
4. Execute the generated script in the TARGET system where the plan will be pinned.
# cd sqlt/utl
# sqlplus sqltxplain
SQL> START sqltprofile.sql [statement id] [plan hash value];
SQL> START sqltprofile.sql 32263 923669362;
SQL> START sqltprofile.sql 32263;
SQL> START sqltprofile.sql;
The custom SQL Profile created by this method is based on the plan outline data and not in scaling factors, therefore it is more steady. If you later want to drop this custom SQL Profile, you can find the drop command within the script that PROFILE generated.
If you don't have SQLT installed in the SOURCE system, or you cannot execute XTRACT or XECUTE for the SQL of concern, you can achieve the same functionality offered by the PROFILE method by using sqlt/utl/coe_xfr_sql_profile.sql instead. This script also uses DBMS_SQLTUNE; therefore a license for the Oracle Tuning pack is required.
If your system is 11g and you are considering using this PROFILE method, review the dynamic readme generated by any of the main methods and look for "Create SQL Plan Baseline from SQL Set". You may want to consider using SQL Plan Management SPM through a SQL Set as documented in the dynamic readme.
XGRAM Module
The XGRAM module provides functionality to modify CBO Histograms either to enhance CBO Statistics for some columns or as part of a Test Case. With this module you can insert, update or delete Histograms or individual Buckets.
Alphabetical list of scripts that implement the XGRAM module:
1. sqlt/utl/xgram/sqlt_delete_column_hgrm.sql
2. sqlt/utl/xgram/sqlt_delete_hgrm_bucket.sql
3. sqlt/utl/xgram/sqlt_delete_schema_hgrm.sql
4. sqlt/utl/xgram/sqlt_delete_table_hgrm.sql
5. sqlt/utl/xgram/sqlt_display_column_stats.sql
6. sqlt/utl/xgram/sqlt_insert_hgrm_bucket.sql
7. sqlt/utl/xgram/sqlt_set_bucket_size.sql
8. sqlt/utl/xgram/sqlt_set_column_hgrm.sql
9. sqlt/utl/xgram/sqlt_set_min_max_values.sql
The XGRAM module is installed automatically when SQLT is installed. If you ever need to install and use this XGRAM module outside SQLT you would only need to install one package and use the list of scripts above (removing SQLTXADMIN dependencies).
XPLORE Module
The XPLORE module helps when after a database upgrade a SQL starts to perform poorly or it may produce apparent wrong results. If switching optimizer_features_enable (OFE) to the database release prior to the upgrade the SQL performs fine again, or it produces different results, you can use this XPLORE module to try to identify which particular Optimizer feature or fix introduced the undesired behavior. Identifying the particular culprit can help to troubleshoot further or do more research on this particular feature and/or fix.
This module toggles initialization and fix control parameters to discover plans.
Use XPLORE only when ALL these conditions are met:
1.SQL performs poorly or returns wrong results while using a "bad" plan.
2.The bad plan can be reproduced on a test system (no data is preferred).
3.A "good" plan can be reproduced on the test system by switching OFE.
4.You need to narrow reason to specific parameter or bug fix control.
5.You have full access to the test system, including SYS access.
Do not use XPLORE when ANY of these conditions is true:
1. The SQL statement may cause corruption or update data.
2. There is high volume of data in tables referenced by SQL.
3. The execution of the SQL may take longer than a few seconds.
To install and use this XPLORE module, read corresponding sqlt/utl/xplore/readme.txt.
XHUME Module
This module is for the exclusive use of Oracle Support. It must be used only in an Oracle internal system. It updates the data dictionary and this operation is not supported by Oracle.
XHUME can be used to discover plans that are only possible with older version of schema object statistics related to one SQL. After a Test Case (TC) is created using SQLT, this XHUME module restores systematically prior versions of the statistics and generates plans by executing the SQL being studied. It captures the plan that each version of the statistics can generate. Then produces a report, which can be used to understand the plan instability, or to find an elusive plan that can be used to create a SQL Profile or SQL Plan Baseline.
This module should never be used in a Production system because it modifies the data dictionary. Use only on an Oracle internal Test enviornment.
As an alternative to modify the creation date of the Test Case (TC) schema objects, you can change the date on the server prior to the TC implementation, and reset to current date after the TC is created. This temporary prior date must be at least one month old, so all history of schema object statistics would have a save time newer than the TC object creation time.
Use XHUME only when ALL these conditions are met:
1.SQL is known to generate more than one plan, and one or more perform poorly.
2.Bind peeking has been ruled out as the culprit of the plan instability.
3.Changes to CBO parameters has been ruled out as the culprit of the plan instability.
4.You have a SQLT TC where a known plan is generated (a "good" or a "bad" one).
5.You need to understand the plan instability, or you are looking for an elusive known "good" plan.
6.You have full access to the Oracle internal test system, including SYS access.
Do not use XHUME when ANY of these conditions is true:
1.The SQL statement may cause corruption or update data.
2.You only have a TC on a Production environment.
3.You do not have TC created with SQLT.
4.You do not have SYS access to the Oracle internal test system that contains your TC.
5.Bind peeking or CBO parameters have not been ruled out as culprit of the plan instability.
To install and use this XHUME module, read corresponding sqlt/utl/xhume/readme.txt.
Uploading SQLT files to support
When SQLT runs, it generates a single output file (in zip format) that contains the various outputs from each section of the SQLT. This file is typically named:
sqlt_s<sqltrun#>_<method>_<sql_id>.zip
If you have executed a SQLT with a particular method then check that the name contains that method. For example the following file is from a successful SQLT run using the XTRACT method:
sqlt_s45774_xtract_fp48hh5dkm529.zip
If the only output file is a log file similar to :
sqlt_s50605_log.zip
Then it may be that the SQLT did not complete successfully and it is worth examining the log for errors.
For reference, the contents of a standard SQLT zip file should be similar to the following :
$ unzip -v sqlt_s45774_xtract_fp48hh5dkm529.zip Archive: sqlt_s45774_xtract_fp48hh5dkm529.zip Length Method Size Cmpr Date Time CRC-32 Name -------- ------ ------- ---- ---------- ----- -------- ---- 0 Stored 0 0% 04-09-2015 18:34 00000000 sqlt_s45774_10053_explain.trc 0 Stored 0 0% 04-09-2015 18:34 00000000 sqlt_s45774_10053_i1_c0_extract.trc 610 Stored 610 0% 04-09-2015 18:35 51c54175 sqlt_s45774_cell_state.zip 4187 Stored 4187 0% 04-09-2015 18:35 ded1bbdd sqlt_s45774_driver.zip 12814 Defl:N 2983 77% 04-09-2015 18:34 d359b6e7 sqlt_s45774_lite.html 18879 Stored 18879 0% 04-09-2015 18:35 8270f836 sqlt_s45774_log.zip 862169 Defl:N 121759 86% 04-09-2015 18:34 9c99af83 sqlt_s45774_main.html 3991 Stored 3991 0% 04-09-2015 18:35 d42245f2 sqlt_s45774_opatch.zip 12572 Defl:N 3645 71% 04-09-2015 18:34 2c6b2c0a sqlt_s45774_readme.html 199784 Stored 199784 0% 04-09-2015 18:35 93a85ee1 sqlt_s45774_tc.zip 394 Defl:N 293 26% 04-09-2015 18:35 a405bd8b sqlt_s45774_tc_script.sql 35 Stored 35 0% 04-09-2015 18:35 4e1901f1 sqlt_s45774_tc_sql.sql 52511 Stored 52511 0% 04-09-2015 18:35 2aee61c6 sqlt_s45774_tcx.zip 406 Stored 406 0% 04-09-2015 18:35 6df4be4d sqlt_s45774_trc.zip 216 Defl:N 113 48% 04-09-2015 18:35 213361e5 sqlt_s45774_xpand.sql 2319 Stored 2319 0% 04-09-2015 18:35 6f79e5b8 sqlt_s45774_sqldx.zip -------- ------- --- ------- 1170887 411515 65% 16 files
-- 3) 输出文件 -用这种方法生成的trace文件会被包含在SQLT输出包中，格式为：
sqlt_s94737_xtract_f995z9antmhxn.zip
3.5 通过DBMS_MONITOR进行跟踪（Doc ID 293661.1）
DBMS_MONITOR是在Oracle 10g中引入的内置的程序包。DBMS_MONITOR取代了传统的跟踪工具，例如DBMS_ SUPPORT,DBMS_SYSTEM。需要具有DBA角色才可以使用DBMS_MONITOR。
跟踪支持实例，回话，客户端标识，服务，模块和操作方式。
通过DBA_ENABLED_TRACES视图跟踪开启情况
select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES; TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS --------------------- --------------------------------------- ------------------------------- ----- ----- SERVICE_MODULE SYS$USERS SQL*Plus TRUE FALSE CLIENT_ID HUGO TRUE FALSE SERVICE v101_DGB TRUE FALSE
1. 基于会话ID和序列号设置跟踪
-- 开启会话跟踪 dbms_monitor.session_trace_enable(session_id => x, serial_num => y, waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) ); -- 关闭会话跟踪 dbms_monitor.session_trace_disable(session_id => x, serial_num => y); -- The default of waits is TRUE and the default of binds is FALSE
为了基于会话ID和序列号设置跟踪，首先确定需要跟踪的会话的SID和序列号：
select sid, serial#, username from v$session; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 68 19339 136 5419 SYS 142 4717 214 5473 SCOTT
-- 启用跟踪，可执行如下语句：
SQL> exec dbms_monitor.session_trace_enable(214,5473,TRUE,FALSE);
-- 关闭跟踪，可执行如下语句：
SQL> exec dbms_monitor.session_trace_disable(214,5473);
-- 跟踪当前的会话，可设置SID和SERIAL#为空：
SQL> exec dbms_monitor.session_trace_enable(null,null);
-- 通过v$session视图查看跟踪会话信息
SELECT sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats FROM v$session WHERE sql_trace = 'ENABLED'; select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
2. 基于客户端标识符设置跟踪
-- 开启会话跟踪 execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) ); -- 关闭会话跟踪 execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id'); -- The default of waits is TRUE and the default of binds is FALSE
-- 通过 DBMS_SESSION.SET_IDENTIFIER 函数设置客户端标识ID
SQL> execute dbms_session.set_identifier('HUGO');
通过2种方法查找确认客户端标识ID
-- 1. 当前会话 SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual; CLIENT_ID -------------- HUGO -- 2. 其它会话 SQL> select client_identifier client_id from v$session where sid = 142; CLIENT_ID -------------- HUGO OR select sid,serial#,username, client_identifier from v$session where client_identifier is not null; SID SERIAL# USERNAME CLIENT_IDENTIFIER ---------- ---------- ------------------------ ------------------ 156 3588 SCOTT bryan id
-- 确认客户端标识符后设置跟踪：
SQL> execute dbms_monitor.client_id_trace_enable('HUGO');
-- 禁用这个客户端标识符'HUGO'跟踪，可执行如下语句：
SQL> execute dbms_monitor.client_id_trace_enable ('HUGO');
3. 设置服务名/模块名/操作名的跟踪
-- 开启服务跟踪 execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' ); -- 关闭服务跟踪 execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1');
为了使用操作名，必须有对应的模块名和服务名。为了使用模块名，必须有服务名。对全局范围内针对某个数据库的服务名、模块名和操作名的给定组合启用跟踪，除非为过程指定了实例名。服务名由用于连接到服务的连接字符串确定。
服务名由初始参数文件中的SERVICE_NAMES参数指定。服务名默认为由数据库名(DB_NAME参数)和域名(DB_DOMAIN参数)组成的全局数据库名。
-- 通过 DBMS_APPLICATION_INFO.SET_X_INFO 存储过程设置transaction/client_info/module
-- 参考How to use the DBMS_APPLICATION_INFO Package (文档 ID 61722.1)
DBMS_APPLICATION_INFO程序包包含下面3个方法: SET_CLIENT_INFO ( client_info IN VARCHAR2 ); SET_ACTION ( action_name IN VARCHAR2 ); SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );
DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 ); -- 获取最近一次SET_CLIENT_INFO设置的信息 DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 ); -- 获取最近一次SET_MODULE设置的信息
Example:
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ( module_name => 'add_employee',action_name => 'insert into emp'); INSERT INTO emp ( ename, empno, sal, mgr, job, hiredate, comm, deptno ) VALUES ( 'SCOTT', 9998, 1000, 7698,'CLERK', SYSDATE,0, 10); DBMS_APPLICATION_INFO.SET_MODULE(null,null); END; /
-- 通过V$SQLAREA视图查询上面设置的模块和操作信息：
SQL> SELECT sql_text FROM v$sqlarea WHERE module = 'add_employee' AND action = 'insert into emp'; SQL_TEXT ------------------------------------------------------------------------------------------------------------------------------------------------------ INSERT INTO EMP ( ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VALUES ( 'SCOTT', 9999, 1000, 7698,'CLERK', SYSDATE,0, 10)
-- 启用服务名的跟踪，可执行如下语句：
SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );
-- 跟踪状态检查确认
SQL> select primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES where trace_type like 'SERVICE_MODULE%'; PRIMARY_ID QUALIFIER_ID1 WAITS BINDS ----------------- ------------------- ----- ----- SYS$USERS SQL*Plus TRUE FALSE
-- 关闭服务名的跟踪，可执行如下语句：
SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus');
-- 当产生多个trace文件时，可以用TRCSESS命令合并
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>
-- 启用服务名、模块名和操作名组合的跟踪，可执行如下语句：
SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'salary_update', action_name=>'insert_item');
-- 禁用服务名、模块名和操作名组合的跟踪，可执行如下语句：
SQL> exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'orcl', module_name=>'salary_update', action_name=>'insert_item');
-- 跟踪整个数据库或实例，可执行如下语句(不推荐这样操作)：
execute DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'orcl'); execute DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name => 'orcl');
注：使用DBMS_MONITOR时，请确保在完成操作时禁用跟踪；否则，将会跟踪满足指定条件的每个会话。
4. 跟踪相关视图
DBA_ENABLED_AGGREGATIONS -- 这是一个全局视图 DBA_ENABLED_TRACES V$CLIENT_STATS V$SERVICE_STATS V$SERV_MOD_ACT_STATS V$SVCMETRIC V$CLIENT_STATS -- 如果通过client_identifier来收集执行信息，可以通过这个视图查看结果
3.6 其它特定场景下打开跟踪的方法
-- 1) 跟踪指定SQL语句
alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] level 12'; -- 开启的步骤是(要把下面的awsh60c8mpfu1替换成那条SQL的SQL_ID)： alter system set events 'sql_trace [sql: awsh60c8mpfu1] level 12'; -- 而关闭的步骤是(要把下面的awsh60c8mpfu1替换成那条SQL的SQL_ID)： alter system set events 'sql_trace [sql: awsh60c8mpfu1] off';
-- 2) SQL_TRACE跟踪
Tracing Related Initialization Parameters -- 跟踪相关的初始化参数
<Parameter:TIMED_STATISTICS> -- TRUE - Enable timing or FALSE - Disable timing (Default value). <Parameter:MAX_DUMP_FILE_SIZE> -- the maximum size of trace files in operating system blocks <Parameter:USER_DUMP_DEST> -- the destination for the trace file
-- 在实例或会话层开启或关闭TIMED_STATISTICS参数
ALTER SYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE;
-- 在实例或会话层开启或关闭跟踪 ALTER SYSTEM/SESSION SET SQL_TRACE = TRUE/FALSE; -- 在11G或以上版本，sql_trace看作是事件，其设置语法如下 SQL> oradebug doc event name sql_trace sql_trace: event for sql trace Usage ------- sql_trace wait < false | true >, bind < false | true >, plan_stat < never | first_execution | all_executions | adaptive >, level <ub4> -- 启用sql_trace跟踪绑定窥探和等待事件 alter session set events 'sql_trace bind=true'; alter session set events 'sql_trace bind=true, wait=true'; -- sql_trace按照sql_id进行筛选 alter session set events 'sql_trace [sql: sql_id=g3yc1js3g2689 | sql_id=7ujay4u33g337]bind=true, wait=true'; -- 通过初始化参数开启实例级别的跟踪 <Parameter:SQL_TRACE>: Enable/Disable SQL Trace instance wide. TRUE - Enable statistics to be collected for all sessions. FALSE - Disable statistics to be collected for all sessions.
-- 3) DBMS_SUPPORT
To start tracing: exec sys.dbms_support.start_trace ; /* execute your selects to be traced */ To stop tracing: exec sys.dbms_support.stop_trace ;
-- SID=18 and Serial# =226
4) Using "dbms_system.SET_BOOL_PARAM_IN_SESSION"
To start tracing: exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE); /* execute your selects to be traced */ To stop tracing: exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);
5) Using "dbms_system.set_ev"
To start tracing: exec dbms_system.set_ev(18, 226, 10046, 12, ''); To stop tracing: exec dbms_system.set_ev(18, 226, 10046, 0, '');
6) Using "dbms_system.set_sql_trace_in_session"
To start tracing: exec dbms_system.set_sql_trace_in_session(18,226,TRUE); /* execute your selects to be traced */ To stop tracing: exec dbms_system.set_sql_trace_in_session(18,226,FALSE);
7) Use a Logon Trigger
To start tracing: create or replace trigger user_logon_trg after logon on database begin if USER = 'xxxx' then -- IF SYS_CONTEXT('USERENV','SESSION_USER') = '<USER_NM>' AND SYS_CONTEXT('USERENV','HOST') = '<HOST_NM>' THEN execute immediate 'Alter session set events ''10046 trace name context forever, level 8'''; end if; end; / /* Login a new session as User 'xxxx' and execute your selects to be traced */ To stop tracing: via LogOff Trigger (needs to be created before logging off) create or replace trigger user_logoff_trg before logoff on database begin if USER = 'xxxx' then -- IF SYS_CONTEXT('USERENV','SESSION_USER') = '<USER_NM>' AND SYS_CONTEXT('USERENV','HOST') = '<HOST_NM>' THEN execute immediate 'Alter session set events ''10046 trace name context off'''; end if; end; /
4. Trace文件解析
4.1 查找trace文件信息
-- 查找trace文件的文件名
select 'dss_ora_' || spid || '.trc' from v$process where addr = (select paddr f rom v$session where sid = 63); -- <<<< 实际中替换sid = 63
-- 查找生成trace文件所在的文件位置
select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' || p.spid || '.trc' trace_file_name from (select p.spid from sys.v$mystat m, sys.v$session s, sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, (select t.instance from sys.v$thread t, sys.v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, (select value from sys.v$parameter where name = 'user_dump_dest') d;
4.2 对生成的trace文件进行格式化
tkprof trace文件位置 生成文件位置
10046或10053生成的文件格式比较乱，直接查看有一定的困难，ORACLE自带的一个格式化命令工具tkprof可以将生成的.trc文件进行格式化，具体用说如下：
-- 查看tkprof工具的参数信息：
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option. --手动生成explain plan时，存储中间信息的临时表，默认为PROF$PLAN_TABLE
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN. --手动生成explain时，连接数据库的用户名和密码
print=integer List only the first 'integer' SQL tatements.--仅仅处理前integer数量的sql语句，如果我们需要生成脚本，该参数对脚本中包含的sql数量是不影响的
aggregate=yes|no -- 如果= NO，则不对多个相同的SQL进行汇总。如果为yes则对trace文件中的相同sql进行合并。
insert=filename List SQL statements and data inside INSERT statements.--生成脚本，该脚本会创建表，并把相关统计信息插入表，从而可以在数据库中查看
sys=no TKPROF does not list SQL statements run as user SYS. --是否包含sys用户生成的信息，如递归sql
record=filename Record non-recursive statements found in the trace file. --生成脚本，脚本包含跟踪文件中的非递归sql语句
waits=yes|no Record summary for any wait events found in the trace file.--记录等待事件信息
sort=option Set of zero or more of the following sort options: --对语句进行排序
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
-- 输出文件中列的含义：
CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT:这个语句被parse、execute、fetch的次数。
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
DISK：从磁盘上的数据文件中物理读取的块的数量。
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。