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.
|
1 2 3 |
# cd sqlt/install # sqlplus / as sysdba SQL> START sqcreate.sql |
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.
SQLTXPLAIN password
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
|
1 2 3 4 |
# cd sqlt/install # sqlplus / as sysdba SQL> START sqdefparams.sql SQL> START sqcsilent.sql |
2. Inline method
Executing sqlt/install/sqcsilent2.sql. Inputs the same 6 installation parameters but in-line.
|
1 2 3 |
# cd sqlt/install # sqlplus / as sysdba SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T |
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.
|
1 2 3 |
# cd sqlt/run # sqlplus apps SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] |
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.
|
1 2 3 |
# cd sqlt/utl # sqlplus sqltxplain SQL> START sqltprofile.sql; |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
sqlplus system/*** SQL>@coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: 5xchjlktadbpy PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 532210296 59.9 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 532210296 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "5xchjlktadbpy" PLAN_HASH_VALUE: "532210296" |
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.
|
1 2 3 |
sqlplus / as sysdba @coe_xfr_sql_profile_5xchjlktadbpy_532210296.sql |
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 : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
