How to get an extended SQL (event 10046) or CBO (event 10053) trace file with a click of a button?

It’s often a chore to generate and fetch trace files. You usually have to enable the trace, run your SQL, then go to the DB server’s file system, locate the correct trace file and then find the relevant portion in that trace file.

In Mumbai you can generate extended SQL and CBO trace files with only one button click. The only requirement is that you installed the “Mumbai42” schema into the target database. This schema holds the necessary PL/SQL code to fetch trace and log files from the file system of the DB server through SQL. To install the Mumbai42 schema, connect to the target database and click the button on the Connection tab in the main window.

You will be asked for a password for the Mumbai42 schema user and, if the password is not saved within Mumbai, for the password of the sys user, so that Mumbai can create the Mumbai42 user.

After that you are all set for the one-click trace file retrieval. Open a console window, enter your SQL and click on “Tracing > Execute Query and retrieve 10049/10053 trace file”.

Mumbai will change the Oracle tracefile_identifier before and after executing your SQL, so you end up with a trace file in Mumbai that only shows entries relevant for the execution of your SQL, not a long trace file with previous entries that you have to wade through.

If this is an extended SQL trace file (event 10046), you can open the trace file or just the selected lines in Mumbai’s extended SQL trace file viewer from the menu at the top. For CBO trace files there is no viewer in Mumbai available (yet), so the generic trace file viewer as seen in the image above is all there is for now.

If the one-click solution doesn’t meet your needs the Tracing menu in the console window (see second image above) has all necessary entries to the tracing and trace file retrieval in your own customized way. E.g you might want to have trace file entries from the execution of multiple SQL statements in one trace file. In that case can you use the “Start/Stop tracing”, “Set tracefile identifier” and “Open session’s trace file” entries accordingly.

This entry was posted in Mumbai. Bookmark the permalink.

One Response to How to get an extended SQL (event 10046) or CBO (event 10053) trace file with a click of a button?

  1. Pingback: How to peek into the alert.log file? | Marcus Mönnig's Oracle and Mumbai Blog

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