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
SQL> grant SQLT_USER_ROLE to <application_user>;
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'));
-- 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
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.
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.