SQLTXPLAIN 12.1.01 is now available
Mauro Pagano is doing a great job keeping SQLT well taken care! Version 12.1.01 is now available for download out of MOS (Metalink) doc_id 215187.1. With a monthly release, it is always a good idea to download the latest version at least every quarter. This latest version includes some adjustments for 12c and some other changes as seen below. Here is the list of changes for the past 3 months. The saga continues!
12.1.01 August 19, 2013
- BUG: Incorrect date wrapping in report “Non-Default or Modified Session Parameters”
- BUG: SQLHC Table Summary reporting section was skipping tables if they had no index defined on them
- BUG: The logic in “Newest Value” column in Modified System Parameters needed to be adjusted, the value was sometime incorrect
- BUG: CBO Environment section was reporting a parameter many times if that param had been changed more than once
- ENH: SQLT and SQLHC works on 12c!
- ENH: Improved SQLHC HC on corrupt histograms for 12c
- ENH: SQLT can now create user SQLTXADMIN in presence of password complexity verification function
- ENH: Stat table creation now supports Unicode charset in both SQLT TC and SQLT TCX
- ENH: Importing SQLT TC now handles IOT remapping
- ENH: Fix for 12c
sqlt$a: *** d:ORA-12899: value too large for column “SQLTXPLAIN”.”SQLT$_DBA_TAB_COLS”.”LOW_VALUE” (actual: 45, maximum: 40). - ENH: Added “version date” to SQLHC to make it easier to recognize if an old version is used
- ENH: Added “user” to SQLHC reporting the db login user
- ENH: SQLDX is now executed after SQLT/SQLHC completes, it helps in case SQLDX takes a long time to run since the zip file for SQLT/SQLHC is ready to be moved even when SQLDX is still running
- ENH: Added NUM_CPUs, NUM_COREs and NUM_CPU_SOCKETs info from V$OSSTAT to SQLT/SQLHC to help with multicore/multisocket CPUs
- ENH: Added Historical SQL Statistics – Total section to SQLHC
- ENH: SQLHC Exec now captures session statistics for the SQL analyzed
- ENH: New HC on Mviews not used in the SQL/plan that are defined on top of tables involved in the SQL/plan
- ENH: New column “If Offloadable” and “IO Saved %” on Exadata in Performance Statistics section
- ENH: New HC on Rewrite Equivalences defined by the owner of the objects involved in the SQL/plan
11.4.5.10 July 15, 2013
- BUG: Fixed issue with XPREXT and XPREXC where ASH was not turned off completely
- ENH: New health-check on plans with same PHV but predicates with different ordering
- ENH: SQLHC and SQLHCXEC now provides information about DBMS_STATS preferences
- ENH: SQLT shows when a parameter value has been changed (when this info is available in AWR)
- ENH: New link to Table Statistics Extensions directly available in the top menu
11.4.5.9 June 10, 2013
- ENH: New health-check on SDREADTIM/MREADTIM in Exadata
- ENH: Added SQLT parameter tcb_sampling_percent to control TCB sampling data percentage
- ENH: If no SQL is identified using the input string as HASH_VALUE then a search on PLAN_HASH_VALUE is performed
- ENH: Introduced new parameter c_ash_hist_days to split AWR data collection from ASH data collection and to limit the amount of info extracted from ASH
- ENH: Added scale information for TIMESTAMP datatype in Captured Binds sections
- ENH: ASH reports are now generated 1 per hour/instance starting from the interval where the SQL was most impacting
- ENH: SQLDX (SQL Dynamic eXtractor) collects only up to 10k rows per table/view
Carlos Sierra's Tools and Tips 
Carlos,
we are trying to run SQLT XTRACT on exdata X3 and it took about 7 Hours on this step.
wondering if we can improve this as we are not able to get the reports in time
not sure if this is the right post to address this, wanted to get your view on this.
422 13:05:02 0 sqlt$a: tool version: 12.1.01
423 13:05:02 0 sqlt$a: script version: 12.1.0.1
424 INSTALL_DATE
425 ——————–
426 2013-08-21/15:53:57
826 13:18:40 0 sqlt$d: collect_dba_subpart_col_stats
827 20:37:4426344 sqlt$d: 1635528 rows collected
7 Hours 19 Minutes
SQL In question
SELECT t.owner,
t.table_name,
t.subpartition_name,
t.column_name,
t.num_distinct,
t.low_value,
t.high_value,
t.density,
t.num_nulls,
t.num_buckets,
t.sample_size,
t.last_analyzed,
t.global_stats,
t.user_stats,
t.avg_col_len,
t.histogram
FROM sqlg$_pivot x, sys.dba_subpart_col_statistics t
WHERE x.object_type = ‘TABLE’
AND x.partitioned = ‘YES’
AND x.object_name = t.table_name
AND x.object_owner = t.owner
Regards
Rama
rama.dasara@gmail.com
August 30, 2013 at 8:20 pm
c_gran_cols
EXEC SQLTXADMIN.sqlt$a.set_param(‘c_gran_cols’, ‘PARTITION’);
This is a workaround. You may have a performance issue on dba_subpart_col_statistics. Consider gathering data dictionary stats.
Carlos Sierra
August 31, 2013 at 8:35 am
thanks Carlos, will try that, we have a scheduled job to gather dictionary stats every weekend
Rama
August 31, 2013 at 7:48 pm
Please keep me posted. My email is carlos.sierra.usa@gmail.com
Carlos Sierra
August 31, 2013 at 7:54 pm
Good evening, Carlos. I am very interested in using the SQLT tool. I received the e-book as a birthday present and assumed that it was freely available. However, as a contractor, I do not have direct access to Oracle support. Is it possible to obtain this zip package another way in order to familiarize myself with this much-talked tool and put it to use?
Pedro Ruiz
October 3, 2013 at 12:07 am
[…] you might already know I am one of the fans of SQLT tool developed by Carlos Sierra. And here below what this tool said about redundant indexes in […]
Redundant Indexes | Mohamed Houri’s Oracle Notes
March 24, 2014 at 7:28 am