How to Run SQLT

How to Run SQLT

Step 1: Grant SQLT_USER_ROLE to appication user
Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query
Step 3: Run sqltxtract.sql
Step 4: Find the ZIP file created by above sql
Step 5: Review the mail.html


Step 1: Grant SQLT_USER_ROLE to appication user

SQL> grant SQLT_USER_ROLE to <application_user>;


Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query

sqlplus / as sysdba
set echo on
set linesize 200 pagesize 1000
col sql_text format a50
select sid, serial#,status,sql_id,event from v$session where username='&username';
select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from test t1, test t2%';
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'TYPICAL'));
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'ADVANCED ALLSTATS LAST'));


Step 3: Run sqltxtract.sql

-- sqltxtract.sql script gather the details from memory or else from AWR snapshots.
cd sqlt/run
sqlplus ep/ep; -- connect as application user
@sqltxtract.sql sqlid sqltpassword
@/home/oracle/sqlt/run/sqltxtract.sql dkz7v96ym42c6 SQLTXPLAIN


Step 4: Find the ZIP file created by above sql

cd /home/oracle/sqlt/run
ls -ltr *sqlid*

eg: 
ls -ltr *dkz7v96ym42c6*
-rw-r--r-- 1 oracle oinstall 1842900 Oct 17 22:49 sqlt_s41093_xtract_dkz7v96ym42c6.zip

Please copy the zip file to your desktop for review.


Step 5: Review the mail.html

This mail.html file would have all the previous and current execution plans used by the sql along with timestamp when they were used.

-- click on Execution plans hyperlink under plans

Please check the execution plan currently used by the sql, by comparing the timestamp. Also identify the best execution plan for the sql.

The plan hash value for the best execution plan would have suffix [B]
Then plan hash value for the worst exection plan would have a suffix[W]

In case if you find the BEST EXECUTION PLAN, go ahead and create the custom sql profile. 

If you want to know how to create CUSTOM SQL PROFILE, please click below
How to create Custome SQL Profile

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala