Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

SQLTXPLAIN 12.1.01 is now available

with 6 comments

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

  1. BUG: Incorrect date wrapping in report “Non-Default or Modified Session Parameters”
  2. BUG: SQLHC Table Summary reporting section was skipping tables if they had no index defined on them
  3. BUG: The logic in “Newest Value” column in Modified System Parameters needed to be adjusted, the value was sometime incorrect
  4. BUG: CBO Environment section was reporting a parameter many times if that param had been changed more than once
  5. ENH: SQLT and SQLHC works on 12c!
  6. ENH: Improved SQLHC HC on corrupt histograms for 12c
  7. ENH: SQLT can now create user SQLTXADMIN in presence of password complexity verification function
  8. ENH: Stat table creation now supports Unicode charset in both SQLT TC and SQLT TCX
  9. ENH: Importing SQLT TC now handles IOT remapping
  10. 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).
  11. ENH: Added “version date” to SQLHC to make it easier to recognize if an old version is used
  12. ENH: Added “user” to SQLHC reporting the db login user
  13. 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
  14. ENH: Added NUM_CPUs, NUM_COREs and NUM_CPU_SOCKETs info from V$OSSTAT to SQLT/SQLHC to help with multicore/multisocket CPUs
  15. ENH: Added Historical SQL Statistics – Total section to SQLHC
  16. ENH: SQLHC Exec now captures session statistics for the SQL analyzed
  17. ENH: New HC on Mviews not used in the SQL/plan that are defined on top of tables involved in the SQL/plan
  18. ENH: New column “If Offloadable” and “IO Saved %” on Exadata in Performance Statistics section
  19. 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

  1. BUG: Fixed issue with XPREXT and XPREXC where ASH was not turned off completely
  2. ENH: New health-check on plans with same PHV but predicates with different ordering
  3. ENH: SQLHC and SQLHCXEC now provides information about DBMS_STATS preferences
  4. ENH: SQLT shows when a parameter value has been changed (when this info is available in AWR)
  5. ENH: New link to Table Statistics Extensions directly available in the top menu

11.4.5.9 June 10, 2013

  1. ENH: New health-check on SDREADTIM/MREADTIM in Exadata
  2. ENH: Added SQLT parameter tcb_sampling_percent to control TCB sampling data percentage
  3. ENH: If no SQL is identified using the input string as HASH_VALUE then a search on PLAN_HASH_VALUE is performed
  4. 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
  5. ENH: Added scale information for TIMESTAMP datatype in Captured Binds sections
  6. ENH: ASH reports are now generated 1 per hour/instance starting from the interval where the SQL was most impacting
  7. ENH: SQLDX (SQL Dynamic eXtractor) collects only up to 10k rows per table/view

Written by Carlos Sierra

August 20, 2013 at 11:00 am

Posted in SQLTXPLAIN (SQLT)

6 Responses

Subscribe to comments with RSS.

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

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

  3. […] 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 […]


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,408 other followers

%d bloggers like this: