SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i. When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as sysdba.
XTRACT: This method takes id of sql which is executed recently and still in memory. Using v$sql we can get the sql_id or hash_value of the sql statement and pass it as an input. SQL to run in this case is sqltxtract.sql. This method extracts sql from the memory along with runtime execution plan.
Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XECUTE. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace.
Step 1: Find the SQL ID and PLAN_HASH_VALUE for problem query
Step 2: Run sqltxtract.sql
Step 3: Find the ZIP file created by above sql
Step1: Find the SQL_ID
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';
Step 2: Run sqltxtract.sql
SYNTAX: START sqltxtract.sql [SQL_ID]|[HASH_VALUE] <PASSWORD>
SQL> START sqltxtract.sql 7yfnm5x60f3gm sqltxplain_123; PL/SQL procedure successfully completed. Parameter 1: SQL_ID or HASH_VALUE of the SQL to be extracted (required) Paremeter 2: SQLTXPLAIN password (required) PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Value passed: SQL_ID_OR_HASH_VALUE: "7yfnm5x60f3gm" PL/SQL procedure successfully completed. *** *** NOTE: *** If you get error below it means SQLTXPLAIN is not installed: *** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared. *** In such case look for errors in NN_*.log files created during install. *** SQLT_VERSION ---------------------------------------- SQLT version number: 12.1.10 SQLT version date : 2014-08-08 Installation date : 2015-06-12/04:12:56 ... please wait ... adding: alert_sexau13.log (deflated 98%) adding: alert_sexxp13.log (stored 0%) NOTE: You used the XTRACT method connected as SQLTXPLAIN. In case of a session disconnect please verify the following: 1. There are no errors in sqltxtract.log. 2. Your SQL 7yfnm5x60f3gm exists in memory or in AWR. 3. You connected as the application user that issued original SQL. 4. User SQLTXPLAIN has been granted SQLT_USER_ROLE. In case of errors ORA-03113, ORA-03114 or ORA-07445 please just re-try this SQLT method. This tool handles some of the errors behind a disconnect when executed a second time. To actually diagnose the problem behind the disconnect, read ALERT log and provide referenced traces to Support. After the root cause of the disconnect is fixed then reset SQLT corresponding parameter. To monitor progress, login into another session and execute: SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v; ... collecting diagnostics details, please wait ... In case of a disconnect review log file in current directory If running as SYS in 12c make sure to review sqlt_instructions.html first ... getting sqlt_s66620_sql_monitor_active_driver.sql out of sqlt repository ... adding: sqlt_s66620_sql_monitor_active_driver.sql (deflated 47%) ... getting sqlt_s66620_remote_driver.sql out of sqlt repository ... adding: sqlt_s66620_remote_driver.sql (deflated 48%) ... getting sqlt_s66620_main.html out of sqlt repository ... ... getting sqlt_s66620_lite.html out of sqlt repository ... ... getting sqlt_s66620_readme.html out of sqlt repository ... ... getting sqlt_s66620_readme.txt out of sqlt repository ... ... getting sqlt_s66620_metadata.sql out of sqlt repository ... ... getting sqlt_s66620_metadata1.sql out of sqlt repository ... ... getting sqlt_s66620_metadata2.sql out of sqlt repository ... ... getting sqlt_s66620_system_stats.sql out of sqlt repository ... ... getting sqlt_s66620_schema_stats.sql out of sqlt repository ... ... getting sqlt_s66620_set_cbo_env.sql out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s66620_sql_detail_active.html out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s66620_import.sh out of sqlt repository ... ... getting sqlt_s66620_export_parfile.txt out of sqlt repository ... ... getting sqlt_s66620_export_parfile2.txt out of sqlt repository ... ... getting plan.sql out of sqlt repository ... ... getting 10053.sql out of sqlt repository ... ... getting flush.sql out of sqlt repository ... SQLDX files have been created. Archive: sqlt_s66620_sqldx.zip Length Date Time Name --------- ---------- ----- ---- 706568 01-09-2018 09:19 sqlt_s66620_sqldx_7yfnm5x60f3gm_csv.zip 92796 01-09-2018 09:19 sqlt_s66620_sqldx_table_csv.zip 29963 01-09-2018 09:19 sqlt_s66620_sqldx_global_csv.zip 6287 01-09-2018 09:19 sqlt_s66620_sqldx_7yfnm5x60f3gm_log.zip --------- ------- 835614 4 files adding: sqlt_s66620_sqldx.zip (stored 0%) SQLTXTRACT completed. SQL>
Step 3: Find the ZIP file created by above sql
[oracle@bde4db03 run]$ ls -lrt *7yfnm5x60f3gm* total 53688 -rw-r--r-- 1 oracle oinstall 34667291 Jan 9 09:19 sqlt_s66620_xtract_7yfnm5x60f3gm.zip [oracle@bde4db03 run]$
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
