Oracle SQLTXPLAIN SQLT Tutorial Example Part 2

In our first tutorial, we discussed how to setup and configure SQLTXPLAIN for your Oracle Database. We discussed the values we have to configure for this performance measuring tool during installation, and we have managed to install it successfully into our Oracle database. In this part, we will discuss how to use SQLT and collect diagnostic details;

To generate statistics for a SQL SQLTXPLAIN offers the following different methods: [ XTRACT,XECUTE,XTRXEC,XPLAIN ]

We will try and explain each of these methods based on a test data. 

XTRACT Approach

If you want to collect diagnostic data using this method, you need to know the SQL_ID or HASH_VALUE of the SQL to be analyzed for performance statistics. In order to see how to use this method, we will create a test table and pump some data into it. Refer to the table below

create table big_table as select object_name,substr(object_name,1,1)as first_char from all_objects / select count(*) from big_table where first_char='C' / SQL ID: 0dqt86jwahdub

If you refer above, I have collected the SQL ID of the Query I just executed. We will now use the XTRACT method to collect diagnostic data for this SQL ID. To run XTRACT execute the command as shown below from SQLPLUS

START sqltxtract.sql 0w6uydn50g8cx  /* This SQL file is defined under sqlt/run folder */

Now, when you run this, a lot of statistics gets collected at the background, and a zip file is produced as the output. Refer below for the logs (You will be prompted for SQLTXPLAIN schema password when you run this)

You used the XTRACT method connected as SYS. In case of a session disconnect please verify the following: 1. There are no errors in sqltxtract.log. 2. Your SQL 0dqt86jwahdub exists in memory or in AWR. 3. You connected as the application user that issued original SQL. In case of errors ORA-03113, ORA-03114 or ORA-07445 re-execute SQLT. This tool detects BUG 6356566 and handles it on a re-try. To actually fix BUG 6356566, read ALERT log and provide referenced traces to Support. Reset SQLT parameter predicates_in_plan afterwards. To monitor progress, login as SQLTXPLAIN into another session and execute: SQL> SELECT * FROM sqlt$_log_v; /* You can do this to track the program activity */ ... collecting diagnostics details ... ... getting sqlt_s92772_remote_driver.sql out of sqlt repository ...         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_remote_driver.sql (160 bytes security) (deflated 40%) test of sqlt_s92772_driver.zip OK         zip warning: error deleting sqlt_s92772_remote_driver.sql Archive:  sqlt_s92772_driver.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files ... getting sqlt_s92772_main.html out of sqlt repository ... ... getting sqlt_s92772_lite.html out of sqlt repository ... ... getting sqlt_s92772_readme.html out of sqlt repository ... ... getting sqlt_s92772_readme.txt out of sqlt repository ... ... getting sqlt_s92772_metadata.sql out of sqlt repository ... ... getting sqlt_s92772_system_stats.sql out of sqlt repository ... ... getting sqlt_s92772_set_cbo_env.sql out of sqlt repository ... ... getting sqlt_s92772_p599409829_sqlprof.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_s92772_10053_explain.trc out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s92772_import.sh out of sqlt repository ... ... getting sqlt_s92772_export_parfile.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_s92772_purge.sql out of sqlt repository ... ... getting sqlt_s92772_restore.sql out of sqlt repository ... ... getting tc.sql out of sqlt repository ... ... getting tc.sh out of sqlt repository ... ... getting sqltc.sql out of sqlt repository ... ... getting setup.sql out of sqlt repository ... ... getting selectivity.sql out of sqlt repository ... ... getting selectivity_aux.sql out of sqlt repository ... ... getting sqlt_s92772_tcb_driver.sql out of sqlt repository ...         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: SQLT_S92772_TCB_DPEXP.DMP (100 bytes security) (deflated 89%)   adding: sqlt_s92772_tcb_dpexp.log (100 bytes security) (deflated 46%)   adding: sqlt_s92772_tcb_dpexp.sql (100 bytes security) (deflated 72%)   adding: sqlt_s92772_tcb_dpimp.sql (100 bytes security) (deflated 66%)   adding: sqlt_s92772_tcb_main.xml (100 bytes security) (deflated 76%)   adding: sqlt_s92772_tcb_ol.xml (100 bytes security) (deflated 43%)   adding: sqlt_s92772_tcb_sql.xml (100 bytes security) (deflated 46%)   adding: sqlt_s92772_tcb_ssimp.sql (100 bytes security) (deflated 55%)   adding: sqlt_s92772_tcb_xpl.txt (100 bytes security) (deflated 61%)   adding: sqlt_s92772_tcb_xplf.sql (100 bytes security) (deflated 51%)   adding: sqlt_s92772_tcb_xplo.sql (100 bytes security) (deflated 51%)   adding: sqlt_s92772_tcb_xpls.sql (100 bytes security) (deflated 53%)   adding: README.txt (100 bytes security) (deflated 56%) test of sqlt_s92772_tcb.zip OK Archive:  sqlt_s92772_tcb.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_tcb_driver.sql (160 bytes security) (deflated 55%) test of sqlt_s92772_driver.zip OK         zip warning: error deleting sqlt_s92772_tcb_driver.sql Archive:  sqlt_s92772_driver.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files ... getting sqlt_s92772_export_driver.sql out of sqlt repository ... ****************************************************************** * Enter SQLTXPLAIN password to export SQLT repository (required) *  /* You will be prompted for schema password at this point */ ****************************************************************** Export: Release 11.2.0.1.0 - Production on Sun May 1 18:40:24 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported Note: constraints on tables will not be exported About to export specified tables via Conventional Path ... . . exporting table            SQLT$_SQL_STATEMENT          1 rows exported . . exporting table               SQLT$_AUX_STATS$         13 rows exported . . exporting table      SQLT$_DBA_AUTOTASK_CLIENT          1 rows exported . . exporting table           SQLT$_DBA_COL_USAGE$          1 rows exported . . exporting table          SQLT$_DBA_CONSTRAINTS          1 rows exported . . exporting table              SQLT$_DBA_OBJECTS          1 rows exported . . exporting table   SQLT$_DBA_OPTSTAT_OPERATIONS         40 rows exported . . exporting table             SQLT$_DBA_SEGMENTS          1 rows exported . . exporting table             SQLT$_DBA_TAB_COLS          2 rows exported . . exporting table       SQLT$_DBA_TAB_STATISTICS          1 rows exported . . exporting table               SQLT$_DBA_TABLES          1 rows exported . . exporting table          SQLT$_DBA_TABLESPACES          1 rows exported . . exporting table               SQLT$_DBMS_XPLAN         94 rows exported . . exporting table     SQLT$_GV$OBJECT_DEPENDENCY          1 rows exported . . exporting table            SQLT$_GV$PARAMETER2        342 rows exported . . exporting table         SQLT$_GV$PARAMETER_CBO        275 rows exported . . exporting table            SQLT$_GV$PQ_SYSSTAT         20 rows exported . . exporting table    SQLT$_GV$PX_PROCESS_SYSSTAT         15 rows exported . . exporting table                   SQLT$_GV$SQL          1 rows exported . . exporting table              SQLT$_GV$SQL_PLAN          3 rows exported . . exporting table     SQLT$_GV$SQL_SHARED_CURSOR          1 rows exported . . exporting table               SQLT$_GV$SQLAREA          1 rows exported . . exporting table     SQLT$_GV$SQLAREA_PLAN_HASH          1 rows exported . . exporting table              SQLT$_GV$SQLSTATS          1 rows exported . . exporting table    SQLT$_GV$SQLSTATS_PLAN_HASH          1 rows exported . . exporting table SQLT$_GV$SQLTEXT_WITH_NEWLINES          1 rows exported . . exporting table                      SQLT$_LOG        714 rows exported . . exporting table                 SQLT$_METADATA          2 rows exported . . exporting table             SQLT$_OUTLINE_DATA         24 rows exported . . exporting table           SQLT$_PLAN_EXTENSION          6 rows exported . . exporting table                SQLT$_PLAN_INFO         16 rows exported . . exporting table           SQLT$_SQL_PLAN_TABLE          3 rows exported . . exporting table    SQLT$_V$SESSION_FIX_CONTROL        406 rows exported Export terminated successfully without warnings. 'chmod' is not recognized as an internal or external command, operable program or batch file.         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_exp.dmp (160 bytes security) (deflated 83%)   adding: sqlt_s92772_import.sh (160 bytes security) (deflated 37%) test of sqlt_s92772_tc.zip OK Archive:  sqlt_s92772_tc.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_exp.log (160 bytes security) (deflated 76%) test of sqlt_s92772_log.zip OK Archive:  sqlt_s92772_log.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_export_parfile.txt (160 bytes security) (deflated 73%)   adding: sqlt_s92772_export_driver.sql (160 bytes security) (deflated 65%) test of sqlt_s92772_driver.zip OK         zip warning: error deleting sqlt_s92772_export_driver.sql Archive:  sqlt_s92772_driver.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files ... getting sqlt_s92772_tc_sql.sql out of sqlt repository ... ... getting q.sql out of sqlt repository ... ... getting sqlt_s92772_tc_script.sql out of sqlt repository ...         zip warning: name not matched: $ORACLE_HOME/cfgtoollogs/opatch/opatch*         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log zip error: Nothing to do! (sqlt_s92772_opatch.zip) unzip:  cannot find either sqlt_s92772_opatch or sqlt_s92772_opatch.zip. 'chmod' is not recognized as an internal or external command, operable program or batch file.         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_system_stats.sql (160 bytes security) (deflated 48%)   adding: sqlt_s92772_metadata.sql (160 bytes security) (deflated 66%)   adding: sqlt_s92772_set_cbo_env.sql (160 bytes security) (deflated 77%)   adding: sqlt_s92772_readme.txt (160 bytes security) (deflated 72%) test of sqlt_s92772_tc.zip OK         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: q.sql (160 bytes security) (deflated 10%)   adding: plan.sql (160 bytes security) (deflated 17%)   adding: 10053.sql (160 bytes security) (deflated 8%)   adding: flush.sql (160 bytes security) (deflated 5%)   adding: tc.sql (160 bytes security) (deflated 19%)   adding: sqltc.sql (160 bytes security) (deflated 62%)   adding: tc.sh (160 bytes security) (deflated 6%)   adding: setup.sql (160 bytes security) (deflated 47%)   adding: selectivity.sql (160 bytes security) (deflated 43%)   adding: selectivity_aux.sql (160 bytes security) (deflated 37%) test of sqlt_s92772_tc.zip OK         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqlt_s92772_purge.sql (160 bytes security) (deflated 32%)   adding: sqlt_s92772_restore.sql (160 bytes security) (deflated 42%) test of sqlt_s92772_tc.zip OK         zip warning: name not matched: sqlt_s92772_opatch.zip         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log test of sqlt_s92772_tc.zip OK Archive:  sqlt_s92772_tc.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files         zip warning: sqlt_s92772_trc.zip not found or empty         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: srini_ora_4504_s92772_10053.trc (100 bytes security) (deflated 79%) test of sqlt_s92772_trc.zip OK         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log test of sqlt_s92772_trc.zip OK Archive:  sqlt_s92772_trc.zip   Length      Date    Time    Name ---------  ---------- -----   ---- ---------                     -------         0                     0 files         zip warning: name not matched: sqltxtract2.log         zip warning: name not matched: >>         zip warning: name not matched: sqltxhost.log   adding: sqltxtract.log (160 bytes security) (deflated 78%)   adding: missing_file.txt (160 bytes security) (deflated 15%)         zip warning: name not matched: sqltxtract2.log         zip warning: name not matched: sqltxhost.log deleting: sqltxtract.log deleting: missing_file.txt         zip warning: zip file empty Archive:  sqlt_s92772.zip warning [sqlt_s92772.zip]:  zipfile is empty 'ls' is not recognized as an internal or external command, operable program or batch file. 'ls' is not recognized as an internal or external command, operable program or batch file. 'who' is not recognized as an internal or external command, operable program or batch file.   adding: sqlt_s92772_xtract.log (160 bytes security) (deflated 76%)   adding: sqltxhost.log (160 bytes security) (deflated 41%) test of sqlt_s92772_log.zip OK   adding: sqlt_s92772_10053_explain.trc (160 bytes security) (deflated 79%)   adding: sqlt_s92772_driver.zip (160 bytes security) (stored 0%)   adding: sqlt_s92772_export_driver.sql (160 bytes security) (deflated 65%)   adding: sqlt_s92772_lite.html (160 bytes security) (deflated 73%)   adding: sqlt_s92772_log.zip (160 bytes security) (stored 0%)   adding: sqlt_s92772_main.html (160 bytes security) (deflated 86%)   adding: sqlt_s92772_p599409829_sqlprof.sql (160 bytes security) (deflated 51%)   adding: sqlt_s92772_readme.html (160 bytes security) (deflated 71%)   adding: sqlt_s92772_remote_driver.sql (160 bytes security) (deflated 40%)   adding: sqlt_s92772_tc.zip (160 bytes security) (stored 0%)   adding: sqlt_s92772_tcb.zip (160 bytes security) (stored 0%)   adding: sqlt_s92772_tcb_driver.sql (160 bytes security) (deflated 55%)   adding: sqlt_s92772_tc_script.sql (160 bytes security) (deflated 10%)   adding: sqlt_s92772_tc_sql.sql (160 bytes security) (stored 0%)   adding: sqlt_s92772_trc.zip (160 bytes security) (stored 0%) test of sqlt_s92772.zip OK Archive:  sqlt_s92772.zip   Length      Date    Time    Name ---------  ---------- -----   ----    138664  01/05/2011 18:40   sqlt_s92772_10053_explain.trc      2948  01/05/2011 18:42   sqlt_s92772_driver.zip       838  01/05/2011 18:40   sqlt_s92772_export_driver.sql      8075  01/05/2011 18:40   sqlt_s92772_lite.html      9368  01/05/2011 18:42   sqlt_s92772_log.zip    534089  01/05/2011 18:40   sqlt_s92772_main.html      2818  01/05/2011 18:40   sqlt_s92772_p599409829_sqlprof.sql     12545  01/05/2011 18:40   sqlt_s92772_readme.html       217  01/05/2011 18:40   sqlt_s92772_remote_driver.sql    107788  01/05/2011 18:42   sqlt_s92772_tc.zip     26110  01/05/2011 18:40   sqlt_s92772_tcb.zip       417  01/05/2011 18:40   sqlt_s92772_tcb_driver.sql       157  01/05/2011 18:42   sqlt_s92772_tc_script.sql        56  01/05/2011 18:42   sqlt_s92772_tc_sql.sql     29076  01/05/2011 18:42   sqlt_s92772_trc.zip ---------                     -------    873166                     15 files File sqlt_s92772.zip for 0dqt86jwahdub has been created. /* Final success indicator */ SQLTXTRACT completed. SQL>

If you open the zip file, you will see the following contents



We will look into the contents of this zip file in more detail in the next post.