Re: Oracle Trace Analyzer

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 4 Jan 2013 09:07:27 +0000 (UTC)
Message-ID: <pan.2013.01.04.09.07.11_at_gmail.com>

On Fri, 04 Jan 2013 07:16:49 +0000, Jonathan Lewis wrote:

> My memory of versions may be a little hazy, but I think I first saw
> tkprof in 6.0.27 - then found that it had disappeared in the 6.0.29
> upgrade, so I used awk to recreate it. Dead easy if you've got a
> language that deals with associative arrays, though at the time the
> concept was so new that no-one was thinking about subtracting resources
> for recursive SQL from the triggering SQL and all the other clever bits.
> Then tkprof re-appeared in 6.0.33

Well, parsing strings is not a problem and computing summaries is not a big problem, but tkprof also draws plans and so does TRCA. TRCA also produces rather complex HTML code. I haven't seen your code, so I cannot know whether it was as comprehensive as tkprof or not, but that's not the point. TRCA does take a long time to chew a decent sized trace, while tkprof, orasrp and Method-R profiler are two orders of magnitude faster. That is because PL/SQL is not the right tool for the job. To be honest, I expected them to load a Java class that would parse the file into the database and use that. It would be much. much faster.

Simply said, PL/SQL is not the right tool. It probably isn't too hard, but it certainly isn't fast. PL/SQL is not designed to compete with Lex and Yacc. CUP or ANTLR/JavaCC would probably do the trick much. much faster. The fact that it is possible to parse trace files in PL/SQL doesn't mean that it should be done that way.

I use TRCA despite the performance problem, because it's the easiest way to analyze trace file over the web, without actually logging into the machine. The following snippet will analyze trace file from the web server and display the result in the client browser, without interactive login to the DB server ever taking place:

# php_beautifier->setBeautify(false);
#  The query to generate the trace file name was obtained from the Rene
#  Nyffenegger's collection, available at:
#           http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

$FILE = "select dba_helper.get_trace_file(:SID,:SERIAL) as FL from dual"; $EID = 'begin

        :ID:=trcanlzr.trca$p.get_tool_execution_id;
      end;';
$EXEC = 'begin
         trcanlzr.trca$i.trcanlzr(p_file_name => :FL, x_tool_execution_id 
=>:ID);
       end;';
$RPT = 'SELECT column_value 
      FROM TABLE(trcanlzr.trca$g.display_file(:ID, \'HTML\'))';

# php_beautifier->setBeautify(true);
try {

    $db->Connect($DSN['database'], $DSN['username'], $DSN['password']);     # Get the trace file name for the session identified by 'sid,serial#'.     $rs = $db->Execute($FILE, array('SID' => $sid, 'SERIAL' => $serial));     $row = $rs->FetchRow();                                                             

    $file = $row[0];
    # Get the execution ID. That is needed to query the report.

    $rs = $db->Prepare($EID);
    $db->OutParameter($rs, $id, 'ID');
    $db->Execute($rs);

    # Execute the main procedure and analyze the file.     $rs = $db->Execute($EXEC, array('FL' => $file, 'ID' => $id));     # Get and present the report.
    $rs = $db->Execute($RPT, array('ID' => $id));     csr2ascii($rs);
    $db->close();
}

catch(Exception $e) {

    echo "ID is:$id<br>";
    echo "File is:$file<br>";
    die($e->getMessage());
}
?>

I profiled my code and tested the snippet in the sqlplus, the problem is in the TRCA, not in the PHP wrapper. I am aware of the Adrian Billington's preporcessor trick, by using external tables, but I like TRCA output better. Here is Adrian's trick:

http://www.oracle-developer.net/display.php?id=516

I modified it and used it several times, to pre-process non-trace files by Perl. Results can be spectacular when combined with SQLite.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Fri Jan 04 2013 - 10:07:27 CET