Oracle tuning assignments can be pretty easy

Blog Posts Videos

Some things you probably didn’t know about Oracle tracing (Chicago edition)

Today, I had the honor of speaking online to the Chicago Oracle Users Group in their “20 in 2020” webinar series. In this presentation, I talk about why it’s OK to trace everything on your system and how to do it safely, and I explain why you’d want to do that in the first place.

Some things you probably didn’t know about Oracle tracing

Method R for capacity planning

The practical approach Cary explained for capacity planning brought to light a whole other application for the tool.

Nancy Spafford · DBA and Tech Lead · Lawrence Livermore National Laboratory · Livermore, California
Release Announcements

Workbench 9.1

Method R Workbench 9.1 adds bug fixes and exciting new features to 9.0. New audio feedback makes it easier to know when reports finish, reorganized menus add features and make things easier to find, new copy and paste capability makes it easier to pull data from the files pane into reports and emails, and new Select › Inverse feature makes it easier to manage lots of files.

Workbench Release Notes


  • 8172: Fixed a problem causing some mrcrop error messages to be formatted inconsistently.
  • 8170: Fixed a bug that would cause the string “null” to be appended to the file name when using File › Save Output….
  • 8166: Fixed a bug that caused abnormal termination of mrprofk and other utilities when using a trial license key.
  • 8164: Moved all installation prerequisites and troubleshooting documentation to the mrworkbench man page, at
  • 8163: Moved prerequisite documentation from a web page to the mrworkbench man page, and improved the installation troubleshooting advice on the mrworkbench man page.
  • 8162: Added to the mrprof man page that the default value of the –trace option is the empty string.
  • 8160: Improved the Method R Workbench for Windows dialog text when WinRun4J cannot find a suitable JVM.
  • 8159: Improved error message formatting.
  • 8157: Fixed a bug causing the “%.3f seconds” message at the end of an erroneous action execution to concatenate improperly with the error message.
  • 8154: Added a new “Cut output” feature (Edit › Cut Output and a new button) for the output pane.
  • 8152: The Workbench application now saves the state of the most recently selected item in the actions pane, so that when you start the application, the action that was selected at the end of the previous session is selected at the beginning of a new session. Using View › Clear Application will reset the selection to “Duration by call name”.
  • 8151: Reorganized the Workbench application menus to make features easier to find and execute.
  • 8148: Removed the Edit › Clear Output and Files menu option, which didn’t work properly. Replaced with a new View › Clear Application option, which clears the files pane, files filter, actions filter, and output pane with one click.
  • 8141: Eliminated the confusing Reload and Unload right-click options in the files pane.
  • 8139: New right-click option for any value in the files pane makes it easy to copy the value under the cursor for easy pasting into an emails, reports, etc.
  • 8136: Fixed a bug that would cause mrskew not to recognize the –listrc option when used in conjunction with the –noinitrc option.
  • 8121: Workbench application now uses audio feedback to notify the user of action completions, errors, and so on.
  • 8103: Added two new reports to the actions pane: “Duration by PARSE ID” and “Duration by call name for given PARSE ID value”.
  • 8091: New Select menu options give another means to access the Select › All and Select › None functions, and the new Select › Invert function allows you to quickly invert your visible files pane selections.
Workbench Release Notes


  • 8142: Fixed a bug that could cause an empty argument value to be processed improperly in mrskew commands on Microsoft Windows.
  • 8134: Fixed a bug that could cause quotes to be processed improperly in mrskew commands on Microsoft Windows.
  • 8130: The application now prepends the start time of an action execution to the name of the action in the output pane. The time consumed by the action now prints after the final row of the action’s output.
  • 8126: The Workbench application now pops a dialog when you’re getting close to your license expiration date.
  • 8120: Fixed a bug that prevented Workbench from automatically restarting after an upgrade.
  • 8115: Renamed “Duration by call begin and end times” to “Duration by call begin TIM and end TIM”, and added --top=0.
  • 8113: Fixed a column alignment problem with “Duration by EXEC ID, SQL ID, and placeholder value set” report format.
  • 8105: Tool tip text for Directory and File values now shows the full name of the directory or file, which helps especially when a restricted column width prevents you from seeing the full name.
  • 8104: Fixed a bug that would mistakenly allow the Workbench application to load a .trc.gz file.
  • 8102: Standardized quoting in predefined action commands.
  • 8096: Fixed the mrskew man page description of the --pform and --csv options.
  • 8090: Fixed a bug where mrcrop linerange might not correctly copy the SQL statement text for a PARSE ERROR.
  • 8086: Fixed a bug for Workbench on macOS that would cause the 10 MB Java library folder to be duplicated instead of linked symbolically, when upgrading from version 9.0.1 or earlier.
Workbench Release Notes


  • 8085: Improved handling of nested syscalls, which eliminates some false-negative TRCBUG2 warnings.
  • 8083: Fixed an exception that could occur when mousing over the white region to the right of the rightmost column header.
  • 8082: Fixed a bug that could cause a Workbench for Windows utility to throw a “Could not find or load main class…” error if the product installation pathname contains whitespace.
Release Announcements

Workbench 9.0

Oracle trace files give you incredibly rich, high-resolution performance information, but tracing creates so… much… data. You can’t possibly manage it all without great tools. Method R Workbench 9 makes working with trace data quick and easy, even if you’re staring down thousands of trace files.

Workbench 9—a whole new way to see Oracle performance
Workbench Release Notes


  • 8119: Fixed a Check for Updates… bug that prevented Windows users from upgrading Workbench to Workbench The workaround is to download Workbench 9 from the Method R downloads page.
  • 8071: Rearranged the mrskew manual page to make the packaged .rc files section easier to find.
  • 8067: Fixed a bug causing the Workbench application not to check for updates upon startup in cases when it should.
  • 8066: The Workbench application for Microsoft Windows now resists accidental concurrent instantiations of the same version.
  • 8063: New mr1011 utility converts Oracle Database version 10.2 trace files into version 11.1 format, which enables Method R Workbench to process 10gR2 trace files.
  • 8060: mrprof now processes all data in a multi-session trace file; the –ignore-multiple-sessions and –ignore-multiple-sessions-file options have been eliminated.
  • 8059: New column names and tool tips in the files pane make it easier to understand your data.
  • 8054: Added new mrwhen reports to the actions pane.
  • 8053: mrprof now writes output printed by the –trace option to a file called *.trace.
  • 8046: Fixed a bug causing Workbench not to open the output HTML file when using the mrprof –trace option.
  • 8044: Major advisory text improvements throughout the Method R Profile report.
  • 8041: Fixed a bug that caused the Workbench application to make too many check-for-updates calls.
  • 8038: Added “RTs” column, which shows the file’s network round-trip count.
  • 8025: Workbench on macOS now automatically detects both JRE 8 and JDK 8.
  • 8021: Workbench now writes the Java home directory and version number information into the application log file.
  • 8016: Optimized mrprof memory allocation for cases in which the input trace file has many thousands of distinct SQL texts.
  • 8013: Fixed a bug where mrprof would fail to record why it could not load an extremely large XML file.
  • 8000: Workbench now uses parallel execution to load trace files into the files panel. This reduces file load response times on multi-core systems. The degree of parallelism is configurable in Preferences › Profiler › Workbench Parameters › Use this many parallel profilers.
  • 7995: Renamed “Report Parameters” dialog to “Action Parameters”.
  • 7994: Field labels in the “Action Parameters” dialog now use sentence case.
  • 7992: Made cosmetic improvements to the “Action Parameters” dialog.
  • 7990: Eliminated the “Details by file” action, which has been replaced by the faster and higher-precision “Copy selected file rows to output” button.
  • 7988: Alphabetized the action name list in the application’s actions pane.
  • 7987: The “Action Parameters” dialog now has field hints to help with more complicated input formats.
  • 7985: Created new manual page for mrworkbench, which includes JRE troubleshooting advice.
  • 7983: “Action Parameters” dialog now remembers the most recent value you’ve used for each parameter during a given invocation of Method R Workbench.
  • 7974: Removed the unimplemented –compression-formats option from the mrprof man page.
  • 7969: Fixed a bug that can cause mrprof to re-profile input files more often than necessary.
  • 7954: mrprof now makes it easier to see hierarchical relationships in Profile by Cursor and Execution Plan sections, by using numbers instead of dots to denote hierarchy levels.
  • 7952: Fixed a bug that would cause mrprof to print all the execution plans for a given statement in each section 4.*.5.*, instead of just one plan per section.
  • 7946: Removed “can’t evaluate” message explanation from the Exit Status section of the mrskew man page. This message no longer applies to the newly rewritten mrskew utility.
  • 7938: Changed mrprof –ofile %s specifier to %b (because it represents the file base name), which is consistent with the syntax of the new mrcrop utility.
  • 7934: Fixed a bug that in which mrprof could die instead of issuing a warning.
  • 7932: New Preferences › Profiler › Workbench Parameters › “Auto-open HTML when loading one or two files” option replaces the old and less useful “Open up to this many HTML files per load” option.
  • 7908: References to undefined variables formerly caused mrskew to emit an error and die. Now, the value of an undefined variable reference is the empty string, and mrskew will not throw an error.
  • 7906: Advisory text now contains hyperlinks directly to Method R Workbench utilities manual pages and the Mastering Oracle Trace Data book.
  • 7902: Fixed an HTML validation error.
  • 7901: Added execution count to the Profile by Cursor section.
  • 7899: Added execution count to the Profile by Caller sections.
  • 7898: Added execution count to the Profile by Statement sections.
  • 7892: mrtimfix is now obsolete and no longer shipped. mrtimfix was useful only for systems prior to Oracle Database 11.1 (which we no longer support), and 11.1 systems suffering from Oracle bug 7522002 (which was patched long ago).
  • 7891: mrtim is now obsolete and no longer shipped. It is replaced with the much more useful mrwhen.
  • 7886: A new horizontal scroll bar makes it easier to use all the new columns in the application’s files pane.
  • 7883: Documented how mrskew uses the PERL5LIB environment variable.
  • 7870: Renamed mrskew –glabel and –slabel options to –group-label and –select-label.
  • 7860: New mrskew $base_name and $dir_name variables make it faster and easier to reference parts of a file name.
  • 7851: Created a manual page for mrprofk.
  • 7838: Improved the advisory text in the Profile report for synthetic calls, and improved Synthetic Calls documentation in the mrprof manual page.
  • 7833: Eliminated the –eula option. Users can find the license agreement information in each utility’s manual page.
  • 7828: Standardized the processing of options used by every utility. Now, –help, –license, –man, and –version all behave consistently, and command line errors are processed consistently across utilities.
  • 7813: Fixed a bug that would spin the application’s “Processing” busy indicator forever when a command line option’s quotes don’t match.
  • 7803: Removed mrskew –timunit, –cpuunit, and –trcunit options and the $cpuunit and $timunit variables.
  • 7801: Documented the mrskew %calls ($calls{key}) variable.
  • 7800: mrskew is now more 10–20× faster than before.
  • 7799: Consolidated and simplified error reporting for .rc file names not found.
  • 7798: Deprecated mrskew –rc=mrnl.rc. To see when calls begin and end, use the new mrwhen utility.
  • 7796: Changed the XML schema for reports.xml so that now label is an attribute of the diagnostic element (instead of a child element).
  • 7788: Method R Workbench now uses LaTeX to render more beautiful PDF manual pages.
  • 7784: Moved mrls START-TIM and END-TIM columns to a more logical place in the output.
  • 7782: mrls –debug option now writes debugging information to stderr.
  • 7781: Renamed mrls TIM0 and TIM1 columns to START-TIM and END-TIM.
  • 7780: New mrls –tags-dbfile option allows you to specify which SQLite database to use for retrieval of TAGS information.
  • 7777: New mrls –include=:all and –exclude=:all options obviate the old –none and –details options.
  • 7775: Added new mrls STATUS column values.
  • 7774: Replaced the old mrls options like –cpu and –nocpu for including and excluding columns with new –include and –exclude options.
  • 7772: The mrls –sort option now takes a column heading (e.g., DURATION, SIZE, VERSION, etc.) as its argument.
  • 7763: mrls now dies if you use –sort on a field and then exclude that field from the output.
  • 7762: mrls is now more than 20× faster than before.
  • 7759: Deprecated the mrls –scanmax option; to request full-precision output, use –include=DEP or any of the other columns that force mrls to read the entire input file.
  • 7755: Reduced the size of the distribution by storing the sqlite3 load library in the distribution’s file system instead of the sqlite jar file.
  • 7752: Made significant, sweeping changes to mrls column display interface and manual page.
  • 7746: mrls now displays “?” instead of negative number for unknown recursive depth values.
  • 7739: mrls no longer uses a complicated algorithm based on the file’s modification time to try to guess the START value for a file. Now it simply warns when there is no timestamp in the input trace file.
  • 7737: Removed under-utilized mrls options: –trcunit –timunit –cpuunit –precision –human –dformat –tformat –units
  • 7722: Now you can use mrls -r as an alias for mrls –reverse.
  • 7719: Documented in mrprof man page that mrprof works only with extended SQL trace files generated by Oracle Database 11.1 and newer.
  • 7718: Fixed a bug that would cause a mrprof command line execution to fail if the Method R Workbench application had not yet been opened since the most recent installation or upgrade.
  • 7695: Fixed a bug where starting the app might result in the window not being visible.
  • 7692: Fixed a bug that could cause a file to have duplicate rows in the files pane.
  • 7688: Fixed a mrprof bug that could cause a statement to be identified as “MRPROF-ERROR-0004” in the Profile by Cursor section.
  • 7683: New, simpler application “Show Details” button image.
  • 7678: Method R Workbench 9 is certified to run on macOS 10.15 Catalina.
  • 7674: New –recursive (-R) option gives you control over whether mrprof plunges into the directories it finds.
  • 7654: New application sortable ID column lets you sort files in the order they will be processed by the automatic profiler.
  • 7653: Fixed a bug that could cause the “Copy selected file rows to output” button not to write information into the output pane.
  • 7648: Changed status bar labeling from “filtered” to “hidden” on files pane and actions pane.
  • 7635: mrskew now reports the list of files that matched the –where criteria.
  • 7630: Fixed a bug that caused mrskew to calculate $ssqlid incorrectly when a “***” datestamp line appears within a PARSING IN CURSOR section.
  • 7629: Application now shows “All files are hidden” instead of “Drag trace files here or load sample files” when files are loaded but hidden.
  • 7626: Fixed a bug causing mrprof to emit unnecessary text to stderr when using –nohtml.
  • 7624: Fixed a bug that could cause mrskew and mrprof to report incorrect shared SQL ID values.
  • 7621: New File › Export… and Import… functions make it easy to transport files from one Method R Workbench user to another.
  • 7607: New mrwhen command shows what time a trace file line or a specified tim value maps to for a given input file.
  • 7599: mrprof now reports the total call count in the Profile by Statement sections.
  • 7594: Workbench now remembers your selected sort order when you restart it.
  • 7592: Added a new “Duration by database block address (DBA) for /db.*read/ calls” action.
  • 7589: Added several new mrcrop commands to the predefined actions pane.
  • 7588: Enhanced “Reload selected files” tool tip to note that you can shift-click to perform a force-reload.
  • 7587: Fixed a bug that could cause the Workbench application not to automatically re-profile an input file that had been profiled by an older mrprof version.
  • 7561: Fixed a bug causing mrprof to sometimes not open the HTML file it creates when using XML input.
  • 7559: mrprof now recognizes -? as a synonym for –help.
  • 7551: mrprof now preserves the existing formatting for multi-line SQL and PL/SQL text.
  • 7544: Execution plan tables now include the row source start count.
  • 7528: mrprof now exposes the str (number of starts) statistics for row source operations.
  • 7525: Fixed a bug causing advisory text to render “·” when it should render “0.0%”.
  • 7523: mrprof new recognizes the LOBARRTMPFRE database call.
  • 7517: Removed mrcallrm from the distribution, because the new mrcrop utility accomplishes the goals of mrcallrm and more.
  • 7485: New pause/play button gives user control over whether mrprof runs during file load operations.
  • 7471: Eliminated the requirement to set the environment variable called MRWORKBENCH_INSTALL.
  • 7459: Method R Workbench can now be pinned to the Microsoft Windows taskbar.
  • 7402: Refined the mrprof clock drift detection algorithm.
  • 7302: Pressing the “Copy output” button when the output pane is empty no longer clobbers the clipboard.
  • 6257: New mrcrop tool crops user experience data out of an undesirably too-large trace file, or splits user experience data into separate standalone trace files.
  • 3993: Corrected highlighting of literal values in long SQL text.

No more Sherlock

…And under one minute I was looking directly at the problem. No more guesswork. No more Sherlock with the magnifier goofing around. And the time spend in search of the problem can easily be used to find a better solution.

Eduard Turea · Frostbyte Consulting · Calgary, Alberta CA