Install SQLT and Query Slowness resolved over SQLT profile method

Share via:

Dear Readers,

This article will see the following Install SQLT and Query Slowness resolved over SQLT profile method.

Installing SQLT

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 (Document 215187.1)

Execute installation script sqlt/install/sqcreate.sql connected as SYS.

During the installation, you will be asked to enter values for these parameters:
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.

Provide password

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

Silent installation of SQLT:
1. In a file method
Executing first a script with pre-defined values, similar to sample script in sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql

2. Inline method
Executing sqlt/install/sqcsilent2.sql. Inputs the same 6 installation parameters but in-line.

Profile Method:
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 select, 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.
(Note: SQLT does not have to be installed in the TARGET system where the custom SQL Profile is implemented.)
There are four steps in this PROFILE method.

1.Use XTRACT or EXECUTE on the SOURCE system.

2. Execute sqlt/utl/sqltprofile.sql in SOURCE to generate a script with the custom SQL Profile.

3 . Execute the generated script in the TARGET system where the plan will be pinned.

Alternative Way to use if SQLT not installed in your system
1. Download SQLT software from this Doc Id (All About the SQLT Diagnostic Tool (Doc ID 215187.1)
2. Unzip the software and go to sqlt->utl. The script coe_xfr_sql_profile.sql can be found there.
3. Identify the SQL_ID and hash value of concerned sql.
4. Extract the sql profile by using the script coe_xfr_sql_profile.sql. Script will prompt for sql id and provide list of hash value associated with SQL_ID

5. It generate script that create sql profile in target system (coe_xfr_sql_profile_8tfrjrhtadbpy_532210296.sql)

6. Connect to the target system and run generated sql to create sql profile.



Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook :
LinkedIn :
Twitter :
YouTube :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment