FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions
(Doc ID 1454160.1)
Last updated on JANUARY 23, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and laterOracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Purpose
This document answers some of the frequently asked questions about the SQLTXPLAIN (SQLT) tool. You can download the latest version of SQLT here:
<Document 215187.1> SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly
For SQL usage and installation instructions, refer to:
<Document 1614107.1> SQLT Usage Instructions
NOTE: If you want to simply perform a Health Check on your SQL environment that leaves no database footprint then see:
<Document 1366133.1> SQL Tuning Health-Check Script (SQLHC)
Ask Questions, Get Help, And Share Your Experiences With SQLT
Would you like to explore SQLT further with other Oracle Customers, Oracle Employees, and Industry Experts?
We have various threads available where you can ask questions, get help from others, and share your experiences with SQLTXPLAIN:
SQLTXPLAIN: SQLT Installation Issues
SQLTXPLAIN: Dealing with Long Execution Times
SQLTXPLAIN: Dealing with Errors reported in SQLT MAIN report
SQLTXPLAIN: Using SQLT on a Stand-by or Dataguard
SQLTXPLAIN: Interpreting and Understanding SQLT Output
SQLTXPLAIN (SQLT): General Discussion
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.
Questions and Answers
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Ask Questions, Get Help, And Share Your Experiences With SQLT |
| Questions and Answers |
| Background and Structure |
| What is SQLTXPLAIN, also known as SQLT |
| What is the origin of SQLT? |
| Is there a light weight way of gathering SQLT-like information? |
| IS SQLHC a complement or a substitute for SQLT? |
| What would you use first? SQLHC or SQLT? |
| Can SQLT be used safely in production? |
| What level of expertise is required to use SQLT? |
| Compatible Versions |
| Is the SQLT tool the same for all versions? |
| Can SQLT be used on Oracle 9i and 10gR1? |
| Licensing |
| What is the licensing requirement for SQLT? |
| How do I check in the DB whether or not tuning and diagnostic packs are installed / available based on licensing |
| What is missing from the reports if the Tuning Pack is not installed? |
| When using SQLT, is it more beneficial to have AWR than not having AWR? |
| Installation, Setup and Usage |
| Is there a Install Guide for SQLT? |
| Where can I obtain SQLT Installation Advice? |
| Where can I download SQLT? |
| How Do I install / re-install / un-install SQLT? |
| How Much space does SQLT take up? |
| How Do I upgrade SQLT? |
| How Much space does SQLT take up? |
| If SQLT is already installed, how Do I determine the version? |
| What does SQLT do during installation? |
| Can SQLT be installed into an existing schema instead of creating a new schema? |
| Do you need the SYS password to run SQLT? |
| Can I reset the password for the SQLTXPLAIN user? |
| If the Applications password is unavailable, is SYS/SYSTEM a viable alternative? |
| When database is upgraded from 10.2 to 11.2, what is SQLT upgrade path? |
| If I install SQLT on a production Db and it is later cloned, will SQLT work "out-of-the-" box on the clone? |
| Is any special setup needed to install SQLT on a server with multiple database instances? |
| How Frequently should SQLT be run? |
| Can install be scripted/automated? |
| Do we need to worry about maintenance for SQLEXPLAIN tablespace? |
| SQLT Runtime/Installation Errors |
| How do we deal with installation and runtime issues? |
| How do I generate a SQLT of a SQL statement that hangs or raises an error |
| Functionality |
| What platforms does SQLT work on? |
| Does SQLT capture All the SQL from a Database or capture specific schemas only? |
| Is SQLT able to analyze many SQLs simultaneously (e.g. from a SQL Tuning Set)? |
| If we have multiple SQLs in a session, how do we use SQLT one by one? |
| If we have multiple reports for the same query where the plan has changed, can these be compared? |
| Can we use this tool for PL/SQL code? |
| Can SQLT be used in a distributed environment? |
| Where can I find the SQL_ID or HASH_VALUE? |
| Can SQLT take the PLAN_HASH_VALUE as input? |
| Can it be used on Queries containing Multiple Tables, Views etc? |
| Can it be used where SQL references objects from multiple schemas? |
| Does SQLT work across multiple databases via a dblink? |
| Does SQLT handle literal values and bind variables? |
| How does SQLT handle bind variables? |
| Does SQLT provide formatted 10053 output? |
| Can I use previously generated trace file(s) such as 10046 or 10053 trace file(s) as an input to SQLT? |
| How do you execute the different SQLT Methods? |
| What is the difference between XTRACT, XECUTE, XTRXREC and XPLAIN? |
| What should you run first XTRACT or XECUTE? |
| If the SQL in question does not complete, is SQLT still useful? |
| Does sqlt actually execute the SQL statement or will collect info about executed statements only? |
| Is a hard parse of the analyzed SQL mandatory for the XECUTE method? Is this the purpose of the "/* ^^unique_id */" comment? |
| Using SQLT against queries that generate errors |
| Can SQLT be used in a distributed environment? |
| Is the functionality provided in SQLTXPLAIN also available in RAT (Real Application Testing)? |
| How does SQLT get historical information regarding SQL statements? |
| Does SQLT contain any graphical output? |
| Can we use SQLT to identify different output for SQL on different clients? |
| Can you set a time for when to run sqlt. For example, for the case where the query only runs slowly in the middle of the night? |
| Report Analysis |
| How do I Interpret and analyze the main report? |
| What are the main things to look for in the observations section? |
| Is information from memory 'better' than the information from the DBA_HIST_* views? |
| If the database structure has not changed, why is there more than one explain plan in SQLT? |
| How can I implement an explain plan suggested by SQLT? |
| How do I delete an existing SQL Profile created by SQLT? |
| Performance |
| Does SQLT take a lot of Database resources to execute? |
| How much memory does SQLT use? |
| Software Compatibility |
| Does SQLT work with --Insert application here-- |
| Are there any limitations on using SQLT in a Database running EBusiness Suite? |
| Does SQLT work with RAC and Exadata? |
| Is any Exadata specific information captured in report for the SQL? |
| What is the difference between SQLT and the SQL Performance Analyzer. |
| Is SQLT integrated into Oracle Enterprise Manager (OEM)/Grid Control? |
| Why Would you want to use SQLT when you have OEM? |
| SQLT Testcase Generator |
| What is the SQLT Testcase Generator? |
| Does SQLT Testcase generate user data? |
| In order to reproduce a SQLT Testcase, is the same hardware required on each server? |
| Can I have multiple system stats on a test system? |
| Do we need to take a backup before running SQLT? |
| Can you use SQLT to analyze a SQL statement just with a source system? |
| Can we use SQLT testcase to reproduce Oracle materialized view issue on a Test system? |
| Are there any special considerations when working with dictionary and fixed objects? Do they map to one schema too? |
| If we use Real Application Cluster (RAC) for our Production database, and no RAC for our Development database, then does the testcase still reproduce the SQL Plan in the Development database ? |
| Is the Objective of SQLT Testcase to only reproduce the same execution plan, not help to retrieve the best execution plan? |
| Miscellaneous |
| In SQLT Version 11.4.4.8 and below "!=" is converted into "=", potentially giving wrong information? |
| What is the CBO? |
| What is AWR? |
| In the SQLT report, why is the "Last rows" not "Actual rows"? |
| Original Shipped FAQ |
| What is new in this SQLT release? |
| How do I make a SQL statement use a plan that I can only achieve by using CBO Hints? |
| ORA-00942: table or view does not exist: Error at line 1: SELECT prev_sql_id, prev_child_number FROM sys.my_v$session |
| "ORA-07445" or "ORA-03114: not connected to ORACLE". |
| How do I analyze a distributed query? |
| XTRACT or XECUTE methods are taking too long. |
| How do I register additional SQLT users? |
| Can I change the SQLT staging directory? |
| How to suppress the export of the SQLT repository? |
| What is my SQLT tool version? |
| How to keep the SQLT repository small? |
| How can I expedite "SELECT COUNT(*)" on application Tables? |
| How can I expedite "Taking a snapshot of some Data Dictionary objects"? |
| How do I provide feedback on this SQLT tool? |
| What further information is available? |
| SQLT Webcasts |
| Discuss SQLT! |
| References |