Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Posts Tagged ‘PL/SQL Profiler’

SQLTXPLAIN (SQLT) 11.4.4.6 is now available

leave a comment »

So what is new in SQLT 11.4.4.6? Honestly, not much: one fix and 6 enhancements:

  1. BUG: SQLT TC “restore” was allowing a column to be renamed from “a” to “b” then from “b” to “c”, so it was ending up with duplicate rows in histogram among other issues.
  2. ENH: New tool parameter to control the automatic generation of a script with a Custom SQL Profile for best performing plan. Custom SQL Profiles based on DBMS_SQLTUNE.IMPORT_SQL_PROFILE are now inactive by default.
  3. ENH: New HC detecting corrupt histograms as per bugs 8543770, 10267075, 12819221 and 12876988.
  4. ENH: New HC reporting Tables where there are columns referenced in predicates and for which the number of distinct values has changed more than 10% from one statistics version to the next. These abrupt changes can cause an execution plan to also change.
  5. ENH: New HC about CBO not using partition level histograms if stats were gathered with “INCREMENTAL” and fix for bug 13583722 has been applied.
  6. ENH: Scripts roxtract.sql and roxecute.sql include now System Parameters with non-default or with modified values. They also report on all initialization paramaters for the connecting instance.
  7. ENH: All SQLT main methods ask now for the SQLTXPLAIN password in-line instead of using an ACCEPT command.

What is new, is that 3 satellite tools got also a new version. They can be downloaded from their own MOS note. They are also packaged within SQLT under directory sqlt/utl:

  1. Script: sqlhc.sql – SQL Health-Chech (1366133.1).
  2. Script: pxhcdr.sql – Parallel Execution Health-Check and Diagnostics Reports (1460440.1). NEW!
  3. Script: profiler.sql – HTML report out of PL/SQL Profiler DBMS_PROFILER data (243755.1).

Written by Carlos Sierra

June 5, 2012 at 10:40 am

Posted in Parallel Execution Health-Check and Diagnostics Reports, PL/SQL, SQL Healh-Check (SQLHC), SQLTXPLAIN (SQLT)

Tagged with , , ,

New profiler.sql script to nicely report top time consumers out of PL/SQL Profiler (DBMS_PROFILER) data

with 5 comments

PL/SQL Profiler has been around for more than a decade. With DBMS_PROFILER we can “profile” the execution of PL/SQL code. This is great when the poor performance happens in PL/SQL code and not necessarily due to SQL statements from those PL/SQL libraries. In such cases Trace/TKPROF are not the right tools for the job since they do not include PL/SQL commands.

Once we use PL/SQL Profiler in our application, we get as output some data in 3 Tables. This “profile” data is populated by the PL/SQL engine at the end of the “profile” run, or as we flush this “profile” data from memory.

If you are using PL/SQL Profiler and you want to produce a nice and concise HTML report that shows which are the top 5 code lines in terms of their execution time, then you may want to use the new version of the profiler.sql script out of MOS Doc ID: 243755.1. This document has been around for a decade or so, but I just re-wrote the profiler.sql script. This new and very improved version is available as of today.

Also, within the same document (note), you can find an output sample. This sample would give you some idea how it would look if used with your application libraries.

Written by Carlos Sierra

May 10, 2012 at 4:27 pm

Posted in PL/SQL

Tagged with