Shulou(Shulou.com)06/01 Report--

SQLT overview safe mode installation SQLT uninstall SQLT upgrade SQLT FAQ upload SQLT file to Oracle technical support

Main method XTRACTXECUTEXTRXECXPLAINXTRSBYXPREXTXPREXC

Special method COMPARETRCANLZRTRCAXTRTRCASPLITXTRSET

Overview of advanced methods and modules PROFILEXGRAMXPLOREXHUMESQLT

SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise-ST CoE. The main method of SQLT is to output a set of diagnostic files by inputting a SQL statement. These files are often used to diagnose SQL statements that perform poorly or produce incorrect results.

For tuning sql statements, SQLT requires you to have some professional knowledge to analyze. For many problems, we recommend using SQL Health Check to check your SQL first, and then try using SQLT if it can't be solved. For a detailed introduction of SQLHC, please see:

Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)

Once installed, you can parse the SQL statement by passing to SQLT a text file containing the SQL statement script (including binding variables) or its SQL_ID. SQL_ID can be found in AWR and ASH reports, and HASH_VALUE appears in the output of SQL_TRACE (identified by the "hv=" flag at the top of the SQL text). You can also find these fields in the V$SQL view. Please refer to the following documentation:

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement

The SQLT primary method connects to the database and collects execution plans, cost-based Optimizer CBO statistics, Schema object metadata, performance statistics, configuration parameters, and other elements that affect the SQL performance being analyzed. These methods produce a package of output to the problematic SQL_ID, including a "main" report in html format. For information about how to use the main report, you can refer to the following documentation:

Document 1922234.1 SQLT Main Report: Usage Suggestions

SQLT can use Oracle Diagnostic and / or Oracle Tuning Packs, as long as your database requires a license for both packages. These two packages provide enhanced functionality to the SQLT tool. During SQLT installation, you can specify whether one of the packages is licensed in your database. If none, SQLT still provides some basic information for the initial SQL performance diagnostics.

Safety mode

SQLT creates two users and a role during installation. The names of these users and roles are fixed.

SQLT repository is managed by the user SQLTXPLAIN. Users of SQLT provide the password of SQLTXPLAIN every time they use the main method provided by SQLT. The SQLTXPLAIN user is granted the following system permissions:

CREATE SESSIONCREATE TABLE

The PL/SQL packages and views contained in SQLT are managed by the user SQLTXADMIN. The SQLTXADMIN user is locked out and protected by a randomly generated password. The SQLTXADMIN user is granted the following system permissions:

ADMINISTER SQL MANAGEMENT OBJECTADMINISTER SQL TUNING SETADVISORALTER SESSIONANALYZE ANYSELECT ANY DICTIONARYSELECT_CATALOG_ROLE

All users of SQLT must be given the role of SQLT_USER_ROLE before using the main methods provided by SQLT. The SQLT_USER_ROLE role is granted the following system permissions:

ADVISORSELECT_CATALOG_ROLE

12c by default, SYS users cannot be users of SQLT because of changes in the PL/SQL security model.

To handle this change, SQLTADMIN needs to be granted INHERIT PRIVILEGES permission on SYS.

GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN

For more details, see Oracle? Database PL/SQL Language Reference 12c Release 1 (12.1)-Invoker's Rights and Definer's Rights (AUTHID Property) and in Oracle? Database Security Guide 12c Release 1 (12.1)-Managing Security for Definer's Rights and Invoker's Rights

Install SQLT

SQLT is installed under its own schema SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema. You can install this version of SQLT on UNIX, Linux, or Windows platforms, Oracle DB 10.2,11.1,11.2 and later. For all "apply to:" information, please refer to document 215187.1.

Installation steps:

Uninstall the previous version (optional).

This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh installation. If you want to retain the existing content of SQLT Repository, skip this step (recommended).

# cd sqlt/install# sqlplus / as sysdbaSQL > START sqdrop.sql connects to the database as SYS and executes the installation script sqlt/install/sqcreate.sql. # cd sqlt/install# sqlplus / as sysdbaSQL > START sqcreate.sql

During installation, you will be asked to enter the following parameter values:

Optional connection identifier (required when installed on a pluggable database)

In some systems with restricted access, you may need to specify a connection identifier, such as @ PROD. If you do not need a connection identifier, do not enter any data, just click enter. Typing nothing is the most commonly used installation method.

The connection identifier must be provided when installed on a pluggable database.

SQLTXPLAIN password.

Case-sensitive in most systems.

SQLTXPLAIN defaults to tablespaces.

From the list of available permanent tablespaces, select the tablespaces that should be used by SQLT Repository's SQLTXPLAIN. It must have more than 50MB free space.

SQLTXPLAIN temporary tablespace.

From the list of available temporary tablespaces, select the tablespaces that SQLTXPLAIN temporary operations and temporary objects should use.

Optional application user.

This is the user who issued the SQL statement to parse. For example, on EBS systems, specify as APPS; on Siebel, specify as SIEBEL; on People Soft, and specify as SYSADM. You will not be asked to enter this user's password. You can also add other SQLT users after installing the tool by granting them the role SQLT_USER_ROLE.

Authorized Oracle Pack. (Tencent D or N)

You can specify T for Oracle Tuning; D for Oracle Diagnostic, or N for none. If you select T or DMagSQLT, you can include the authorized content in the diagnostic file it generates. The default value is T. If you choose NQuery SQLT, only limited features will be installed.

If you need a silent installation, you can use the following three options to pass all six installation parameters:

It's in the file.

First, a script is used to predefine the value, similar to the sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql instead of sqlt/install/sqcreate.sql.

# cd sqlt/install# sqlplus / as sysdbaSQL > START sqdefparams.sqlSQL > START sqcsilent.sql command line.

Execute sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs 6 installation parameters in embedded form.

# cd sqlt/install# sqlplus / as sysdbaSQL > START sqcsilent2.sql''sqltxplain USERS TEMP''T is installed inside Oracle.

Execute sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former first executes sqlt/install/sqdefparams.sql, then sqlt/install/sqcsilent.sql.

# cd sqlt/install# sqlplus / as sysdbaSQL > START sqcinternal.sql

If you need more help on installation issues, you can get help from the following forum: SQLTXPLAIN: SQLT Installation Issues

Uninstall SQLT

Uninstalling SQLT removes SQLT Repository and all SQLTXPLAIN/SQLTXADMIN schema objects at the same time. SQLTXPLAIN and SQLTXADMIN users are also deleted. To uninstall SQLT, simply connect as SYS and execute sqlt/install/sqdrop.sql.

# cd sqlt/install# sqlplus / as sysdbaSQL > START sqdrop.sql upgrade SQLT

If you have a previous version of SQLT installed on your system, you can upgrade SQLT to its latest version while partially retaining some objects from existing SQLT Repository. You can then use the newly migrated SQLT Repository to recover CBO statistics or perform COMPARE between the old and new executors of SQLT.

To upgrade SQLT, you only need to perform the installation instead of the optional uninstall steps.

If the upgrade fails, it may be that the previous version of SQLT is too old to upgrade. In this case, continue to uninstall SQLT before performing a fresh installation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.