Home Consulting
                          Services Publications MERITS Profiler RmanJ
Contact


Sample MERITS Profiler Report
(download all diagnostic data that the MERITS Profiler generated in conjunction with the report above here)
User Guide
 





The MERITS  Extended SQL Trace ProfilerEdit
The MERITS Profiler is the next generation SQL trace analysis tool. It not only generates accurate resource profiles at trace
file, SQL or PL/SQL statement, as well as module and action levels but also has a unique real-time mode that allows it to analyze extended SQL trace files
as they are being written by an active database server process and to automatically correlate the information therein with V$
dynamic performance views and the data dictionary. The detection of think time is yet another unique feature of the Profiler.
The MERITS Profiler even contains a parser for the SQL language that allows it to recognize and aggregate similar SQL statements in the same way as the ORACLE DBMS with cursor_sharing=similar.
The MERITS Profiler is available as a commerical ORACLE database performance diagnostic tool with an incident-based support model that includes software upgrades, enhancements and bug fixes. Instructor-led training is also available and is highly recommended for database administrators that want to become aquainted with the software and advanced ORACLE DBMS tuning in gerneral.

Read an introduction on the MERITS Profiler here (PDF).

For further information and to request a trial license please contact us by phone or e-mail (contact details)

Features:
  • Resource profiles at trace file, SQL statement as well as module and action levels (i.e. support for software instrumentation with DBMS_APPLICATION_INFO and JDBC End-To-End Metrics)
  • Database call statistics (for PARSE, EXEC, FETCH, CLOSE) at trace file, SQL statement as well as module and action levels
  • LOB operation statistics (for 11g Release 2 LOB operations LOBFILCLOSE, LOBFILOPN, LOBGETLEN, LOBREAD, LOBTMPCREATE, LOBTMPFRE, LOBWRITE)
  • Wait event histograms with varying units for optimum legibility (┬Ás, ms, s) and cumulative values for each histogram bucket
  • Row prefetch histograms for each SELECT statement with detailed information on rows fetched per FETCH call, average elapsed time per FETCH call and prefetch size, etc.
  • Top statements executed by the client (dep=0)
  • Response time per recursive call depth level
  • Top SQL statement statistics per module and action
  • Configurable classification of SQL*Net message from client as the synthetic wait event "think time" to identify periods of non database related activity by the client
  • Statements ranked by database service time, i.e. excluding think time
  • Buffer busy waits reported per data file and block if they exceed a threshold
  • Execution plans with resource consumption by row source derived from the cumulative resource consumption in the trace file (both are reported) for easy identification of the most costly row sources in an execution plan
  • Support for multiple execution plans per statement
  • Aggregation of similar statements that differ only by literals (based on a built in parser for SQL syntax)
  • Recursive descendants and recursive elapsed time for each statement
  • Physical reads by database object including the average multi block read size per segment
  • Bind variable data types and values for a configurable number of executions
  • Optional session-level Statspack snapshots at the beginning and end of the measurement interval provide access to session time model data and session statistics
  • Optional AWR snapshots at the beginning and end of the measurement interval (the MERITS Profiler itself can enable extended SQL trace in a session). When enabled an AWR and ASH report covering the same measurement interval as the SQL trace file are automatically generated
  • Trace file can be read over the Oracle Net connection (no need for file transfers from the database server)
  • HTML and plain text report formats
  • The MERITS Profiler itself can turn SQL trace for a session on and off at any level using DBMS_MONITOR
  • The Profiler can also temporarily set statistics_level=all to gather additional information using DBMS_XPLAN
  • Counter for ERROR entries (Oracle server ORA-nnnnn) errors
  • Parser debug mode generates a normalized version of the SQL trace file where each database call is reported including the SQL hash value and tim values are converted to a human readable date and time
  • Extensive correlations in real-time mode (license required):
    • SQL ID reported for Oracle10g trace files which lack the SQL ID in addition to statement hash value and old Oracle9i hash value for integration with AWR (which uses Oracle10g SQL ID) or Statspack (which uses Oracle9i old hash value)
    • CBO (cost based optimizer) statistics for table and index segments referenced by a trace file
    • System statistics (CPU speed, MBRC, etc.)
    • Initialization parameters
    • Table and index structure
    • Estimated space efficiency identifies fragmented tables
    • Buffer cache contents
    • Results of DBMS_XPLAN.DISPLAY_CURSOR for traced statements with much more details on execution plans than available in SQL trace files
    • Information on SQL profiles, stored outlines, optimizer environments, etc. from V$SQL_PLAN
    • Details of each referenced optimizer environment detailing which statement used what optimizer settings
    • User IDs in trace files resolved to user names
    • Database object IDs resolved to segment names