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;
SELECT
'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
FROM
DUAL
/
SQLT_VERSION
----------------------------------------
SQLT
version number: 11.4.4.8
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 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:
- Doc ID 215187.1 SQLT Diagnostic Tool
- Document 1614107.1 SQLT Usage Instructions
- Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions
No comments:
Post a Comment