Popular Posts

What is SQLT in Oracle?

Image result for query performance

SQLT is a tool or scripts (SQLTXPLAIN.SQL) devolved by Carlos Sierra (Oracle Corporation).It is using to diagnose the performance of quires by their sql_ids. It suggests the improvement areas of an sql query by inputting the sql id. DBA need minimum tuning experience to read and interprets the SQLT outputs, it is like reading AWR reports. Oracle Support engineers are using this tool to diagnose the performance issues.

You can download the SQLT from the below link (MOS)
Document 215187.1 SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly.

Follow the instruction in sqlt_instructions.html included in the zip file downloaded above to install SQLT. The following SQL can be used to check wheater SQLT is already installed or not. ( run as SYS or the SQLTXPLAIN user ) This will provide version information if it is installed.
COL sqlt_version FOR A40;
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date  : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date  : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
SQLT version number:
SQLT version date  : 2012-09-27
Installation date  : 2012-10-24/09:56:12
SQLT  required separate user SQLTXPLAIN to function and it creates a repository in the database uder SQLTCPLAN schema to record the information about the quires that are analyzed by this tool. Also it creates a number for directories on disks during the installation. For installing SQT we need SYS credentials but post installation we can manage the SQLT by SQLTXPLAIN user. You may change the password of SQLTXPLAN user anytime as a normal user. We need application user passwords to run the SQLT to retrieve the data, We may use SYS also for this but it is not recommend.
When we install SQLT it will ask for main application schema, we can pick any schema for this, it does not matter. We need to register the schema that actually run the SQL when we want to examine a SQL. So once we installed the SQLT, connect as SYS and grant the SQLT user role to the shema we run the SQL as. They use those schemas to run SQLT against the SQL. If we don’t grant the role to all the schemas and have only referenced one, SQLT will still be able to pick up the references to tables and object in the SQL that are from other schemas. All the register does is setup the runtime environment to run as that particular user.
SQLT is not designed to check all SQL statements, rather, when we have concerns about the performance of a single SQL statement then run it for that SQL.

SQLT takes a snapshot of the SQL and the environment around it, so use SQLT when you want to analyze the environment at a particular time. If SQL has intermittent performance issues then run in SQLT to collect information from the good and the bad times. If it works on one system and not another then run SQLT on both. Then compare the information and use the differences to identify where the problem lies.
SQLT requires no license and it is FREE. Oracle diagnostic /tuning pack licenses enhance the functionality of SQLT. During the installation we can specify if these packages are licensed for the site. SQLT does NOT check for a valid license and it works according to our input during the installation.
Related Documents:
  1. Doc ID 215187.1 SQLT Diagnostic Tool
  2. Document 1614107.1 SQLT Usage Instructions
  3. Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions

No comments