SQLTXPLAIN

What is SQLTXPLAIN (SQLT)?
Ø SQLTXPLAIN is tool to enable users to analyze and tune the performance of a single SQL statement.
Ø SQLTXPLAIN, also known as SQLT. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.
Installing the Utility
1.Download the file sqlt.zip file from Note 215187.1 and unzip it to an empty local or  server directory. This will create the “sqlt” directory and a number of subdirectories. 2.In the sqlt/install directory run the script “sqcreate.sql”
Navigate to the sqlt/install directory 
$sqlplus ‘/as sysdba’ 
   Sql>START sqcreate.sql
During the installation you will be asked to enter values for these parameters:
1.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. Entering nothing is the most common setup.
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
Note: 2 users and one role are created. These users and role have fixed names
     Users:-SQLTXADMIN,SQLTXADMIN
      Role:- SQLT_USER_ROLE 
Privileges Required:–
The SQLT repository is owned by user SQLTXPLAIN. SQLT users need to provide the SQLTXPLAIN password everytime they use any of the main methods. User SQLTXPLAIN is granted the following system privileges:
    •CREATE SESSION
    •CREATE TABLE
The SQLT set of PL/SQL packages and Views is owned by user SQLTXADMIN. This SQLTXADMIN user is locked and identified by a random password. SQLTXADMIN is granted the following system privileges:
    •ADMINISTER SQL MANAGEMENT OBJECT
    •ADMINISTER SQL TUNING SET
    •ADVISOR
    •ALTER SESSION
    •ANALYZE ANY
    •SELECT ANY DICTIONARY
•SELECT_CATALOG_ROLE
If SQLTXADMIN is locked,unlock the account and reset the password and check whether the above privileges are granted/not,if not grant the above.
All SQLT users must be granted the SQLT_USER_ROLE before they can use any of the main methods. This SQLT_USER_ROLE role is granted the following system privileges:
         •ADVISOR
 •SELECT_CATALOG_ROLE
—>XTRACT Method
Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed
# 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

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.

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

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

XTRXEC executes both(XTRACT,XECUTE) 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.

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.

XTRSBY takes 3 parameters: the SQL id, the DB_LINK id, and the SQLTXPLAIN password

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.

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.

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

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
 
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


Recent Posts