In this DocumentPurpose Ask Questions, Get Help, And Share Your Experiences With SQLTQuestions 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? Which Schema should I register as the application scema? For example we have 3 schemas in the DB with cross permissions - Schema A is the main schema but uses objects from B and C. How does that work with SQLT? 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 My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle Sql Tuning With Oracle Sqltxplain
Oracle SQL Tuning with SQLTXPLAIN is a practical guide to SQL tuning the way Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. Using this simple tool you'll learn how to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole.
A SQL tuning set (STS) is a database object that stores SQL statements along with their execution context. When a set of SQL statements serve as input, the database must first construct and use an STS.
SQL Access Advisor performs best with a workload based on actual usage. You can store multiple workloads in the form of SQL tuning sets, so that you can view the different uses of a real-world data warehousing or OLTP environment over a long period and across the life cycle of database instance startup and shutdown.
2ff7e9595c
Comments