SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. 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.
SQLT requires no license and is FREE. SQLT can use data from the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them.
But setting up SQLT on a Pluggable Database Architecture for EBS 12.2 is quite tricky. Initially when I followed the straight forward Oracle documents to setup SQLT it was failing with below errors, and I figured out the trick required to setup this correctly.Steps to Install SQLTXPLAIN / SQLT
1. Always Download the latest setup scripts file [sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip] from MOS: SQLT Usage Instructions (Doc ID 1614107.1)
2. Copy the downloaded zip file to Database server in a temporary directory and unzip the file. A directory by name 'sqlt' will be extracted that will contain the scripts to drop, install, upgrade the SQLT.
[oracle@ed-olraclin1:CDB1:~/tmp]$ ls -ltr
-rwxrwxrwx 1 oracle oinstall 948892 Sep 23 15:59 sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
[oracle@ed-olraclin1:CDB1:~/tmp]$ unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
[oracle@ed-olraclin1:CDB1:~/tmp]$ ls -ltr
drwxr-xr-x 7 oracle oinstall 4096 Jun 5 2020 sqlt
-rwxrwxrwx 1 oracle oinstall 948892 Sep 23 15:59 sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
[oracle@ed-olraclin1:CDB1:~/tmp]$ cd sqlt
[oracle@ed-olraclin1:CDB1:~/tmp/sqlt]$ ls -ltr
drwxr-xr-x 3 oracle oinstall 4096 Jun 5 2020 input
drwxr-xr-x 2 oracle oinstall 4096 Jun 5 2020 doc
drwxr-xr-x 2 oracle oinstall 4096 Jun 5 2020 run
drwxr-xr-x 7 oracle oinstall 4096 Jun 5 2020 utl
-rw-r--r-- 1 oracle oinstall 58466 Jun 5 2020 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall 39368 Jun 5 2020 sqlt_instructions.txt
drwxr-xr-x 2 oracle oinstall 4096 Sep 23 17:37 install
[oracle@ed-olraclin1:CDB1:~/tmp/sqlt]$ cd install
[oracle@ed-olraclin1:CDB1:~/tmp/sqlt/install]$ ls -ltr sqdrop.sql sqcreate.sql
-rw-r--r-- 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
-rw-r--r-- 1 oracle oinstall 4851 Aug 4 2019 sqcreate.sql
sqdrop.sql : This script will drop/unistall the existing SQLT installed in the database. It will drop the user schemas SQLTXADMIN & SQLTXPLAIN
sqcreate.sql: This script will install SQLT in the database and will create the two schemas SQLTXADMIN & SQLTXPLAIN
3. UNINSTALL/DROP SQLT from the PDB
Run the sqdrop.sql on the PDB only if earlier you have SQLT failed to install and got any errors like below,
Error 1 in log files:
-> ORA-01927: cannot REVOKE privileges you did not grant
-> SQLT$STAGE: READ,WRITE access not revoked from SQLTXPLAIN due to error above
(ignore this error)
SQLT$STAGE: READ,WRITE access granted to SQLTXADMIN
SQLT$STAGE: READ,WRITE access granted to SQLT_USER_ROLE
SQLT$STAGE: write test file tasqdirset.txt
sys.sqlt$_trca$_dir_set.open_write_close: ORA-29283: invalid file operation:
path traverses a symlink [29433]
BEGIN sqlt$_trca$_dir_set; END;
*
ERROR at line 1:
ORA-20102: SQLT$STAGE: not capable of reading or writing.
ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 314
ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 322
ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 397
ORA-06512: at line 1
Error 2 in log files:
BEGIN
*
ERROR at line 1:
ORA-20110: User "APPS" lacks required "SQLT_USER_ROLE" role or SQLT is not properly installed. >>>>>>>>>>>>>
Review installation NN_*.log files.
ORA-06512: at line 6
*
ERROR at line 1:
ORA-20110: User "APPS" lacks required "SQLT_USER_ROLE" role or SQLT is not properly installed. >>>>>>>>>>>>>
Review installation NN_*.log files.
ORA-06512: at line 6
==>> So first lets connect to PDB and uninstall/drop SQLT from the PDB that was failed to be installed earlier or you just want to do a fresh install and want to drop the SQLT repository if already available on the PDB database.
SQL> ALTER SESSION SET CONTAINER=MYPDB;
Session altered.
SQL> show con_id
CON_ID
4
SQL> show pdbs
CON_ID CON_NAME OPEN_MODE RESTRICTED
4 MYPDB READ WRITE NO
SQL> START sqdrop.sql
sqdrop.sql script output:
... uninstalling SQLT, please wait
TADOBJ completed.
SQDOLD completed. Ignore errors from this script
SQDOBJ completed. Ignore errors from this script
SQL>
SQL> DECLARE
2 my_count INTEGER;
3
4 BEGIN
5 SELECT COUNT(*)
6 INTO my_count
7 FROM sys.dba_users
8 WHERE username = 'TRCADMIN';
9
10 IF my_count = 0 THEN
11 BEGIN
12 EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
13 EXCEPTION
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
16 END;
17
18 FOR i IN (SELECT directory_name
19 FROM sys.dba_directories
20 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
21 LOOP
22 BEGIN
23 EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
24 DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
28 END;
29 END LOOP;
30 END IF;
31 END;
32 /
Dropped directory SQLT$STAGE.
PL/SQL procedure successfully completed.
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.
SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old 1: DROP USER &&tool_administer_schema. CASCADE
new 1: DROP USER SQLTXADMIN CASCADE
User dropped.
SQL> DROP USER &&tool_repository_schema. CASCADE;
old 1: DROP USER &&tool_repository_schema. CASCADE
new 1: DROP USER SQLTXPLAIN CASCADE
User dropped.
SQL> DROP ROLE &&role_name.;
old 1: DROP ROLE &&role_name.
new 1: DROP ROLE SQLT_USER_ROLE
Role dropped.
SQL>
SQL> SET ECHO OFF;
SQDUSR completed.
SQDROP completed.
4. Take the TNSENTRY from EBS application tier node and update the Database server tnsnames.ora file on both RAC nodes.
[oracle@ed-olraclin01:CDB1:~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
MYPDB1=
(DESCRIPTION_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ed-olraclin01.oracle.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ebs_MYPDB)
(INSTANCE_NAME=CDB1)
)
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ed-olraclin2.oracle.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ebs_MYPDB)
(INSTANCE_NAME=CDB2)
)
)
)
5. It's must to check connectivity of SYS user from both RAC nodes using this new TNSENTRY.
[oracle@ed-olraclin1:CDB1:~]$ sqlplus sys@MYPDB1 as sysdba
Enter password:
Last Successful login time: Thu Sep 23 2021 16:46:50 +12:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL>
6. If the connection is not working then update the password file on each RAC node using orapwd utility
$ cd $ORACLE_HOME/dbs
$ orapwd password='pwd_here_in_single_quote' entries=5 file=orapwCDB1
$ orapwd password='pwd_here_in_single_quote' entries=5 file=orapwCDB2
7. Modify the SYS password from CDB database for all the containers
SQL> show con_id
con_id
1
SQL> ALTER USER SYS IDENTIFIED BY "pwd_here_in_double_quote" CONTAINER=ALL;
user altered.
9. Note that we cannot run sqcreate.sql script successfully if the diagnostic_dest path is as symbolic link and the script will thrown this error [path traverses a symlink [29433]] in the log file as detailed in error 1 above. This is a drawback of Oracle that, if the directory is a symbolic link to ACFS mount then the script will give errors.
SQL> show parameter diagnostic_dest
NAME TYPE VALUE
diagnostic_dest string /custom_diag/mypdb
[oracle@ed-olraclin1:CDB1:/]$ ls -ltr /
lrwxrwxrwx 1 root root 22 Feb 2021 custom_diag -> /acfsnpevol/custom_diag
SQL> SELECT name, value FROM v$parameter2 WHERE name LIKE '%dump_dest';
NAME VALUE
background_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log
user_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log
core_dump_dest /custom_diag/cdb/diag/rdbms/cdb/CDB1/cdump
SQL> SELECT directory_name||' '||directory_path directories FROM sys.dba_directories WHERE directory_name LIKE 'SQLT$%' OR directory_name LIKE 'TRCA$%' ORDER BY 1;
no rows selected
10. Temporarily we will modify the diagnostic_dest parameter for both instances to a non-symbolic link directory in ORACLE_BASE, and once the SQLT installation completes successfully we will re-set the value back to the symbolic link ACFS directory.
SQL> ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle' SCOPE=both SID='*';
Verify if the alert log and the trace log files are getting generated in the updated NON-ACFS directory.
SQL> SHOW PARAMETER DIAGNOSTIC_DEST
NAME TYPE VALUE
diagnostic_dest string /u01/app/oracle
You will see that the trace files and alert log file gets generated in $ORACLE_BASE directory
$ cd $ORACLE_BASE/diag/rdbms/cdb/CDB1/trace
11. Now install SQLT from DB node 1 connecting as SYS user
$ cd $ORACLE_HOME
$ . MYPDB_ed-olraclin1.env
$ . CDB1_ed-olraclin1.env
$ echo $TNS_ADMIN/
$ cd /tmp/sqlt/install
$ sqlplus sys@MYPDB1 as sysdba
SQL> START sqcreate.sql
Optional Connect Identifier (ie: @PROD): @MYPDB1
Password for user SQLTXPLAIN: <pwd_here>
Re-enter password: <pwd_here>
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES
Default tablespace [UNKNOWN]: USERS
Temporary tablespace [UNKNOWN]: TEMP1
Main application user of SQLT: APPS
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: T
Last two lines from Long output,
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SQL>
12. Now revert back the diagnostic_dest parameter value to use ACFS mount,
SQL> ALTER SYSTEM SET diagnostic_dest='/custom_diag/mypdb' SCOPE=both SID='*';
SQL> select name,value from v$diag_info ;
NAME VALUE
Diag Enabled TRUE
ADR Base /custom_diag/mypdb
ADR Home /custom_diag/mypdb/diag/rdbms/cdb/cdb2
Diag Trace /custom_diag/mypdb/diag/rdbms/cdb/cdb2/trace
Diag Alert /custom_diag/mypdb/diag/rdbms/cdb/cdb2/alert
Diag Incident /custom_diag/mypdb/diag/rdbms/cdb/cdb2/incident
Diag Cdump /custom_diag/mypdb/diag/rdbms/cdb/cdb2/cdump
Health Monitor /custom_diag/mypdb/diag/rdbms/cdb/cdb2/hm
Default Trace File /custom_diag/mypdb/diag/rdbms/cdb/cdb2/trace/cdb2_ora_220278.trc
Active Problem Count 0
Active Incident Count 0
ORACLE_HOME /u01/app/oracle/product/19.11/db_cdb_home
SQL> SELECT directory_name||' '||directory_path directories FROM sys.dba_directories WHERE directory_name LIKE 'SQLT$%' OR directory_name LIKE 'TRCA$%' ORDER BY 1;
directory_name||''||direcotry_path
SQLT$BDUMP /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
SQLT$DIAG /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
SQLT$STAGE /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
SQLT$UDUMP /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
TRCA$INPUT1 /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
TRCA$INPUT2 /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
TRCA$STAGE /u01/app/oracle/diag/rdbms/cdb/cdb2/trace
SQL> SELECT name, value FROM v$parameter2 WHERE name LIKE '%dump_dest';
NAME VALUE
background_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log
core_dump_dest /custom_diag/cdb/diag/rdbms/cdb/CDB2/cdump
user_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log
13. Now generate the report for SQL_ID from EBS Application server.
Note: While generating the sqltxtract output again the value of diagnostic_dest should not be pointing to any symbolic link to ACFS storage. Always update the diagnostic_dest parameter value as shown above to a non-symbolic link , generate the below diagnostic report and then revert back the parameter value.
$ cd /tmp/sqlt/run
$ sqlplus APPS/<pwd_here>
SQL> START sqltxtract.sql 417b6c4a1bm8r
To monitor the progress of the above script you can run the below statement in another session as apps user.
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;
Above sqltxtract.sql script will generate sqlt_s23339_xtract_417b6c4a1bm8r.zip output file that will be used for diagnosing the slowness of that SQL_ID.
References:
SQLT Usage Instructions (Doc ID 1614107.1)
Troubleshooting SQLT Issues Community Thread (Doc ID 1521607.1)
FAQ: Common SQLT (SQLTXPLAIN) Runtime/Installation Errors (Doc ID 1670677.1)
SQLT installation fails. No error in the log file or during execution of sqcreate.sql script. (Doc ID 2339926.1)
All About the SQLT Diagnostic Tool (Doc ID 215187.1)
