Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQLTXPLAIN is now available

with 12 comments

What is new in SQLT? A couple of new stand-alone utilities. Both can be used without having to install SQLT. And both are only for 11g+. Look for sqlt/utl/mon and sqlt/utl/spm. The former is to monitor executions taking longer than 5 seconds (requires Oracle Tuning Pack and it uses the SQL Monitor report). The latter is to facilitate the use of SQL Plan Management (SPM), specially for plan migrations. For a complete log of changes to SQLT you can review the changes log here: SQLT Changes Log as of Most recent set is this: May 10, 2013

  1. BUG: Fix TRCA parsing errors due to missing CURSOR information: invalid call at line 27 “1” “1” “” “2”.
  2. ENH: List of Cursors to include now “is_obsolete” flag.
  3. ENH: Improved performance on SQLT TC restore script. This SQL was taking too long to execute: UPDATE SQLI$_STATTAB_TEMP SET C5 = :B2 WHERE STATID = :B1 AND TYPE IN (‘C’, ‘I’, ‘T’).
  4. ENH: Traces generated by SQLT are now limited to 200 MB per file.
  5. ENH: New stand-alone set of scripts to migrate a SQL Plan Basaline. Look for readme in sqlt/utl/spm.
  6. ENH: New stand-alone monitoring tool to capture executions of SQL statements taking more than 5 seconds. Look for readme in sqlt/utl/mon. April 5, 2013

  1. BUG: SQL Tuning Advisor was invoked only when SQL existed in memory. Now it is invoked also if SQL resides only in AWR.
  2. BUG: File sqlt_instructions.html had links with XPREXT tag instead of XTRACT.
  3. ENH: TKPROF may error out under some circumstances. SQLT now displays a message to “ignore this error”.
  4. ENH: Improved performance of SQLT COMPARE when number of partitions is high.
  5. ENH: Collection of dba_sql_patches only happens on 11g+. It used to error out on 10g.
  6. ENH: Following error shows now on log file but not in MAIN html report:
    sqlt$a: *** i:DBMS_SPACE.CREATE_INDEX_COST: ORA-01031: insufficient privileges
    sqlt$a: *** i:index_name:XIE1_PCRD_APPROVER
  7. ENH: Best and Worst plans now consider last those plans with no fetches. Before this change it was possible to see killed executions (fetches 0) ranking as good plans.
  8. ENH: Purge of TRCA global temporary tables uses now a TRUNCATE instead of a DELETE.
  9. ENH: SQLT produces now a with all installation logs.
  10. ENH: Include Product Component Version in installation and main methods logs.
  11. ENH: SQLT log zip includes now alert.log and spfile.
  12. ENH: SQLDX (SQL Dynamic eXtractor) includes now details about tables accessed by execution plans of SQL provided. March 5, 2013

  1. ENH: New script sqlt/utl/sqltq.sql to generate “q” script that contains binds and sql text for given SQL_ID.
  2. ENH: Script sqlt/utl/coe_gen_sql_patch.sql inputs now CBO Hints text in addition to SQL_ID.
  3. ENH: New min methods XPREXT and XPREXC similar to XTRACT and XECUTE but disabling several SQLT features as per sqlt/run/sqltcommon11.sql in order to improve SQLT performance. March 1, 2013

  1. BUG: Following SQLT Parameters where not updateable: sql_monitor_reports, awr_reports, addm_reports, sta_time_limit_secs
    Workaround: Execute this update before using API to change value: update SQLTXPLAIN.sqli$_parameter set low_value = 0, high_value = 9999 where name = ‘addm_reports’;
  2. BUG: Eliminate duplicate entries on views: sqlt$_gv$act_sess_hist_pl_v and sqlt$_dba_act_sess_hist_pl_v.
  3. BUG: Incorrect observation “Table rebuild candidate.” when getting error on DBMS_SPACE.CREATE_TABLE_COST.
  4. BUG: Incorrect observation “Index coalesce candidate.” when getting error on DBMS_SPACE.CREATE_INDEX_COST.
  5. BUG: Trap this error on 10g:
    ORA-00942: table or view does not exist
    sqlt$a: *** d:INSERT INTO sqlt$_gv$cell_state (statement_id, statid, begin_end_flag) SELECT :statement_id, :statid, :begin_end_flag FROM gv$cell_state
  6. ENH: Remove health-check references to Bug 3620168, which was fixed on 10.2.
  7. ENH: COUNT(*) on Tables referenced by SQL includes now a FULL(t) Hint. This is to improve performance of COUNT.
  8. ENH: SQLT now sets TRCA to skip extents capture (by setting TRCA tool parameter “capture_extents” to N during SQLT installation). This is to improve XECUTE performance.
  9. ENH: New SQLT parameter “tcb_export_pkg_body” controls if 11.2+ Test Case Builder includes package bodies (of the packages referenced in the SQL are exported). Default is FALSE.
  10. ENH: View DBA_HIST_PARAMETER is now excluded from SQLT repository export file. This is to improve performance of export and to reduce size of zip.
  11. ENH: Value of parameter “_db_file_exec_read_count” is now displayed in MAIN and COMPARE reports.
  12. ENH: 11g PLSQL_CODE_TYPE is set to NATIVE by default (to improver performance). 10g defaults back to INTERPRETED.
  13. ENH: SQLY XPLORE now iterates “_optimizer_max_permutations” on values: 100, 2000, 40000, 79999 and 80000.
  14. ENH: SQL Patches are now displayed on the MAIN report.
  15. ENH: New script sqlt/utl/flush_cursor.sql flushes one cursor out of the shared pool.
  16. ENH: New script sqlt/utl/coe_gen_sql_patch.sql generates a SQL Patch for one SQL with some diagnostics CBO Hints. It also turns trace 10053 for same SQL.

Written by Carlos Sierra

May 10, 2013 at 10:43 am

Posted in General

12 Responses

Subscribe to comments with RSS.

  1. Carlos, I attempted to install the latest sqlt using a remote database. It seems to be hanging. Is it possible to install this way?


    May 10, 2013 at 5:19 pm

    • Rich. It is possible. You login to the remote using a connect identifier, then when ask, you indicate the same connect identifier (1st parameter). But it is better if you login to the same server (if possible). When you say it is hanging, what do you mean? what is the last file it created? Look into your local directory. There must be a zip and a log.

      Carlos Sierra

      May 10, 2013 at 5:24 pm

      • just installed it but when I tried to run the below command :
        sql> start “C:\oracle\run\sqltxplain.sql” sqlid
        I got the following error :

        ERROR at line 1:
        ORA-20106: SQLT parameter connect_identifier must be set when running SQLT from a remote client
        ORA-06512: at line 10


        November 11, 2015 at 12:09 pm

      • looks like you are using a remote connection to reach the DB and your SQLT parameter “connect_identifier” is not set. Please look at instructions html file

        Carlos Sierra

        November 11, 2015 at 2:42 pm

    • nevermind, just very slow. Odd for an Exadata


      May 10, 2013 at 5:32 pm

  2. Carlos, where could I download old version of SQLT? In case end user used old version, and I need build the testcase… Thank you

    X kglob

    May 15, 2013 at 11:30 am

    • SQLT from May 10, 2013 is backward compatible with all prior versions between from April 11, 2010 to current one. So you can install a test case created lets say on into
      Still, if for any reason you want a prior version, let me know which one you need (and why) and I will send it to you by email.
      Cheers — Carlos

      Carlos Sierra

      May 15, 2013 at 4:56 pm

  3. […] SQLTXPLAIN is now available ( […]

  4. […] recently decided to start exploring the Oracle SQLTXPLAIN tool developed by Carlos Sierra from Oracle support. Up to know I am still exploring the different […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: