Carlos Sierra's Tools and Tips

SQLTXPLAIN (SQLT) 11.4.4.4 was made available on April 2, 2012. Find this tool under MOS 215187.1. It contains 2 fixes and 37 enhancements:

Fixes:

1. Peeked and Captured Binds in Execution Plan of MAIN was showing :B1 where predicate was “COL=:B10” (false positives). Fix display of bind peeking and bind capture when SQL contains binds like :b1 and :b10.
2. Metadata script includes now creation of Statistics Extensions.

Enhancements:

1. New HC when derived stats are detected on a Table or Index (GLOBAL_STATS = ‘NO’ and PARTITIONED = ‘YES’).
2. New HC when SQL Plan Baseline contains non-reproducible Plans.
3. New HC indicating sizes of SQL Plan History and SQL Plan Baseline (enabled and accepted Plans).
4. New HC when there is an enabled SQL Profile and there are VPD policies affecting your SQL. Plan may be unstable.
5. New HC when there is more than 1 CBO environment in memory or AWR for given SQL.
6. New HC when Indexes or their Partitions/Subpartitions have UNUSABLE segments.
7. New HC when Indexes are INVISIBLE.
8. New HC when an Index is referenced in a Plan and the index or its partitions/subpartitions are now UNUSABLE.
9. New HC when an Index is referenced in a Plan and the index is now INVISIBLE.
10. New HC when Table has locked statistics.
11. New HC when INTERNAL_FUNCTION is present in a Filter Predicate since it may denote an undesired implicit data_type conversion.
12. New HC when Plan Operations have a Cost of 0 and Cardinality of 1. Possible incorrect Selectivity.
13. New HC when SREADTIM differs from actual db file sequential read for more then 10%.
14. New HC when MREADTIM differs from actual db file scattered read for more then 10%.
15. New HC when BLEVEL has changed for an Index, an Index Partition or an Index Subpartition according to statistics versions.
16. New HC when NUM_ROWS has changed more than 10% for a Table, a Table Partition or a Table Subpartition according to statistics versions.
17. New HC when Index is redundant because its leading columns are a subset of the leading columns of another Index on same Table.
18. New HC when leaf blocks on a normal non-partitioned index are greater than estimated leaf blocks with a 70% efficiency.
19. Active Session History sections on MAIN report include now up to 20 sessions and 20 snapshots (it was 10 and 10).
20. Parameter _optimizer_fkr_index_cost_bias has been added to SQLT XPLORE.
21. SQLTPROFILE and script coe_xfr_sql_profile.sql take now SQL statement with SQL Text larger than 32767 characters.
22. Add metrics similar to what we have now on summary tables/indexes on SQLHC.
23. Tables and Indexes sections on MAIN contain now links showing object counts instead of a constant. Similar to SQLHC.
24. Execution Plans on SQLT to show with mouse-over, schema statistics for both: current and as per plan timestamp.
25. Add new columns on 11.2.0.3 for all V$, GV$ and DBA views accessed by SQLT.
26. Include reason WHY a cursor is not shared (out of XML “reason” column on gv$sql_shared_cursor).
27. MAIN report heading includes now a link to MOS SQLT document.