Section 3.2. OraExplain

3.2 OraExplain

OraExplain, an Oracle tuning tool that DBAs can use to explain SQL execution plans and examine their SQL cache, was the first widely available tool that combined Perl/Tk and Perl DBI. OraExplain was created by Alan Burlison, a Solaris kernel gatekeeper for Sun Microsystems, and Tim Bunce quickly added the original ora_explain.pl module to the DBD::Oracle driver download bundle. So when you obtain DBD::Oracle , you'll get OraExplain automatically (at least if you're installing on Unix).

If you're on Win32 and using ActivePerl's DBD-Oracle8 package, you'll find that OraExplain fails to come with the download. However, you can get hold of the source bundle containing the precursor file, ora_explain.PL, from the following site:

http://www.cpan.org/authors/id/TIMB/

If you use WinZip or another decompression tool, you'll find the ora_explain.PL file within the main unload directory, as shown in Figure 3-5.

Figure 3-5. Finding and running OraExplain

Follow these installation steps:

1. Once you've extracted, or located, the ora_explain.PL precursor file, run the following command:

    $ perl ora_explain.PL 

2. This extracts the actual ora_explain application. Now we can run it:

    $ perl ora_explain 

   The rest of the steps should be fairly straightforward once you've connected to your target Oracle databases. Check out the program and consult the instructions provided with it if you need help. ^[1]

   ^[1] The program may ask you to run the utlxplan.sql file, which is normally available from $ORACLE_HOME/rdbms/admin (on Unix) or %ORACLE_HOME%\rdbms\admin (on Win32). This happens if the DBA user you've logged in as requires it to create the PLAN_TABLE object for EXPLAIN PLAN usage.