Install SQLT tool

-- UnInstall
[oracle@primary ~]$ cd sqlt/install
[oracle@primary install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 10 03:14:11 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @sqdrop.sql
-- Install
SQL> @sqcreate.sql
  adding: 130510031620_01_sqcreate.log (deflated 78%)
        zip warning: name not matched: *_ta*.log

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

PL/SQL procedure successfully completed.


Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD): @orcl


PL/SQL procedure successfully completed.


Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN:
Re-enter password:


PL/SQL procedure successfully completed.

... please wait

no rows selected


Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP


Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP

PL/SQL procedure successfully completed.


The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: HR

PL/SQL procedure successfully completed.


SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

TADOBJ completed.

PL/SQL procedure successfully completed.


SQDOLD completed. Ignore errors from this script
  adding: 130510031817_01_sqcreate.log (deflated 89%)


SQCUSR completed. Some errors are expected.

Procedure created.

No errors.
  adding: 130510032159_02_sqcusr.log (deflated 85%)

TAUTLTEST completed.
  adding: 130510032211_09_tautltest.log (deflated 60%)
. . .
SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.
-- Su dung
SQL> grant SQLT_USER_ROLE to sh;

Grant succeeded.
--
SQL> alter user sh account unlock identified by sh;

User altered.

SQL> conn sh/sh
Connected.
SQL> select count(*) from customers;

  COUNT(*)
----------
     55500

SQL> select sql_id,SQL_FULLTEXT  from v$sqlarea where sql_text like '%select count(*) from customers%';

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
d9zh8uqxmcwf5 select sql_id,SQL_FULLTEXT  from v$sqlarea where sql_text like '%select count(*)
7yurapch1qnsp select count(*) from customers

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@primary install]$ cd /home/oracle/sqlt/run/
[oracle@primary run]$ sqlplus sh/sh

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 10 03:40:40 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>  @sqltxtract 7yurapch1qnsp

PL/SQL procedure successfully completed.



Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)



Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: oracle_4U

PL/SQL procedure successfully completed.


Value passed:
SQL_ID_OR_HASH_VALUE: "7yurapch1qnsp"


PL/SQL procedure successfully completed.

***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
***   PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
*** In such case look for errors in NN_*.log files created during install.
***

SQLT_VERSION
----------------------------------------
SQLT version number: 11.4.5.7
SQLT version date  : 2013-04-05
Installation date  : 2013-05-10/03:25:09

... please wait ...

NOTE:
You used the XTRACT method connected as SH.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 7yurapch1qnsp exists in memory or in AWR.
3. You connected as the application user that issued original SQL.
4. User SH has been granted SQLT_USER_ROLE.

In case of errors ORA-03113, ORA-03114 or ORA-07445 please just
re-try this SQLT method. This tool handles some of the errors behind
a disconnect when executed a second time.

To actually diagnose the problem behind the disconnect, read ALERT
log and provide referenced traces to Support. After the root cause
of the disconnect is fixed then reset SQLT corresponding parameter.

To monitor progress, login into another session and execute:
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

... collecting diagnostics details, please wait ...

In case of a disconnect review log file in current directory

. . . .
Archive:  sqlt_s91563_xtract_7yurapch1qnsp.zip
  Length     Date   Time    Name
 --------    ----   ----    ----
   235324  05-10-13 03:45   sqlt_s91563_10053_explain.trc
    85336  05-10-13 03:45   sqlt_s91563_10053_i1_c0_extract.trc
     5370  05-10-13 03:45   sqlt_s91563_driver.zip
    13103  05-10-13 03:45   sqlt_s91563_lite.html
   888070  05-10-13 03:45   sqlt_s91563_log.zip
   768652  05-10-13 03:45   sqlt_s91563_main.html
    15750  05-10-13 03:45   sqlt_s91563_readme.html
     2310  05-10-13 03:45   sqlt_s91563_sql_detail_active.html
    62386  05-10-13 03:45   sqlt_s91563_sqldx.zip
     3133  05-10-13 03:45   sqlt_s91563_sta_report_mem.txt
    45122  05-10-13 03:45   sqlt_s91563_tcb.zip
      489  05-10-13 03:45   sqlt_s91563_tc_script.sql
       32  05-10-13 03:45   sqlt_s91563_tc_sql.sql
    34416  05-10-13 03:45   sqlt_s91563_tcx.zip
   162130  05-10-13 03:45   sqlt_s91563_tc.zip
   114433  05-10-13 03:45   sqlt_s91563_trc.zip
 --------                   -------
  2436056                   16 files

File sqlt_s91563_xtract_7yurapch1qnsp.zip for 7yurapch1qnsp has been created.

SQLTXTRACT completed.