Oracle Trace Analyzer

[Mladen Gogala]

I've just learned from the author that the venerable TRCA package uses
just PL/SQL to parse the trace files. That's why it's so slow and
resource consuming tkprof, orasrp and Cary's Method R profiler. There are
many parsing good tools but PL/SQL is not one of them. Most of the
parsing tools use finite automata which require lexer and grammar. Lexer
identifies the tokens while the grammar part performs the required
actions with the tokens. The most frequently used parsing tools are lex/
yacc (flex/bison) and Perl.
That is why parsing of a 30MB trace file takes 10 minutes with TRCA and
it takes seconds with tkprof or orasrp.



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

[Mladen Gogala]

On Thu, 03 Jan 2013 19:28:38 +0000, Mladen Gogala wrote:

> That's why it's so slow and resource consuming tkprof, orasrp and Cary's
> Method R profiler.

Should have read:"That's why it's so slow and resource consuming when
compared to tkprof, orasrp and Cary's Method R profiler"

I was interrupted by a phone, while writing that.

[John Hurley]

What is the Oracle Trace Analyzer? You mean tkprof?

Everyone that I know uses either orasrp or Cary's tool.

I licensed a copy of Cary's tool ( through hotsos pricing ... kind of
weird ) ... but do not use it because I find the sorting options that
orasrp to be superior.

I find that my developers want to see long resource profiles contain
SQL in the order that it was submitted into the oracle database
engine. They can handle understanding that "not the first SQL in a
resource profile is the SQL that needs attention first".

I have asked for an enhancement to the Method R profiler but
apparently Cary believes strongly in how the tool should produce
output.

[Mladen Gogala]

On Thu, 03 Jan 2013 14:37:20 -0800, John Hurley wrote:

> On Jan 3, 2:28 pm, Mladen Gogala <gogala.mla...@gmail.com> wrote:
>> I've just learned from the author that the venerable TRCA package uses
>> just PL/SQL to parse the trace files. That's why it's so slow and
>> resource consuming tkprof, orasrp and Cary's Method R profiler. There
>> are many parsing good tools but PL/SQL is not one of them. Most of the
>> parsing tools use finite automata which require lexer and grammar.
>> Lexer identifies the tokens while the grammar part performs the
>> required actions with the tokens. The most frequently used parsing
>> tools are lex/ yacc (flex/bison) and Perl.
>> That is why parsing of a 30MB trace file takes 10 minutes with TRCA and
>> it takes seconds with tkprof or orasrp.
>>
>> --
>> Mladen Gogala The Oracle Whispererhttp://mgogala.byethost5.com
>
> What is the Oracle Trace Analyzer? You mean tkprof?

Nope. There is another one, look at the Metalink:

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for
Interpreting Raw SQL Traces [ID 224270.1]

>
> Everyone that I know uses either orasrp or Cary's tool.

Cary's tool is not free. Orasrp has bugs. I reported some to Egor.

[Jonathan Lewis]

"Mladen Gogala" <gogala...@gmail.com> wrote in message
news:pan.2013.01...@gmail.com...

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


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

[Mladen Gogala]

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.

[Jonathan Lewis]

"Mladen Gogala" <gogala...@gmail.com> wrote in message
news:pan.2013.01...@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.
|

I don't recall if I did the plans in awk - it wouldn't have been too hard
since the id and pid are in the STAT lines, it only a case of simple
counting of extra spaces. But I dropped the project when tkprof came back
in 6.0.33.