Oracle SQLTXPLAIN (SQLT)
Reference -
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 :
- Download SQLT
- Install SQLT
- Run SQLT report
Download SQLT tool :
- Login to My Oracle Support (http://support.oracle.com)
- Browse the DOCID All About the SQLT Diagnostic Tool (Doc ID 215187.1)
- Click the Download SQLTXPLAIN of your database version.
- Unzip the SQLT (sqlt_10g_11g_12c_25_08_2018.zip) zip file.
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;
- Installing required packages to support SQLT.
- Create SQLT schema objects.
- Migrating relevant objects from old to new repository.
- 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.
EXECUTE :-
- 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.
No comments:
Post a Comment