Carlos Sierra's Tools and Tips

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 11.4.5.8. Most recent set is this:

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

11.4.5.7 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 SQLT_installation_logs_archive.zip 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.

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

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