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

Step 2: Run sqltxtract.sql

SYNTAX: START sqltxtract.sql [SQL_ID]|[HASH_VALUE] <PASSWORD>

Step 3: Find the ZIP file created by above sql

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.