Note:215187.1 SQLT changes log

MM/DD/YY - Version
~~~~~~~~~~~~~~~~~~
08/31/09 - 11.2.9.8
 1. XPLORE to test _optimizer_search_limit set to 6 only.
 2. sqlt/utl/sqltprofile.sql handles hints with len > 500.
 3. sqlt/install/squtltest.sql exits if utl_file errors out.
 4. Re-grant access to SQLT$STAGE to TRCA.
 5. Added EVENT 10241 to XECUTE in order to get details about REMOTE
    operations.
 6. Added "_c" to copied file in order to avoid files with size 0 if
    executed from a SQLT directory.
 7. New tool parameters to disable 10046, 10053 and other traces.

08/17/09 - 11.2.9.7
 1. Fix bug 8758594 ORA-00600: internal error code, arguments:
    [qksceParamStrToNum2], [GIVE_ME_LOV], [], [], [], [], [], []
    ALTER SESSION SET "_optimizer_undo_cost_change" = 'GIVE_ME_LOV'
 2. Improved readability by including row number in each table, as
    well as title every 30 rows. Controlled by tool parameter.
 3. Detect and report 5969780 CPU overhead in Linux for 10g.
 4. COMPARE method indicates statement_ids for line 1 and 2.
 5. Main report and COMPARE to include Table Join Order.
 6. Use TRANSLATE to validate SQL_ID.
 7. Improved performance of refresh_sqli$_segments.
 8. Footnote in Child Plan regarding the availability of values for
    several columns only when statistics_level is set to ALL.
 9. Added DBMS_STATS execute grant for 9i systems.
10. Re-sync with bde_chk_cbo.sql 11.2.9.7 174605.1 216205.1/396009.1

07/23/09 - 11.2.9.6
 1. Simplify hints assignment in coe_xfr_sql_profile.sql and
    sqltprofile.sql.
 2. XPLORE to test _optimizer_search_limit set to 8 only.
 3. Setenv script to contain parameters with default value in
    addition to those with non-default values.
 4. Fix error while checking there exists a PLAN in Plan Table
    ORA-01722: invalid number
    ORA-06512: at "SQLTXPLAIN.SQLT$D", line 7819
 5. CTXSYS.CTX_REPORT.CREATE_INDEX_SCRIPT provides now whole DDL
    for DOMAIN indexes.
 6. Detect columns with Frequency histograms and 1 bucket.
 7. Validate reasonable sample size when gathering stats on
    columns with histograms.
 8. Validate sreadtim and mreadtim are consistent between them.
 9. XPLORE to show tests count per plan cost under summary section.
10. Use WRI$_OPTSTAT_HISTGRM_HISTORY to report table columns with
    mutating number of buckets in their histograms.
11. USER_STATS is now displayed for Tables, Indexes, Partitions,
    Subpartitions and CBO Table/Index History.
12. Fix errors below due to incorrect setup for PLSQL_CODE_TYPE
    ORA-24344: success with compilation error
    ORA-06512: at line 19
    155/3    PL/SQL: Declaration ignored
    157/10   PLS-00905: object SQLTXPLAIN.BIND_NT is invalid
13. Hidden columns are now displayed in "indexed columns" list.
14. COMPARE now includes Table and Index subpartitions.
15. User Stats are added to CBO Stats in MAIN and COMPARE reports.
16. COMPARE includes Table Partition and Subpartition Columns.
17. COMPARE includes Low and High value as well as AVG_COL_LEN.
18. Display COST when PROFILE displays list of plans to choose from.
19. Added 29 columns to SQLG$_SQL based on V$SQL 11g.
20. New System Statistics section with current and historical stats.
21. DBA_AUTOTASK_CLIENT details for 11g on client_name
    "auto optimizer stats collection".
22. Added DBMS_STATS parameters for 11g.

06/28/09 - 11.2.9.5
 1. Extended health-check of CBO stats as per Note: 337096.1.
 2. New method XPLORE searches for plans by tweaking parameters

06/18/09 - 11.2.9.4
 1. XPLAIN, XTRACT and XECUTE accept host_string when asking for
    SQLTXPLAIN password for export of SQLT repository.
 2. sqlt$_other_xml_hints.hint expanded from 512 to 2000, fixing
    ORA-12899: value too large for column
    "SQLTXPLAIN"."SQLT$_OTHER_XML_HINTS"."HINT" (actual: 839,
    maximum: 512)
 3. refresh_sqli$_segments is now executed if more then 7 days old.
 4. Complex SQL was generating on plan table incorrect statement_id
    when using SQLT on 9i.

06/12/09 - 11.2.9.3
 1. XECUTE to issue EVENT 10730 for fine-grained access control
 2. Replace NUL for Space on sql_text in order to avoid error
    "ORA-06533: Subscript beyond count" on sqltprofile
 3. Include in AWR Hist SQL Plan all known plans and not just
    the ones with an entry on DBA_HIST_SQLSTAT.
 4. Change default for skip_subpartitions tool configuration
    parameter from N to Y in order to reduce main report size.
 5. New script squtltest.sql executed automatically during install
    verifies that SQLT directories can be accessed by UTL_FILE.
 6. Since SQL Statements can have & in literals (i.e. URLs),
    token &&unique_id has been changed to ^^unique_id.
 7. Enhanced readme.txt by incorporating instructions to create
    a stand-alone Test Case based on a SQLT Test Case.

05/27/09 - 11.2.9.2
 1. On Explain Plan, show Last Rows even if it has all zeroes.
 2. Fix error: SELECT host_name FROM v$instance
    ERROR at line 1: ORA-00942: table or view does not exist
 3. Fix error: ORA-00933: SQL command not properly ended
    CREATE SEQUENCE sqlt$_statement_id_s START WITH 3,114
 4. Low and High value of TIMESTAMP type columns display now
    correctly instead of garbish in Table Columns.
 5. Use ^ instead of & as the character used to prefix
    substitution variables. Some SQL with URLs have &
 6. Section Initialization Parameters (GV$PARAMETER2) showed
    no entries in some cases.
 7. Eliminate metadata for SYS objects.
 8. Version validation between SQLT scripts and libraries.
 9. Updated list of init.ora params for APPS.

04/29/09 - 11.2.9.1
 1. Fix ORA-01400: cannot insert NULL into
   ("SQLTXPLAIN"."SQLT$_OTHER_XML"."HASH_VALUE").
 2. Fix ORA-01403: no data found sqlt$d.data_collection.plan_cost2.
 3. Fix ORA-00957: duplicate column name ORA-06512: at line 38
    when installing SQLT on 10.1.0.3.
 4. Fix for large SQLs: ORA-06502: PL/SQL: numeric or value error
    sqlt$d.data_collection.generate_signatures.
 5. Incorporate DBA_TAB_STATISTICS, DBA_IND_STATISTICS and
    DBA_TAB_COL_STATISTICS views with corresponding config param.
 6. SQLT reports now CBO Stats on Fixed Objects when accessed.
 7. Other_XML is found in plan even if not under id 1.
 8. Workaround bug 6356566 by disabling plan table predicates if
    one SQLT execution gets a disconnection (ORA-07445: exception
    encountered: core dump) on SELECT * FROM SQLT$_V$SQL_PLAN.
 9. Deprecate obsolete parameter debug_level_10046.
10. Improve SQLT performance by eliminating COMMITs and
    consolidating SQLT schema objects stats gathering.

04/19/09 - 11.2.9
 1. Metadata for TYPE include semicolon ";" and "/". Some types
    were missing one of the two out of DBMS_METADATA.GET_DDL.
 2. All SQLT execution scripts include SQLT$ PL/SQL library verions
    to validate mismatch between script and library versions.
 3. Metadata script executes ALTER TABLE commands 3 times at the end
    in order to satisfy integrity constraints for FK and PK.
 4. Object Depencencies include reference object owner, name, hash
    value, address and source.
 5. Validate password for SQLTXPLAIN user does not contain spaces.
 6. Reduce the size of dmp and zip files by excluding segments
    snapshot from SQLT objects export.
 7. Several SQLT schema objects are promoted from GLOBAL TEMPORARY to
    permanent, incl. Indexes, Tab Cols, Ind Cols, Stored Outlines,
    SQL Profiles, Session Stats.
 8. New table sqlt$_hist_files stores all reports generated by SQLT.
 9. Linux Statistics Level controlled by tool configuration param.
    Possible values are: TYPICAL (default), ALL and BASIC.
10. SQL Statistics section includes reason WHY a cursor was not
    shared as per view GV$SQL_SHARED_CURSOR.
11. Deprecate days_keep_cbo_stats Tool Configuration Parameter. SQLT
    CBO Stats are now kept as long as SQLT is not uninstalled.
12. Clause TABLESPACE is now removed from metadata script for
    Materialized Views (bug 8372834). Same for STORAGE.
13. Created files metadata, setenv and readme now document which user
    must be used to connect when executing each steps.
14. SQLT accepts now MERGE command type. This is in addition to CTAS,
    Select, Insert, Update and Delete.
15. XTRACT method generates two new Test Cases files: tcscript and
    tcsql. The former is to be used with XECUTE and the latter with
    XPLAIN methods.
16. New Test Case Builder script is created by XTRACT and XECUTE on
    11g (DBMS_SQLDIAG.IMPORT_SQL_TESTCASE).
17. ZIP command deletes the files generated by SQLT after they have
    been copied into sqlt_*.zip file.
18. COMPARE method shows in red when compared plans have same length
    and operations with same id are different.
19. COMPARE method includes now Access and Filer Predicates, and when
    different they are highlighted in red.
20. COMPARE method shows difference in average row lenght for Tables
    and Partitions.
21. COMPARE and SQLTIMP include now timestamp in list of STAT_IDs.
22. New run/sqlthistfile.sql script extracts files from SQLT historic
    file repository. This "files repository" is populated by imp of
    sqlt*.dmp file or by use of XPLAIN, XTRACT and XECUTE.
23. New run/sqlthistpurge.sql script to purge SQLT repository for a
    given range of statement ids.
24. New method XTRXEC which combines XTRACT and XECUTE. Extracts one
    SQL given sql_id or hash_value, then creates a script with bind
    variables used at peeking (10g or 11g) and calls XECUTE on it.
25. Readme file includes specific instructions how to transfer an
    Stored Outline from one system into another.
26. Readme includes specific instructions how to gather a baseline
    for schema objects CBO statistics, before any SQL tuning.
27. Mouse over for main Explain Plan and AWR Plan in Plan Operations
    to display Table, NumRows, Alias and QBlock.
28. Mouse over description on "Bug Fix Control Environment" section
    to indicate in title that source is V$SESSION_FIX_CONTROL.
29. New Observation for TABLE COLUMN: Candidate for NOT NULL
    constraint.
30. New Observation for TABLE COLUMN: Candidate for single-column
    UNIQUE index.
31. SQL Profile Hints for 11g are included in main report.
32. New utility sqltprofile.sql generates a custom manual SQL Profile
    for a known plan. This profile can be implemented on same or
    similar system.
33. Table of Content is now organized by section type.
34. AWR Hist SQL Statistics section includes a summary with one line
    by plan hash value and links to stats and plan below.
35. Trap error when other_xml is greater than 32767.
36. New link on "partitioned" column to navigate to table and index
    partitions.
37. Populate object# on plan table when missing (EXPLAIN PLAN FOR)
    and use it to navigate to Metadata for respective object.
38. Remove Metadata link from Objects section when object is a
    partition or when owned by SYS.
39. New Tool Configuration Parameters to better control DBMS_SQLTUNE:
    dbms_sqltune_scope, dbms_sqltune_time_limit and
    dbms_sqltune_report_level.
40. Fix "sqlt$r.display_file: File xxx.trc not found in directory
    SQLT$UDUMP" due to upper/lower case inconsistent names in traces
41. Deprecate Tool Configuration Parameter days_show_cbo_stats.

02/20/09 - 11.2.8
 1. Fix ORA-06502: PL/SQL: numeric or value error: character string
    buffer too small on "Other XML"
 2. Peeked Binds and Bind Variables for Latest Execution are shown
    now one after the next.
 3. Metadata link added to Explain Plan for Tables/Indexes/Views.
 4. Object dependencies is determined recursively instead of just 3
    levels down.
 5. Installed SQLT 11.0.1 or higher can now be upgraded to latest
    version of SQLT as per instructions.txt.
 6. Plan in plan_table is populated from GV$SQL_PLAN or V$SQL_PLAN
    or from DBA_HIST_SQL_PLAN in that order.
 7. Document in each HTML table which DB objects is being used (i.e.
    V$/DBA).
 8. Document with mouse-over unconspicuous columns like those from
    SYS.COL_USAGE$ and DBA_TAB_MODIFICATIONS.
 9. XECUTE method includes in main report the name of the script
    passed as inline parameter.
10. Correctly get peeked values even when XML format of OTHER_XML is
    not well constructed: <bind nam=":B4" pos="2" ... mxl="32"/>
11. Fix error below when NLS_NUMERIC_CHARACTERS not set to ".,"
    PL/SQL: numeric or value error: character to number conv error.
12. Spell out syntax to disable SQL Tuning Advisor or AWR due to lack
    of license of Tuning Pack.
13. ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR; consolidated now
    into one place: sqctab.sql.
14. XECUTE issues ALTER SESSION SET STATISTICS_LEVEL = 'ALL'; for
    non Linux platforms, and 'TYPICAL' for Linux (bug 7432590).
15. XECUTE method invokes TKPROF on generated 10046 trace.
16. XTRACT includes 11g SQL Monitor views GV$SQL_MONITOR and
    GV$SQL_PLAN_MONITOR when selected SQL is still executing.

01/12/09 - 11.2.7
 1. Clause TABLESPACE is now removed from metadata script for
    partitioned objects (tables and indexes). bug 7690799.
 2. COUNT(*) Threshold is raised from 1M to 10M.
 3. Last Starts column added to Explain Plan.
 4. View sqlt$_v$session add columns username and prev_hash_value.
 5. Performance improvement refreshing and using sqlt$_segments.
 6. UTL_FILE is granted to SQLTXPLAIN only if not already granted
    to PUBLIC.

12/27/08 - 11.2.6
 1. Limit the size of output from 32767 to 2000 bytes per line.
 2. Peeked Binds to display source: V$, GV$, AWR.
 3. XECUTE and XPLAIN methods recognize and display Stored
    Outlines and SQL Profiles associated with SQL.
 4. Include list of SQLT directories in log files for methods
    XPLAIN, XTRACT, XECUTE and COMPARE
 5. Fix error: sqlt$r.output_report: cannot display awr sql stats:
    ORA-01427: single-row subquery returns more than one row.
 6. Fix: unknown SET option "SQLBL", when SQLT was used from 8.0
    SQL*Plus client. This error caused very large SQLT files.
 7. 10053 trace file is now copied out of trace_dir instead of
    stage_dir.
 8. Workaround bug in DBMS_APPLICATION_INFO.SET_CLIENT_INFO not
    allowing NULL as parameter value.
 9. Log files include now RDBMS and Tool versions installed.
10. Installation of DBMS_SHARED_POOL is no longer mandatory.
11. Metadata inludes now referenced tables for BITMAP JOIN INDEXES
12. &&unique_id token is no longer required by sqltxecute.sql
13. Initialization Parameters section of setenv script shows now
    parameters with altered values.
14. SQL Statistics include now Parsing User and Optimizer Mode.
15. Improved performance of DBA_SEGMENTS snapshot.
16. Object Extents are now included in Objects section.

12/07/08 - 11.2.5
 1. Index section in main is moved below tables and above
    indexed columns.
 2. Script sqltxecute makes a ROLLBACK so in case the DML
    being analyzed is not a QUERY the data is not changed.
 3. Document "ORA-06510: PL/SQL: unhandled user-defined
    exception" in all scripts.
 4. Add SYSSTATS_INFO from SYS.AUX_STATS$ in Main report.
 5. v$session_fix_control only exists on 10.2.0.2 or higher.
 6. XTRACT method includes AWR SQL Stats and Plan.

11/17/08 - 11.2.4.1
 1. GRANT SELECT ON sys.v_$session_fix_control fails on 10.1.

11/15/08 - 11.2.4
 1. Metadata script consolidates schema_owner specification in
    customization section
 2. Exclude DOMAIN indexes from CBO Stats Observations
 3. Clustering Factor must be between 0.9 BLOCKS and 1.1 NUM_ROWS
 4. XPLAIN method requires now a confirmation to validate method.
 5. Include sqltxecute log on XECUTE method.
 6. Replace > and < by html tags in SQL Tuning Advisor section.
 7. Added GRANT EXECUTE ON sys.dbms_lob (needed for Windows)
 8. ORA-00001: unique constraint (SQLTXPLAIN.SQLT$_PARAMETER2_PK)
    violated during INSERT INTO sqlt$_parameter2 is fixed now.
 9. Compute and display "New Density" column.
10. Cost in plan table shows now if from SQLT or plan_table.
11. Include values of SYS.COL_USAGE$
12. XPLAIN and XECUTE methods include now input file into ZIP.
13. DBMS_XPLAN.DISPLAY_AWR is now only invoked by XTRACT method.
14. New section Optimizer Environment (as per 10053)
15. New section Bug Fix Control Environment (as per 10053)
16. COMPARE method handles sources with same tables/indexes but
    having different schema owners.
17. COMPARE method includes Optimizer Environment and Bug Fix
    Control Environment sections.
18. New setenv.sql file (includes OFE, Optimizer parameters and fix
    control setup)
19. New readme.txt file is now created by SQLTXPLAIN with
    instructions to create a test case, reproduce an explain plan
20. CPUSPEEDNW and OFE are now displyed in the Environment section
21. Child Plan section includes now the object owner.

10/18/08 - 11.2.3
 1. Paremeter use_gv$sql_views changes its default value from N
    to Y. In case of ORA-600 or 7475 you can disable GV$SQL views
    by using:
    SQL> EXEC sqltxplain.sqlt$d.set_param('use_gv$sql_views', 'N');
 2. Compare report includes now Table and Index Partitions.
 3. Script sqltimp displays now totals for partitions and subpart.
 4. Quote low and high values to ease spaces identification.
 5. Added estimated and actual performance in main and compare
    reports.
 6. Shows if a system is RAC and uses GV$ views instead of V$
    (if paremeter use_gv$sql_views is set to 'Y').
 7. Search Columns should only show value for indexes in Plan.
 8. DBMS_XPAN.DISPLAY_CURSOR() for 10.1 does not take format
    ADVANCED. It should get ALL instead.
 9. XTLITE includes now output of DBMS_XPLAN.DISPLAY_AWR
10. Include in Child Plan "Total Output Rows", "Buffer Gets,
    "Disk Reads" and "Elapsed Time in secs"
11. Materialized View are now included in metadata script and in
    metadata section of main html report.
12. New Observation message is System Statistics are missing.
13. Add "ALTER SESSION SET optimizer_features_enable" to metadata.
14. New script install/sqguser.sql to create grants for other users
    of SQLTXPLAIN after it has been installed.
15. Writes in log start/end of main SQLTXPLAIN procedures calls.
16. Improved customiztion section of metadata script.

10/06/08 - 11.2.2
 1. New config parameter "Use GV$SQL RAC Views" with default N
    This parameter disables GV$SQL views in SQLTXPLAIN which are
    known to cause ORA-600 and ORA-7445 in some instances.

09/21/08 - 11.2.1
 1. Compare report shows significant differences in red.
 2. Reincorporated script sqcdiru.sql to create pointer to UDUMP.
 3. Display indexed columns in plan even if they are part of
    a function-based index.
 4. Fix ocassional ORA-29284 doing UTL_FILE.FCOPY of trace file.
 5. Better error reporting when &&unique_id is missing on XECUTE
 6. Better error reporting when SQL is not found using XTRACT.
 7. Removed 10053 enable/disable and size limit config parameters.

09/06/08 - 11.2.0
 1. Materialize DBA_SEGMENTS to improve performance of tool
 2. Automatic gathering of CBO stats for staging tables
 3. Dependencies now includes objects in DBA_DEPENDENCIES
 4. New APIs to execute SQLTXPLAIN from within applications
 5. Some scripts in run directory got deprecated due to new
    APIs for XPLAIN, XTRACT and XECUTE methods
 6. Automatic export of SQLT staging objects for all methods
 7. Automatic generation of ZIP file with files created as
    last step of script execution for all methods

08/13/08 - 11.1.6.1
 1. Stored Outlines were not displayed in some RDBMS releases
    Method XTRACT displays now Stotred Outlines for given SQL

08/09/08 - 11.1.6
 1. Include referenced objects owned by SYS or SYSTEM
 2. New parameter to control if SQLTXPLAIN can be executed by
    SQLTXPLAIN, SYSTEM or SYS
 3. Improved performance while getting segment blocks from
    DBA_SEGMENTS
 4. Metadata disables now STORAGE and TABLESPACE instead of
    SEGMENT_ATTRIBUTES
 5. Script sqltxecute.sql to find the script in local directory
    instead of sqlt/run
 6. Export of CBO Stats includes now SYSTEM and SYS objects
 7. Metadata script includes DBMS_STATS.SET_SYSTEM_STATS calls
 8. CBO stats for fixed objects always exported into staging table
 9. New script sqltimpfo.sql to import CBO stats for fixed objects
10. Trap ORA-600 from Bug 4634662 and use then v$sql instead of
    gv$sql
11. Export of SQLT objects including CBO stats is now executed
    automatically out of sqltxrlite.sql when SQLTXPLAIN is used

07/19/08 - 11.1.5
 1. sqltxtlite.sql to display all child cursors for given sql_id.
 2. Indexes section, Operation ID column, to recognize when index
    name is the same as table name.
 3. New section "Indexed Columns" with a matrix showing which
    columns are used in which indexes.
 4. Section "Indexes" includes now column "Indexed Column Ids".
 5. System Statistics are now included in the Environment Section
    and in the compare report.
 6. Segment Blocks are shown in Tables, Indexes, Partitions and
    Subpartitions sections.
 7. New observation when CBO Blocks and Segment Blocks differ by
    more than 10%.
 8. Max index selectivity is included for all indexes.

07/02/08 - 11.1.4
 1. Added "ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR" to install.
 2. Use of pipelined functions to expedite file copy at the end.
 3. Fix false error "Missing APPS required Histogram. FND_STATS was
    not used to gather CBO Stats" triggered on 9i when num_buckets
    is 2. View dba_tab_columns shows one bucket instead of two.
 4. Select on gv$object_dependency matches to_address to from_address
    eliminating now false positives in dependent tables.

06/15/08 - 11.1.3
 1. Metadata script was considering undefined object types when
    replacing owner by && tag.
 2. Metadata script performs a SHOW ERRORS with object name included.
 3. All run scripts display correct error when tool is not installed.
 4. Metadata script includes enumerator on "Objects Stats" summary.
 5. SQLTXPLAIN calls now new TRCANLZR 11.2
 6. ALTER SESSION SET NLS is called at the start of all run scripts.
 7. DBMS_STATS.IMPORT_TABLE_STATS NO_INVALIDATE is set to FALSE now
    on import_cbo_stats.
 8. Explanation message for DBMS_STATS.UPGRADE_STAT_TABLE requirement

05/24/08 - 11.1.2
 1. Grant SELECT on V$SQL to application user to avoid sqltxecute to fail
 2. Fix Bug 7122982 ORA-06502 : PL/SQL: NUMERIC OR VALUE ERROR
 3. Split package SQLT$D into SQLT$D and SQLT$R

05/12/08 - 11.1.1
 1. Removed column_name from several indexes to avoid ORA-01450 on systems
    with small block size

04/25/08 - 11.1
 1. Set nls_numeric_characters to ".," to avoid ORA-01722: invalid number
 2. New observation when dependent table does not show in Explain Plan
 3. Include DBA_OBJECTS for SQL dependent object
 4. Method XECUTE displays GV$SEGMENT_STATISTICS for SQL dependent object
 5. Set of _optimizer_connect_by_cost_based to false in CONNECT BY sql
 6. ALTER SESSION commands for 10046 and 10053 are reorganized and lite
 7. Script sqltxecute validates now that tag &&unique_id exists in SQL passed
 8. Method XECUTE displays session statistics V$SESSTAT for dependent objects
 9. Method XECUTE displays wait events V$SESSION_EVENT for dependent objects
10. New tool configuartion parameters section replacing old control table
11. APPS init.ora validation: Added 11g and re-sync with 216205.1/396009.1
12. Tool version is now displayed in report title
13. Metadata script includes objects status for those created by script
14. Statistics import shows metrics of imported tables, indexes and columns
15. Handle user error of not including &&unique_id in script.txt (XECUTE)
16. Use hint _optimizer_connect_by_cost_based on tool's "CONNECT BY" queries
17. When SQLTXPLAIN is installed it displays now free space in tablespaces
18. Reinstallation shows prior default and temp tablespaces for SQLTXPLAIN

04/04/08 - 11.0.4
 1. Compare report now includes "last output rows" in addition to cardinality
 2. Table blocks and number of rows is displayed next to clustering factor
 3. Improved "end point" computed value for columns histograms
 4. Include frequency distribution for columns with histograms
 5. Grant execute to SQLTXPLAIN for: dbms_metadata, dbms_random,
    dbms_shared_pool, utl_file
 6. Add Density compare column under Table and Index Columns: 1/(Rows - Nulls)
 7. Incorporated staging table for dba_tab_histograms
 8. Table sqlt$_tables promoted from Global Temporary to permanent
 9. Calls to DBMS_METADATA use now set_transform_param to fully remove storage
    details

03/16/08 - 11.0.3
 1. Remove SQLTXPLAIN password from sqcusr.lis spool file
 2. Allow group_name as temporary tablespace name for user SQLTXPLAIN

03/10/08 - 11.0.2
 1. Handle sqlt$d.data_collection.get_sql_signature: ORA-06502: PL/SQL: numeric
    or value error

03/09/08 - 11.0.1
 1. XTRACT method can find now requested SQL even when elapsed_time and
    buffer_gets are both zero
 2. Expect SYS.DBMS_SHARED_POOL to be missing and handle error instead of exit
    installation
 3. Promote table sqlt$_plan_table from GLOBAL TEMPORARY to permanent
 4. Added SQL Statement, Explain Plan, Tables and Indexes discrepancies to
    comparison report

03/05/08 - 11.0
 1. Scheduled Job GATHER_STATS_JOB does not exist on 11g
 2. Pins SQL into memory on methods XTRACT and XECUTE so it can be analyzed
    before it ages out

03/01/08 - 10.7.4
 1. Grant execute on sys.dbms_metadata to SQLTXPLAIN and application user
 2. Scripts sqltxplain.sql and sqltxplite.sql accept now input files without
    any suffix (like sql1)
 3. Added Cost, IO Cost and CPU Cost to SQL Identification section of main
    report
 4. Added SQL Identification section to comparison report

02/23/08 - 10.7.3
 1. Enhanced documentation of all methods and scripts
 2. Cleaner exit of failed executions with better messages in log files
 3. Fixed wrong error ORA-31603: object "I_SNAP$_..." of type INDEX not found
    in schema
 4. Included a section on known RDBMS Bugs that may affect the SQLTXPLAIN in
    instructions.txt

02/18/08 - 10.7.2
 1. Enhanced metadata generated script to comment tablespace details on
    partitioned tables/indexes
 2. Fix complilation errors on 10.1.0.4
 3. Improved *.log and *.lis files

02/13/08 - 10.7.1
 1. Warn user that using DBMS_SQLTUNE requires a license for the Oracle Tuning
    Pack

02/07/08 - 10.7
 1. Methods XECUTE and XTRACT invoke now the SQL Tuning Advisor DBMS_SQLTUNE
    and report findings
 2. SQL Statistics V$SQL are now included for execution methods XECUTE and
    XTRACT
 3. Displays Stored Outlines when method XTRACT is used
 4. Shows SQL Profile hints if SQL Tuning Advisor recommends to accept a
    generated profile
 5. Observations section tells is an Stored Outline or SQL Profile is active
    for SQL
 6. Main Explain Plan includes now Rows in addition to Cardinality (method
    XTRACT and XECUTE)
 7. Incorporated Constraints section to main report

01/22/08 - 10.6.4

 1. Added export/import of CBO system stats
 2. New Compare report that shows differences in CBO Stats and Parameters for
    two executions of SQLTXPLAIN
 3. Added 1/NDV for Tabla and Index Columns. Also Diff % with respect to
    Density
 4. COUNT(*) is now performed up to specified threshold

12/18/07 - 10.6.3
 1. Improved Initialization Parameters section

12/04/07 - 10.6.2

 1. Fix Access and Filter Predicates in Explain Plan to display > and <
 2. Fix ORA-31600: invalid input value MATERIALIZED VIEW for parameter
    OBJECT_TYPE in function GET_DDL
 3. Fix incorrect observation for 9i: Missing APPS required Histogram.

11/08/07 - 10.6.1

 1. Fix ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

10/26/07 - 10.6

 1. Fix dependencies ORA-06502: PL/SQL: numeric or value error: Bulk Bind:
    Truncated Bind
 2. Partitions are now listed in order of Position instead of name
 3. Validation of directory paths in configuration script and log file
 4. Include directory paths into configuration section and udump into
    identification
 5. New parameters to reduce size of report: skip_part_column_stats and
    skip_subpart_column_stats
 6. SQLTXPLAIN can now be traced using new parameter debug_level_10046 with
    levels 0, 1, 4, 8 and 12
 7. Script sqcdir split into sqcdiru and sqcdirs to recreate UDUMP and Stage
    directories pointers
 8. Eliminated drop of synonyms from sqdtab.sql to avoid confusion
 9. Packages now verify that are compiled under SQLTXPLAIN schema and not any
    other
10. Creation scripts verify SQL*Plus is connected to expected schema owner (SYS
    or SQLTXPLAIN)
11. Automatic execution of sqltxplite or sqltxtlite from sqltxplain and
    sqltxtract

10/15/07 - 10.5.7

 1. Fix ORA-00942 "table or view does not exist" on
    sqlt$i.data_expansion.generate_10053
 2. Do not perform QUOTA UNLIMITED on temporary tablespace
 3. Capture directory creation error if name contains "?", '*" or "$"

09/21/07 - 10.5.6

 1. Tablespace references for partitions in metadata script are now commented
    out
 2. Improved performance selecting from DBA_PART_HISTOGRAMS and
    DBA_SUBPART_HISTOGRAMS
 3. Metadata routine detects now ORA-31603 and provides syntaxt to fix it using
    a GRANT
 4. Granting SELECT_CATALOG_ROLE to main application schema

09/14/07 - 10.5.5

 1. Lite version of XPLAIN and XTRACT scripts, using DBMS_XPLAN
 2. Improve documentation of release specific errors that are expected
 3. Add description of parameters for System Statistics

09/07/07 - 10.5.4

 1. Reinstall "ALTER USER xxx QUOTA UNLIMITED ON xxx;"
 2. Emphasize that SQLTXPLAIN should be run as the application user
 3. Tolerate if a semicolon ";" ends the statement in the text file

08/31/07 - 10.5.3

 1. Remove "ALTER USER xxx QUOTA UNLIMITED ON xxx;"

08/30/07 - 10.5.2

 1. Simplify signature of trcanlzr call anticipating new version of Trace
    Analyzer

08/23/07 - 10.5.1

 1. Handling error sqlt$d.output_report: cannot display identification1

08/17/07 - 10.5

 1. Fix error: sqlt$d.output_report: cannot display identification: ORA-06502:
 2. Fix error: sqlt$d.output_report: cannot display explain plan1: ORA-01489
 3. Fix javascript sporadic error "Unterminated string constant" on main report
 4. An update on sqlt$_control without a COMMIT was restricting concurrency.
    Fixed.
 5. Trace 10053 was truncated if a line on it was larger than 32767. Fixed now.
 6. Removed execution of 10046 for Xplain execution mode. Debug leftover.
 7. Reduce number of "skipped cbo stats for column" on sqltimp.sql using
    dba_tab_cols
 8. Peeked Binds extracted from DBMS_XPLAN are presented below Child Plans for
    Xtract
 9. Report CBO Stats History from extended DBA_TAB_STATS_HISTORY
10. Report CBO Stats History for indexes, partitions and subpartitions
11. Control length of CBO History reported using a new configuration parameter
12. Performance improvement of sqltxplain selecting from
    DBA_SUBPART_COL_STATISTICS

08/09/07 - 10.4

 1. Fix ORA-01722: invalid number ORA-06512: at "SQLTXPLAIN.SQLT$D", line 2963
 2. Show bind variable values (GV$SQL_BIND_CAPTURE) if executed by sqltxtract
 3. Display Data and Cache Stats Lock/Unlock flags for Tables and Partitions
 4. Include CBO System Statistics from SYS.AUX_STATS$
 5. Traverse Object dependencies to all levels down instead of just first one
 6. Detect and include into Observations if deprecated ANALYZE command was used
 7. Report errors within main html report in addition to display them in screen
 8. Present the syntax to export related CBO Stats in the Script sqltcprep

07/20/07 - 10.3

 1. Generate metadata script
 2. RAC enabled: Use GV$ views instead of V$
 3. Added 3rd method of execution and Trace Analyzer invocation
 4. Incorporate monitoring flag and ALL_TAB_MODIFICATIONS
 5. Include a summary on DBMS_STATS setup
 6. Show actual rows side by side with estimated cardinality
 7. COUNT(*) has now a threshold so is performed if num_rows <1M
 8. Indexed columns include count of indexes and leading indexes
 9. Limit the number of child plans to distinct hash_plan
10. Child plans show now filter and access predicates
11. Added object_id to several sections
12. Added column_id to indexed columns section
13. Workareas sizes are in Bytes
14. Added extra rules to Observations section
15. Date histograms now display value when date is truncated
16. Rename generated files to slqt_s...
17. If SQL (file or id) is not provided, exit sooner
18. Use of old PL/SQL synyax for INSERT INTO sqlt$_warning
19. Fixed typos on Configuration list and Depencency to Dependency

06/12/07 - 10.2

 1. Default for max_mb_10053 from 10 to 50
 2. Table names can contain now /
 3. Configuration can be displayed using new sqconfig.sql script
 4. New section with Observations (cross reference validation)
 5. New Tablespaces section
 6. UTL_FILE error detected
 7. EVENT 10053 copied from UDUMP

05/29/07 - 10.1.1

 1. Fix sqlt$d.output_report.utl_file.fopen: ORA-29281: invalid mode

05/25/07 - 10.1

 1. Fix UTL_FILE.PUT_LINE ORA-29285: file write error
 2. Print Tool Configuration section
 3. Include Predicates on Explain Plan 1st block
 4. Generate frames "f" file to ease navigation
 5. Links to Top and parent section
 6. Removed grants on execution of utilities
 7. Sequence sqlt$_statement_id_s start value

05/07/07 - 10.0

 1. Capability to export/import CBO stats
 2. Pass HASH_VALUE or SQL_ID as execution parameter
 3. Extract Plan from V$ static views
 4. Work Areas when SQL is provided by ID
 5. INIT.ORA parameters for APPS instances
 6. Required column histograms for APPS instances
 7. Display hidden columns for function-based indexes
 8. Fix some errors on SQLT index creation when referencing column_name
