How to Collect Standard Diagnostic Information Using SQLT for SQL Issues
Gather Diagnostics with SQLT
In order to gather SQLT output for the query, you will need to download and install SQLT.
Download SQLT
To do this, download and run the SQLT tool. You can download SQLT here.
Install SQLT
Install SQL by executing the installation script sqlt/install/sqcreate.sql connected as SYS:
# sqlplus / as sysdba
SQL> START sqcreate.sql
During the installation you will be asked to enter values for these parameters:
- Optional Connect Identifier - 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.
- SQLTXPLAIN password - Case sensitive in most systems.
- 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.
- SQLTXPLAIN Temporary Tablespace - Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.
- 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 or by using provided script sqlt/install/sqguser.sql
- 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.
Collect Diagnostics Using SQLT
Now that SQLT is installed, the optimal set of data that we need is information from SQLT in XTRXEC Method (executes both XTRACT and XECUTE methods serially and executes the query in question). XTRXEC mode picks the most expensive plan and collects information based on that. In most cases this should collect the majority of information to satisfy steps 2(a)->(e) in the checklist.
When XTRXEC runs, it starts with an XTRACT phase which generates a script containing the extracted SQL together with the binds declarations and assignments for an expensive plan found for the requested SQL statement. XTRXEC then executes the XECUTE phase using the script created by the XTRACT phase.
The major drawback of XTRXEC mode is that if the SQL being analyzed takes long to execute (within the XECUTE mode executed in the second phase), this method will also take a long time.
As a rule of thumb, use this method only if the SQL takes less than 1hr to execute, else use XTRACT.
Collect SQLT in XTRXEC mode when the query is running poorly
XTRXEC method Setup
To use the XTRXEC method, be sure SQLT has been installed first.
To use this method, you need to know the SQL_ID or the HASH_VALUE of the SQL to be analyzed. If you do not know these use the XECUTE which accepts a SQL script as input. 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). You can also find he SQL_ID in the V$SQL view using a select similar to:
from V$SQL
where SQL_TEXT like '%&Some_Identifiable_String%';
The XTRACT phase produces a report based upon SQL that is found in memory. If the SQL is still in memory, or it has been captured by AWR, then XTRACT finds it and provides a set of diagnostics files. Otherwise XTRACT will fail with an error. It 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.
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. You can execute the grant directly, or you can execute sqlt/install/sqguser.sql connected as SYS.
XTRXEC method execution
Once you have satisfied the setup requirements, collect in XTRXEC mode using the following instructions:
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. The following shows different commands to run XTRXEC using these inputs:
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx
SQL> START sqltxtrxec.sql 2524255098
Alternative exception Collections
Collect SQLT in XTRACT mode
If you are not able to collect SQLT in XTRXEC mode (due to time or other constraints) then please collect in XTRACT mode when the query is running poorly and also well (if possible) for comparison. XTRACT produces a report based upon SQL that is found in memory. If the SQL is still in memory, or it has been captured by AWR, then XTRACT finds it and provides a set of diagnostics files. Otherwise XTRACT will fail with an error.
To use this method, you need to know the SQL_ID or the HASH_VALUE of the SQL to be analyzed. If you do not know these use the XECUTE which accepts a SQL script as input. 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). You can also find he SQL_ID in the V$SQL view using a select similar to:
from V$SQL
where SQL_TEXT like '%&Some_Identifiable_String%';
Collect SQLT in XECUTE mode
If you are not able to collect SQLT in either XTRXEC or XTRACT mode (due to time or other constraints) then please collect in XECUTE mode which accepts a SQL script as input.
XECUTE method Setup
To use the XECUTE method, be sure SQLT has been installed first.
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 is highly recommended.
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. You can execute the grant directly, or you can execute sqlt/install/sqguser.sql connected as SYS.
XECUTE method execution
Once you have satisfied the setup requirements, collect in XECUTE mode using the following instructions:
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.
# sqlplus apps
SQL> START [path]sqltxecute.sql [path]scriptname
SQL> START run/sqltxecute.sql input/sample/script1.sql
Comments
Post a Comment