oracle-developer.net

profiling trace files with preprocessor external tables in 11g

External tables were introduced in Oracle 9i but have had several new features added through the major releases of 10g and 11g. In particular, Oracle 11g added a range of support for compression, both in reading and writing data with external tables. The external table preprocessor (released in the 11.1.0.7 patchset) was possibly developed to enable the reading of compressed files but as we will see in this article, it can be used for other types of preprocessing tasks. In particular, we will be using the preprocessor to generate and read TKProf and OraSRP reports from trace files, all within SQL*Plus (or any other IDE of choice, such as SQL Developer, TOAD etc).

For a full description of the external table preprocessor and its capabilities, there are two oracle-developer.net articles available:

Readers who are unfamiliar with the external table preprocessor feature should read the articles listed above. Some of the concepts described in them will not be repeated in the following article.

oracle directory setup

As with any external table, we need to begin by creating an Oracle directory to read our external files. As this article is related to generating profiler reports from extended SQL trace files, we need to create a directory over the user dump destination, as follows.

We also need to grant our user both READ and WRITE privileges on this directory (READ to enable reading both the raw SQL trace files and the profiler reports and WRITE to generate the profiler reports), as follows.

SQL> GRANT READ, WRITE ON DIRECTORY trace_dir TO scott;

Grant succeeded.

In addition to a "standard" read/write directory, we also need an executable directory for our preprocessor scripts (as already stated, the background reading has full descriptions of all preprocessor concepts).

SQL> CREATE DIRECTORY bin_dir AS '/app/oracle/bin_dir';

Directory created.

SQL> GRANT EXECUTE ON DIRECTORY bin_dir TO scott;

Grant succeeded.

The executable directory is created in the same way as any other, but this time we have granted an EXECUTE privilege to our user. The executable directory will contain the preprocessor scripts needed to generate and read the profiler reports. Naturally, this has security implications, as described in the recommended reading and we'll assume for this article that the /app/oracle/bin_dir directory is strictly controlled.

generating and reading tkprof reports

Now that we have our Oracle directories, we can connect as our user and create an external table to generate and read TKProf reports, as follows.

As highlighted, this is a preprocessor external table. Its data source is not a flat-file, but rather the output of the preprocessor script highlighted on line 13. The location clause on line 20 is unnecessary at create-time, but before we can query the external table, we'll need to provide a valid trace file (which we'll see later).

The preprocessor script that supports this external table is very simple, as follows.

Obviously, this is a shell script for a *nix system but is simple to convert to a Windows batch-file equivalent. The preprocessor script simply generates and reads a TKProf report for the trace file provided as parameter $1 (as noted in the background reading, this $1 parameter is the name of the file set as the external table's location).

And that's it! We've generated and read a TKProf report without having to leave our SQL*Plus window. This is quite powerful and can be used in all IDEs. For SQL*Plus users, the pause and pagesize settings can make the analysis of the output easier. In grid-based IDEs such as SQL Developer, the output can easily be scrolled within the data grid.

suppressing the tkprof banner

Note lines 1-10 in the TKPROF_XT resultset above. We can see the TKProf banner twice and the reason for this is simple. The first occurrence of the banner is the stdout of running the tkprof utility (i.e. generating the TKProf report) and the second occurrence is the heading of the resulting report. To suppress the first occurrence of the banner, we can change the tkprof.sh script to redirect the stdout to /dev/null as follows.

Because we've altered the table (rather than drop and re-create it), its location will still be set to our sample trace file. We will query the first 10 rows to demonstrate the removal of the first banner as follows.

avoiding multiple tkprof executions

So far in this article, we've generated the same TKProf report twice (compare the timestamps at the top of each of the resultsets). This is a shortcoming of the method so far because larger trace files will take time and resources to profile. To avoid generating the TKProf report every time we query the TKPROF_XT external table, we have two options:

we can change the preprocessor script to skip the TKProf step if the profile report already exists; or

we can create a read-only external table to read the profile reports once they have been created.

We'll look at each of these options below.

option 1: prevent multiple invocations of tkprof

We can prevent the TKProf report being re-generated with a simple change to the preprocessor script, as follows.

This version of the shell script will only generate a TKProf report if it doesn't already exist. In other words, we only get one chance to generate the profile report for each trace file (without intervention such as removing the TKProf report from the trace directory). We will modify the TKPROF_XT external table to use this new preprocessor script and query the table using the same trace file as in previous examples.

We can see from the highlighted timestamp that the generation of the TKProf report was skipped this time (i.e. it's the same report that we generated in an earlier example). This time we've simply read an existing report.

option 2: create a read-only external table for trace files

If we want the option to re-generate the TKProf report (should we need to), but also the efficiency of reading the resulting report many more times, then we can use the original implementation of TKPROF_XT but create a second, read-only external table for files in the user dump destination. We start by creating the read-only external table, as follows.

As stated, this is a vanilla external table to read from files in the TRACE_DIR Oracle directory. We'll alter the location of this table to reference our sample TKProf report and query the table as follows.

Again, we've simply read an existing profile report. In addition to saving on TKProf executions, another benefit of this method is that we can also read raw trace files with the read-only external table. In the following example, we'll reset the location of TRACEFILE_XT to the raw trace file and run the same query.

We can see the benefit of having a preprocessor external table to initially generate and read a TKProf report together with a generic read-only external table for reading any trace file or profile thereafter. With these, we never have to leave our IDE to work with trace files or profiler reports.

further productivity improvements

If we consider the steps we usually take to profile a trace file, they might include the following:

exit SQL*Plus or leave the SQL*Plus terminal (or other IDE window);

start another terminal window and connect to the database server;

navigate to the trace directory;

identify the trace file;

run the tkprof utility to generate the report;

open the report in an editor.

So far, we've managed to reduce these steps to identifying our default trace file, setting the external table location and querying the preprocessor external table. These are significant productivity improvements. We can take this even further by encapsulating these remaining tasks in either a SQL*Plus script or in a pipelined function.

option 1: using a utility script

Most SQL*Plus users will set a SQLPATH location for utility scripts (including the login.sql script, for example), meaning they are available in SQL*Plus regardless of the current working directory. We can add a small script to our SQLPATH directory to encapsulate the TKProf tasks shown in the previous examples. The following is an example of what a script might look like.

We can see that this script contains each of the steps needed to prepare and query the external table. Note that this script will accept the name of the trace file, but if it is not supplied, it will use the default trace file for our current session (which is what we've been using throughout this article). Using the script is, of course, very simple, as demonstrated below.

With this script, we've reduced the steps needed to generate and read a TKProf report to a single call.

option 2: using a pipelined function

An alternative to using a free-standing SQL script is to create a pipelined function to perform these tasks. This will be particularly useful for users of IDEs such as SQL Developer and TOAD. As with all pipelined functions, we need an object type to describe our output row and a collection of this type for the function to return. We create these as follows.

other profilers (orasrp)

In addition to the supplied TKProf utility, it's possible to use a preprocessor external table to generate and read any other executable profilers that generate text-based reports. We'll see an example of this with the excellent OraSRP profiler. Like TKProf, OraSRP is a command-line utility but has the added benefit of generating a fully-navigable HTML report in addition to a standard text format.

Note that for these examples, the OraSRP utility is available on the database server itself, so it is able to access trace files in the same way as the server-side TKProf utility (a workaround for readers with client-side access only is described later). In this first example, we're generating a text profile report with a naming format of <tracefile>.orasrp. We'll test it with the sample trace file we've used so far, as follows.

As with the TKPROF_XT examples, we can apply all the same resource-saving measures to the OraSRP equivalent, such as avoiding repeated report-generations for the same trace file. We can also apply productivity-measures such as a script or pipelined function utility fetch the tracefile name, set the location and query the external table in a single call.

As noted above, this example needs the OraSRP utility to be located on the database server. If this isn't possible, then there is a workaround, but this requires that the OraSRP utility is available on the client machine and also that the read-only TRACEFILE_XT external table can access trace files located in the user dump destination. Putting the following steps into a script can be an exercise for the reader.

1) determine the raw trace file name;

2) set the location of the TRACEFILE_XT external table;

3) turn off all SQL*Plus formatting and start a spool file;

4) query the TRACEFILE_XT table;

5) turn off the spool;

6) make a host call to orasrp to profile the spooled trace file to a new report;

7) make a host call to start the report file (for example, if this is saved as a .txt file on a Windows client, it will open in Notepad).

Note that with the above workaround, there is a risk that large volumes of raw trace file data could be transferred from the database server to the client machine. Wherever possible, this technique should be avoided and all work performed on the server itself (although this isn't always possible in controlled environments).

orasrp html reports

The OraSRP text report is extremely useful in its own right, but it is the HTML version of the report that adds an enormous amount of value over TKProf. We can easily transform our example to benefit from the HTML report format. First, we'll create a shell script to generate the OraSRP report as HTML for our external table.

Of course, being in HTML format means that we need to view it in a browser, so we'll use the tkprof.sql script example from earlier as a template and create a convenient orasrp.sql script to set the external table location, spool the HTML report to a flat-file and open the report in a browser. The orasrp.sql script is as follows (it allows for both Windows and *nix clients).

This version of the script is setup to display the OraSRP report in a browser on a Windows client (see the two variables defined at the top of the script). We will test this script with the trace file we've been using for all our examples, as follows.

SQL> @orasrp ORA112_ora_5107.trc

When the script completes, the following OraSRP report is opened in the web browser.

Of course, with this example we need to leave SQL*Plus for a web browser, but at least it costs no additional keystrokes or mouse-clicks and automatically takes the foreground focus! We also get the benefit of the full navigation of the HTML OraSRP report.

listing available trace files

All of the examples have used either the session's default trace file or a known trace file name throughout. We will probably want to profile trace files created outside of our own sessions, so for this we need to be able to list the available files. A preprocessor external table is perfect for this. In fact, one of the recommended background articles (listing files with the external table preprocessor in 11g) is dedicated to this very subject, but the examples in that article list files on a Windows database server. Therefore a short UNIX/Linux version is provided below. Note, however, that for any detailed commentary regarding this technique, readers should see the original article.

As described in the referenced article, the file in the location clause (line 24) can be empty but must exist. The preprocessor script that supports this external table is a very simple directory long-listing, as follows.

summary

Using the simple techniques described in this article, we are now able to find a trace file, read it using the TRACEFILE_XT read-only external table, profile it with preprocessor external tables over TKProf or OraSRP and read the reports without leaving our IDE once. We can even read HTML reports in a browser window generated from within SQL*Plus without any additional keystrokes. For readers who work regularly with trace files, these are good productivity improvements!

further reading

See the further reading references in the oracle-developer.net articles listed at the top of this article. More information on OraSRP is available in this README.

source code

The source code for the examples in this article can be downloaded from here.