Oracle SQLTXPLAIN (SQLT) Tips And Tricks

SQLTXPLAIN :-

  • SQLT is a set of packages and scripts that produces HTML-formatted reports, some SQL scripts and some text files.
  • SQLTXPLAIN is tool to enable users to analyze and tune the performance of a single SQL statement.
  • The entire collection of information is packaged in a zip file and often sent to Oracle Support, but you can look at these files yourself. There are just over a dozen packages and procedures (called “methods”) in SQLT. These packages and procedures collect different information based on your circumstances.
  • These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

Main steps :

  1. Download SQLT
  2. Install SQLT
  3. Run SQLT report

Download SQLT tool :

Install SQLT tool :

  • Unzip the zip file to suitable location.
[oracle@orcl:~ orcldemo] unzip sqlt_10g_11g_12c_25_08_2018.zip
  • Connect as SYS user and ensure database is running
[oracle@orcl:install orcldemo] cd /home/oracle/sqlt/install
[oracle@orcl:install orcldemo] sqlplus / as sysdba
  • Run the sqcreate.sql script
  • Enter the connect identifier for the database
  • Enter and confirm the password for SQLTXPLAIN user
  • Select the tablespace  and temp tablespace where the SQLTXPLAIN to keep its packages and data
  • Enter the username of the user in the database who will use SQLT packages to fix tuning problems. For us schema HARI that runs the problematic SQL,
  • Enter “T”, “D” or “N.” This reflects your license level for the tuning and diagnostics packs
SQL> START sqcreate.sql
zip warning: Local Entry Flag does not match CD: 180825155053_00_sqdrop.log
adding: 181027005437_01_sqcreate.log (deflated 85%)

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old 1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new 1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE
Session altered.
Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.
Optional Connect Identifier (ie: @PROD): @orcldemo

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN:
Re-enter password:

PL/SQL procedure successfully completed.

The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES

... please wait

TABLESPACE   FREE_SPACE_MB
------------ -------------
T1           135
T2           135
T3           135
T4           199
NEW          299
USERS        32745

6 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: HARI
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

At last you will see the below message,

SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
Some of the key steps performed at this stage include;
  1.  Installing required packages to support SQLT.
  2. Create SQLT schema objects.
  3. Migrating relevant objects from old to new repository.
  4. Taking snapshots of some existing data dictionary objects.
Run the SQLT report :
  • Execute the below SQL statement and Get the SQL_ID
SQL> conn hari/hari;
Connected.
SQL> select count(*) from user_objects;

COUNT(*)
----------
10

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

SQL_ID
-------------
8x615vyks733p

SQLTXPLAIN Methods :-

SQLT provides 7 main methods that generate diagnostics details for one SQL statement: XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT and XPREXC.

Mainly used methods in SQLT are:-

Select appropriate method to diagnose the SQL statement.

  • XTRACT when SQL_ID is available
  • XECUTE when detailed execution metrics are desired
  • XTRXEC to get everything from XTRACT and XECUTE
  • XPLAIN when XTRACT and XECUTE are not feasible
  • XTRSBY when SQL executed on a read-only database

We must provide SQLT_USER_ROLE for user to run SQLT methods

  • Grant SQLT_USER_ROLE after SQLT installation
  • Optionally use sqlt/install/sqguser.sql

XTRACT :-

Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XECUTE method. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace.

  • Run the SQLT report from the HARI or respective user.
[oracle@orcl:~ orcldemo] cd sqlt/run/

[oracle@orcl:run orcldemo] sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 1 02:19:10 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn hari/hari;

SQL> @sqltxtract.sql 8x615vyks733p

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$123

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed:
SQL_ID_OR_HASH_VALUE: "8x615vyks733p"

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.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 90%)

NOTE:
You used the XTRACT method connected as HARI.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 8x615vyks733p exists in memory or in AWR.
3. You connected as the application user that issued original SQL.
4. User HARI 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
If running as SYS in 12c make sure to review sqlt_instructions.html first
... getting sqlt_s81018_sql_monitor_active_driver.sql out of sqlt repository ...
adding: sqlt_s81018_sql_monitor_active_driver.sql (deflated 47%)
... getting sqlt_s81018_remote_driver.sql out of sqlt repository ...
adding: sqlt_s81018_remote_driver.sql (deflated 47%)
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_perfhub_driver.sql out of sqlt repository ...
... generating sqlt_s81018_perfhub_0001__.html ...
adding: sqlt_s81018_perfhub_driver.sql (deflated 52%)
... getting sqlt_s81018_main.html out of sqlt repository ...
... getting sqlt_s81018_lite.html out of sqlt repository ...
... getting sqlt_s81018_readme.html out of sqlt repository ...
... getting sqlt_s81018_readme.txt out of sqlt repository ...
... getting sqlt_s81018_metadata.sql out of sqlt repository ...
... getting sqlt_s81018_metadata1.sql out of sqlt repository ...
... getting sqlt_s81018_metadata2.sql out of sqlt repository ...
... getting sqlt_s81018_system_stats.sql out of sqlt repository ...
... getting sqlt_s81018_schema_stats.sql out of sqlt repository ...
... getting sqlt_s81018_set_cbo_env.sql out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_sql_detail_active.html out of sqlt repository ...
... getting sqlt_s81018_10053_explain.trc out of sqlt repository ...
... getting sqlt_s81018_10053_i1_c0_extract.trc out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_import.sh out of sqlt repository ...
... getting sqlt_s81018_export_parfile.txt out of sqlt repository ...
... getting sqlt_s81018_export_parfile2.txt out of sqlt repository ...
... getting plan.sql out of sqlt repository ...
... getting 10053.sql out of sqlt repository ...
... getting flush.sql out of sqlt repository ...
... getting sqlt_s81018_purge.sql out of sqlt repository ...
... getting sqlt_s81018_restore.sql out of sqlt repository ...
... getting sqlt_s81018_del_hgrm.sql out of sqlt repository ...
... getting tc.sql out of sqlt repository ...
... getting xpress.sh out of sqlt repository ...
... getting xpress.sql out of sqlt repository ...
... getting setup.sql out of sqlt repository ...
... getting readme.txt out of sqlt repository ...
... getting tc_pkg.sql out of sqlt repository ...
... getting sel.sql out of sqlt repository ...
... getting sel_aux.sql out of sqlt repository ...
... getting install.sh out of sqlt repository ...
... getting install.sql out of sqlt repository ...
... getting pack_tcx.sql out of sqlt repository ...
... getting sqlt_s81018_awrrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_awrrpt_driver.sql (deflated 47%)
... getting sqlt_s81018_addmrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_addmrpt_driver.sql (deflated 46%)
... getting sqlt_s81018_ashrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_ashrpt_driver.sql (deflated 57%)
... getting sqlt_s81018_tcb_driver.sql out of sqlt repository ...
zip warning: name not matched: /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/README.txt
adding: sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_tcb_README.txt (deflated 57%)
adding: sqlt_s81018_tcb_dpexp.dmp (deflated 91%)
adding: sqlt_s81018_tcb_dpexp.log (deflated 70%)
adding: sqlt_s81018_tcb_dpexp.sql (deflated 72%)
adding: sqlt_s81018_tcb_dpimp.sql (deflated 67%)
adding: sqlt_s81018_tcb_main.xml (deflated 78%)
adding: sqlt_s81018_tcb_ol.xml (deflated 88%)
adding: sqlt_s81018_tcb_prmimp.sql (deflated 55%)
adding: sqlt_s81018_tcb_smrpt.html (deflated 48%)
adding: sqlt_s81018_tcb_sql.xml (deflated 26%)
adding: sqlt_s81018_tcb_ssimp.sql (deflated 66%)
adding: sqlt_s81018_tcb_ts.xml (deflated 32%)
adding: sqlt_s81018_tcb_xpl.txt (deflated 81%)
adding: sqlt_s81018_tcb_xplf.sql (deflated 52%)
adding: sqlt_s81018_tcb_xplo.sql (deflated 73%)
adding: sqlt_s81018_tcb_xpls.sql (deflated 55%)
adding: sqlt_s81018_tcb_driver.sql (deflated 53%)
... getting sqlt_s81018_xpand_sql_driver.sql out of sqlt repository ...
... getting sqlt_s81018_export_driver.sql out of sqlt repository ...

*******************************************************************
* Enter SQLTXPLAIN valid password to export SQLT repository *
* Notes: *
* 1. If you entered an incorrect password you will have to enter *
* now both USER and PASSWORD. The latter is case sensitive *
* 2. User is SQLTXPLAIN and not your application user. *
*******************************************************************

zip error: Nothing to do! (sqlt_s81018_tc.zip)
adding: sqlt_s81018_import.sh (deflated 35%)
zip warning: name not matched: sqlt_s81018_exp2.dmp

zip error: Nothing to do! (sqlt_s81018_tcx.zip)
adding: sqlt_s81018_exp.log (deflated 7%)
adding: sqlt_s81018_exp2.log (deflated 7%)
adding: sqlt_s81018_export_driver.sql (deflated 67%)
adding: sqlt_s81018_export_parfile.txt (deflated 73%)
adding: sqlt_s81018_export_parfile2.txt (deflated 34%)
... getting sqlt_s81018_tc_sql.sql out of sqlt repository ...
... getting q.sql out of sqlt repository ...
... getting sqlt_s81018_tc_script.sql out of sqlt repository ...
### tkprof commands below may error out with "could not open trace file". disregard error.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:18 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:21 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

### copy command below will error out on linux and unix. disregard error.
/bin/bash: copy: command not found
### tkprof commands below may error out with "could not open trace file". disregard error.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
could not open trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/S81018_SQLT_TRACE.trc
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
could not open trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/S81018_SQLT_TRACE.trc
### tkprof commands below may error out with "could not open trace file". disregard error.
cat:
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.


cat:
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.


*_ora_*_S81018_SQLT_TRACE*.trc: No such file or directory
*_ora_*_S81018_SQLT_TRACE*.trc: No such file or directory
adding: sqlt_s81018_sqlt_tkprof_nosort.txt (deflated 91%)
adding: sqlt_s81018_sqlt_tkprof_sort.txt (deflated 90%)
adding: sqlt_s81018_sqlt_tkprof_tnosort.txt (deflated 61%)
adding: sqlt_s81018_sqlt_tkprof_tsort.txt (deflated 61%)
updating: alert_orcldemo.log (deflated 90%)
adding: spfileorcldemo.ora (deflated 80%)
adding: opatch2018-05-24_23-47-33PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-11PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-15PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-18PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-50-30PM_1.log (deflated 72%)
adding: opatch2018-05-25_00-25-44AM_1.log (deflated 72%)
adding: opatch2018-05-25_01-56-29AM_1.log (deflated 72%)
adding: opatch2018-05-25_02-44-45AM_1.log (deflated 72%)
adding: opatch2018-05-25_04-58-05AM_1.log (deflated 72%)
adding: opatch2018-05-25_22-00-14PM_1.log (deflated 72%)
adding: opatch2018-05-25_22-02-46PM_1.log (deflated 72%)
adding: opatch2018-05-25_22-02-50PM_1.log (deflated 72%)
adding: opatch2018-06-04_06-32-54AM_1.log (deflated 72%)
adding: opatch2018-06-06_09-56-59AM_1.log (deflated 72%)
adding: opatch2018-06-07_18-49-37PM_1.log (deflated 72%)
adding: opatch2018-06-07_20-27-51PM_1.log (deflated 72%)
adding: opatch2018-06-07_22-54-04PM_1.log (deflated 72%)
adding: opatch2018-06-08_02-27-30AM_1.log (deflated 72%)
adding: opatch2018-06-08_10-24-45AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-15-19AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-02AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-15AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-28AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-19-03AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-43-04AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-44-01AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-49-05AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-49-11AM_1.log (deflated 72%)
adding: opatch2018-09-18_21-16-12PM_1.log (deflated 72%)
adding: opatch2018-09-21_18-50-48PM_1.log (deflated 72%)
adding: opatch2018-09-22_00-27-46AM_1.log (deflated 74%)
adding: opatch2018-09-22_00-58-49AM_1.log (deflated 74%)
adding: opatch2018-09-22_01-25-35AM_1.log (deflated 86%)
adding: opatch2018-09-22_01-26-15AM_1.log (deflated 96%)
adding: opatch2018-09-22_01-50-06AM_1.log (deflated 72%)
adding: opatch2018-09-22_01-51-07AM_1.log (deflated 71%)
adding: opatch2018-10-24_22-33-26PM_1.log (deflated 72%)
adding: opatch_history.txt (deflated 94%)
### chmod command below will error out on windows. disregard error.
adding: sqlt_s81018_system_stats.sql (deflated 48%)
adding: sqlt_s81018_set_cbo_env.sql (deflated 78%)
adding: sqlt_s81018_metadata1.sql (deflated 65%)
adding: sqlt_s81018_metadata2.sql (deflated 66%)
adding: q.sql (deflated 19%)
adding: plan.sql (deflated 27%)
adding: 10053.sql (deflated 22%)
adding: flush.sql (deflated 5%)
adding: tc.sql (deflated 17%)
adding: sel.sql (deflated 40%)
adding: sel_aux.sql (deflated 34%)
adding: install.sql (deflated 58%)
adding: install.sh (deflated 10%)
adding: pack_tcx.sql (deflated 64%)
adding: sqlt_s81018_schema_stats.sql (deflated 56%)
### chmod command below will error out on windows. disregard error.
adding: sqlt_s81018_system_stats.sql (deflated 48%)
adding: sqlt_s81018_set_cbo_env.sql (deflated 78%)
adding: sqlt_s81018_metadata.sql (deflated 64%)
adding: sqlt_s81018_readme.txt (deflated 79%)
adding: q.sql (deflated 19%)
adding: plan.sql (deflated 27%)
adding: 10053.sql (deflated 22%)
adding: flush.sql (deflated 5%)
adding: tc.sql (deflated 17%)
adding: sel.sql (deflated 40%)
adding: sel_aux.sql (deflated 34%)
adding: xpress.sql (deflated 60%)
adding: xpress.sh (deflated 11%)
adding: setup.sql (deflated 43%)
adding: readme.txt (stored 0%)
adding: tc_pkg.sql (deflated 53%)
adding: sqlt_s81018_purge.sql (deflated 30%)
adding: sqlt_s81018_restore.sql (deflated 43%)
adding: sqlt_s81018_del_hgrm.sql (deflated 27%)
adding: sqlt_s81018_opatch.zip (stored 0%)
zip warning: sqlt_s81018_trc.zip not found or empty
adding: orcldemo_ora_4148_s81018_10053.trc (deflated 87%)
adding: orcldemo_ora_4148_s81018_10053_i1_c0.trc (deflated 88%)
adding: orcldemo_ora_4148_sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_10053_explain.trc (deflated 87%)
adding: sqlt_s81018_10053_i1_c0_extract.trc (deflated 88%)
zip warning: name not matched: sqltxtract2.log
adding: sqltxtract.log (deflated 81%)
adding: missing_file.txt (deflated 16%)
zip warning: name not matched: sqltxtract2.log
deleting: sqltxtract.log
deleting: missing_file.txt
zip warning: zip file empty
### ls commands below will error out on windows. disregard error.
### who command below will error out on windows. disregard error.
adding: sqlt_s81018_xpand_sql_driver.sql (stored 0%)
adding: sqlt_s81018_cell_state_begin.txt (stored 0%)
adding: sqlt_s81018_cell_state_begin_and_end.txt (stored 0%)
adding: sqlt_s81018_cell_state_end.txt (stored 0%)
adding: sqlt_s81018_xtract.log (deflated 78%)
adding: sqltxhost.log (deflated 56%)
adding: sqlt_s81018_10053_explain.trc (deflated 87%)
adding: sqlt_s81018_10053_i1_c0_extract.trc (deflated 88%)
adding: sqlt_s81018_cell_state.zip (stored 0%)
adding: sqlt_s81018_driver.zip (stored 0%)
adding: sqlt_s81018_lite.html (deflated 88%)
adding: sqlt_s81018_log.zip (stored 0%)
adding: sqlt_s81018_main.html (deflated 91%)
adding: sqlt_s81018_opatch.zip (stored 0%)
adding: sqlt_s81018_perfhub_0001__.html (deflated 28%)
adding: sqlt_s81018_readme.html (deflated 77%)
adding: sqlt_s81018_sql_detail_active.html (deflated 35%)
adding: sqlt_s81018_tc.zip (stored 0%)
adding: sqlt_s81018_tc_script.sql (deflated 19%)
adding: sqlt_s81018_tc_sql.sql (stored 0%)
adding: sqlt_s81018_tcb.zip (stored 0%)
adding: sqlt_s81018_tcx.zip (stored 0%)
adding: sqlt_s81018_trc.zip (stored 0%)
Archive: sqlt_s81018_xtract_8x615vyks733p.zip
Length Date Time Name
--------- ---------- ----- ----
801938 11-01-2018 02:31 sqlt_s81018_10053_explain.trc
639616 11-01-2018 02:31 sqlt_s81018_10053_i1_c0_extract.trc
610 11-01-2018 02:32 sqlt_s81018_cell_state.zip
7230 11-01-2018 02:32 sqlt_s81018_driver.zip
62342 11-01-2018 02:31 sqlt_s81018_lite.html
912413 11-01-2018 02:32 sqlt_s81018_log.zip
3882333 11-01-2018 02:31 sqlt_s81018_main.html
59911 11-01-2018 02:32 sqlt_s81018_opatch.zip
402939 11-01-2018 02:31 sqlt_s81018_perfhub_0001__.html
22274 11-01-2018 02:31 sqlt_s81018_readme.html
1962 11-01-2018 02:31 sqlt_s81018_sql_detail_active.html
122838 11-01-2018 02:32 sqlt_s81018_tc.zip
207 11-01-2018 02:32 sqlt_s81018_tc_script.sql
35 11-01-2018 02:32 sqlt_s81018_tc_sql.sql
170525 11-01-2018 02:31 sqlt_s81018_tcb.zip
59240 11-01-2018 02:32 sqlt_s81018_tcx.zip
626264 11-01-2018 02:32 sqlt_s81018_trc.zip
--------- -------
7772677 17 files

File sqlt_s81018_xtract_8x615vyks733p.zip for 8x615vyks733p has been created.
sqlt_s81018_sqldx
T
CSV
8x615vyks733p

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)


Values passed:
License: "T"
Output : "CSV"
SQL_ID : "8x615vyks733p"


### ... getting SQL text ...


### ... getting signature ...


### ... getting tables ...


### ... generating dynamic script, please wait ...


sqlt_s81018_sqldx_8x615vyks733p_driver.sql file has been created.

###
### by sql_id
###
2018-11-01/02:32:49 DBA_HIST_SQLTEXT
2018-11-01/02:32:51 DBA_SQLSET_PLANS
2018-11-01/02:32:52 DBA_SQLSET_STATEMENTS
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_HIST_SQLTEXT.csv (deflated 98%)
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_STATEMENTS.csv (deflated 96%)
Archive: sqlt_s81018_sqldx_8x615vyks733p_csv.zip
Length Date Time Name
--------- ---------- ----- ----
12297 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_HIST_SQLTEXT.csv
779840 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_PLANS.csv
16590 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_STATEMENTS.csv
--------- -------
808727 3 files
adding: sqlt_s81018_sqldx_8x615vyks733p_csv.zip (stored 0%)
Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
--------- -------
7123 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-01/02:32:52 DBA_HIST_SNAPSHOT
2018-11-01/02:32:52 GV$PARAMETER2
adding: sqlt_s81018_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81018_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81018_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
51119 11-01-2018 02:32 sqlt_s81018_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-01-2018 02:32 sqlt_s81018_sqldx_global_GVsPARAMETER2.csv
--------- -------
3749527 2 files
adding: sqlt_s81018_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
31844 11-01-2018 02:32 sqlt_s81018_sqldx_global_csv.zip
--------- -------
38967 2 files

sqlt_s81018_sqldx_*.zip files have been created.
adding: sqlt_s81018_sqldx_8x615vyks733p_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81018_sqldx_8x615vyks733p_log.zip
Length Date Time Name
--------- ---------- ----- ----
26423 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_driver.sql
2939 11-01-2018 02:32 sqldx.log
--------- -------
29362 2 files

adding: sqlt_s81018_sqldx_8x615vyks733p_log.zip (stored 0%)


SQLDX files have been created.

Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
31844 11-01-2018 02:32 sqlt_s81018_sqldx_global_csv.zip
4534 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_log.zip
--------- -------
43501 3 files

adding: sqlt_s81018_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81018_purge.sql out of sqlt repository ...

SQLTXTRACT completed.
  • Copy the different folder and Unzip the SQLT extract zip file
[oracle@orcl:run orcldemo] unzip sqlt_s81018_xtract_8x615vyks733p.zip
Archive: sqlt_s81018_xtract_8x615vyks733p.zip
inflating: sqlt_s81018_10053_explain.trc
inflating: sqlt_s81018_10053_i1_c0_extract.trc
extracting: sqlt_s81018_cell_state.zip
extracting: sqlt_s81018_driver.zip
inflating: sqlt_s81018_lite.html
extracting: sqlt_s81018_log.zip
inflating: sqlt_s81018_main.html
extracting: sqlt_s81018_opatch.zip
inflating: sqlt_s81018_perfhub_0001__.html
inflating: sqlt_s81018_readme.html
inflating: sqlt_s81018_sql_detail_active.html
extracting: sqlt_s81018_tc.zip
inflating: sqlt_s81018_tc_script.sql
extracting: sqlt_s81018_tc_sql.sql
extracting: sqlt_s81018_tcb.zip
extracting: sqlt_s81018_tcx.zip
extracting: sqlt_s81018_trc.zip
extracting: sqlt_s81018_sqldx.zip
  • Open the sqlt_s81018_main.html report and start the performance analysis.

XECUTE :-

  • It will execute the SQL statements and get analyzed.Its major drawback is that if the SQL being analyzed takes long to execute, this method will also take long.
  • This doesn’t take SQL_ID or Hash value as an input.Provide the SQL statement
SQL> start sqltxecute.sql user_objects.sql SQLTEXPLAIN

PL/SQL procedure successfully completed.

Parameter 1:
SCRIPT name which contains SQL and its binds (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed to sqltxecute:
SCRIPT_WITH_SQL: "user_objects.sql"

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.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 93%)

NOTE:
You used the XECUTE method connected as HARI.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxecute.log or sqltxecute2.log.
2. Your SQL contains token "^^unique_id" within a comment.
3. Your SQL ends with a semi-colon ";".
4. You connected as the application user that issued original SQL.
5. Script user_objects.sql can execute stand-alone connected as HARI
6. User HARI 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.

... executing user_objects.sql ...

In case of a disconnect review sqltxecute2.log and user_objects_output_s81019.txt

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
If running as SYS in 12c make sure to review sqlt_instructions.html first

File sqlt_s81019_xecute.zip for user_objects.sql has been created.
sqlt_s81019_sqldx
T
CSV
0d7hz8d1y5vw6

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)

Values passed:
License: "T"
Output : "CSV"
SQL_ID : "0d7hz8d1y5vw6"

### ... getting SQL text ...

### ... getting signature ...

### ... getting tables ...

### ... generating dynamic script, please wait ...

sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql file has been created.

###
### by sql_id
###
2018-11-02/01:07:17 DBA_HIST_ACTIVE_SESS_HISTORY
2018-11-02/01:07:17 DBA_HIST_SQLTEXT
2018-11-02/01:07:17 DBA_SQLSET_PLANS
2018-11-02/01:07:18 DBA_SQLSET_STATEMENTS
2018-11-02/01:07:18 GV$ACTIVE_SESSION_HISTORY
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv (deflated 84%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv (deflated 99%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv (deflated 95%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv (deflated 83%)
Archive: sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
Length Date Time Name
--------- ---------- ----- ----
8088 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv
12297 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv
946940 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv
16590 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv
7740 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv
--------- -------
991655 5 files
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip (stored 0%)
Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
--------- -------
11492 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-02/01:07:18 DBA_HIST_SNAPSHOT
2018-11-02/01:07:18 GV$PARAMETER2
adding: sqlt_s81019_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81019_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81019_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
55335 11-02-2018 01:07 sqlt_s81019_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-02-2018 01:07 sqlt_s81019_sqldx_global_GVsPARAMETER2.csv
--------- -------
3753743 2 files
adding: sqlt_s81019_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
32118 11-02-2018 01:07 sqlt_s81019_sqldx_global_csv.zip
--------- -------
43610 2 files

sqlt_s81019_sqldx_*.zip files have been created.
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip
Length Date Time Name
--------- ---------- ----- ----
28251 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql
2928 11-02-2018 01:07 sqldx.log
--------- -------
31179 2 files

adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip (stored 0%)

SQLDX files have been created.

Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
32118 11-02-2018 01:07 sqlt_s81019_sqldx_global_csv.zip
4605 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip
--------- -------
48215 3 files

adding: sqlt_s81019_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81019_purge.sql out of sqlt repository ...

SQLTXECUTE completed.

XTRXEC :-

  • It is a combination feature of XTRACT and XECUTE for DBA. First XTARCT generates a script that contains extracted SQL and expensive plan found for requested statement. XTRXEC then executes the XECUTE phase using the script created before.
  • This method only need SQL_ID/Hash value and sqltxplain_password. This method is most commonly and recommended method for SQL Performance related tuning issues.
SQL> START sqltxtrxec.sql 8x615vyks733p Oracle$123

PL/SQL procedure successfully completed.

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

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Value passed to sqltxtrxec:
SQL_ID_OR_HASH_VALUE: "8x615vyks733p"
#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81021_purge.sql out of sqlt repository ...

SQLTXECUTE completed.
updating: sqlt_s81020_tc_script.sql (deflated 18%)

  adding: sqlt_s81020_xtract_8x615vyks733p.zip (stored 0%)
  adding: sqlt_s81021_xecute.zip (stored 0%)
  adding: sqltxtrxec.log (deflated 75%)

PL/SQL procedure successfully completed.

SQLTXTRXEC completed.

XPLAIN :-

This method is based on the EXPLAIN PLAN FOR command, therefore it is to bind variables referenced by your SQL statement. Use this method only if XTRACT or XECUTE are not possible.

SQL> START sqltxplain.sql /home/oracle/sqlt/run/user_objects.sql

PL/SQL procedure successfully completed.

WARNING:
You are using SQLT XPLAIN method.
If you were requested by Oracle Support to use
XTRACT or XECUTE, then do not use this XPLAIN method.

Be aware that XPLAIN method cannot perform bind peeking
thus you will get an EXPLAIN PLAN instead of actual
EXECUTION PLAN.

Replacing bind variables with literal values does not
guarantee the generated plan to be the same than the one
produced by XTRACT or XECUTE. Thus the plan generated by
XPLAIN might not be useful to progress your issue.

Parameter 1:
Name of file that contains SQL to be explained (required)

Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: Oracle$123

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed:
FILE_WITH_ONE_SQL: "/home/oracle/sqlt/run/user_objects.sql"

PL/SQL procedure successfully completed.

WARNING:
You are using SQLT XPLAIN method.

If you were requested by Oracle Support to use
XTRACT or XECUTE, then do not use this XPLAIN method.

Be aware that XPLAIN method cannot perform bind peeking
thus you will get an EXPLAIN PLAN instead of actual
EXECUTION PLAN.

Replacing bind variables with literal values does not
guarantee the generated plan to be the same than the one
produced by XTRACT or XECUTE. Thus the plan generated by
XPLAIN might not be useful to progress your issue.

***
*** 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.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 93%)

... reading file /home/oracle/sqlt/run/user_objects.sql ...

File sqlt_s81023_xplain.zip for /home/oracle/sqlt/run/user_objects.sql has been created.
sqlt_s81023_sqldx
T
CSV
0d7hz8d1y5vw6

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)

Values passed:
License: "T"
Output : "CSV"
SQL_ID : "0d7hz8d1y5vw6"

### ... getting SQL text ...

### ... getting signature ...

### ... getting tables ...

### ... generating dynamic script, please wait ...

sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql file has been created.

###
### by sql_id
###
2018-11-02/02:37:57 DBA_HIST_ACTIVE_SESS_HISTORY
2018-11-02/02:37:57 DBA_HIST_SQLTEXT
2018-11-02/02:37:57 DBA_SQLSET_PLANS
2018-11-02/02:37:57 DBA_SQLSET_STATEMENTS
2018-11-02/02:37:57 GV$ACTIVE_SESSION_HISTORY
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv (deflated 84%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv (deflated 99%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv (deflated 95%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv (deflated 83%)
Archive: sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
Length Date Time Name
--------- ---------- ----- ----
8088 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv
12297 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv
946940 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv
16590 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv
7740 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv
--------- -------
991655 5 files
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip (stored 0%)
Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
--------- -------
11492 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-02/02:37:57 DBA_HIST_SNAPSHOT
2018-11-02/02:37:57 GV$PARAMETER2
adding: sqlt_s81023_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81023_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81023_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
45322 11-02-2018 02:37 sqlt_s81023_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-02-2018 02:37 sqlt_s81023_sqldx_global_GVsPARAMETER2.csv
--------- -------
3743730 2 files
adding: sqlt_s81023_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
31633 11-02-2018 02:37 sqlt_s81023_sqldx_global_csv.zip
--------- -------
43125 2 files

sqlt_s81023_sqldx_*.zip files have been created.
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip
Length Date Time Name
--------- ---------- ----- ----
28250 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql
2927 11-02-2018 02:37 sqldx.log
--------- -------
31177 2 files

adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip (stored 0%)

SQLDX files have been created.

Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
31633 11-02-2018 02:37 sqlt_s81023_sqldx_global_csv.zip
4606 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip
--------- -------
47731 3 files

adding: sqlt_s81023_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81023_purge.sql out of sqlt repository ...

SQLTXPLAIN completed.

XTRSBY Method :-

Use this method if you need to analyze a SQL executed on a Data Guard or stand-by read-only database. You need to know the SQL_ID or the Hashvalue of the SQL to be analyzed.

  • Create on Primary database a link to read-only database connecting as any user that has access to the data dictionary. we need DBA privileges access to do it.
SQL> CREATE PUBLIC DATABASE LINK DEMO CONNECT TO HARI IDENTIFIED by hari USING '(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=orcldemo.localdomain.com)(PORT=1521))(CONNECT_DATA=(SID = orcldemo)))';

Database link created.
  • If the SQL is still in memory in the read-only database, then XTRSBY finds it and provides a set of diagnostics files, else XTRSBY errors out.
  • XTRSBY need 3 parameters: the SQL _ID, the DB_LINK name, and the SQLTXPLAIN password
  • To use this XTRSBY method, be sure SQLT has been installed on the Primary first, and replicated into the read-only database.
SQL>START sqltxtrsby.sql 0d7hz8d1y5vw6 Oracle$123 orcldemo

PL/SQL procedure successfully completed.

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

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Parameter 3:
DBLINK to stand-by database (required)

Values passed to sqltxtrsby:
SQL_ID_OR_HASH_VALUE: "0d7hz8d1y5vw6"
DB_LINK : "@orcldemo"

PL/SQL procedure successfully completed.

The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81024_purge.sql out of sqlt repository ...

SQLTXTRSBY completed.

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba

 

Leave a Reply

Your email address will not be published. Required fields are marked *