What's going on in a Oracle session?

Usually, you turn on tracing a session, then analyse the output of trace file. But the raw trace file are difficult to read and understand. So you must use some tools to help you do this job. The most popular tool is what Oracle offer, tkprof, which parse the trace file and output another text file.

Here I will introduce another tool which is more powerful than tkprof, called Trace Analyzer. This tool parse the trace file completely, and also query the database for additional infomation, erasing your job for quering relate-to-trace information from database, such as table, index, tablespaces, extents, etc.

For example, when you analyse the trace from tkprof, sometimes you want to know detailed infomation about the tables and index referenced by the explain plan. Then you use the sqlplus to query the database for these infomation. But with trca, it free you from such jobs, you just read it in the report.

This tool is open source, you can view the code, modify the code, and learn the code for your own development. and you can download from Metalink Note 224270.1.

 

Install trca:

simple, follow the instructions.txt included in installation zip file.

 

Quick start to use trca:

SQL> exec dbms_session.session_trace_enable;

 

SQL> sql activites to be traced..............

 

SQL>exec dbms_session.session_trace_disable;

 

SQL> exec trca$i.trace_analyzer 

 

then a generated HTML is put into "user_dump_dest" directory.

 

NOTE:

There are various ways to enable trace.

require DBA privileage, equivalent to  "alter session set sql_trace = true"

require DBA privileage

only visable to DBA.

5. DBMS_SYSTEM.Set_sql_trace_in_session

The DBMS_SYSTEM package is not a published package in the Oracle published documentation of 9iR2, 10gR1 or 10gR2..

is not present by default, but can be loaded as the SYS user by executing the $ORACLE_HOME/rdbms/admin/dbmssupp.sql script.

 

 

以database的角度，考察下两者的关系，咔咔。

结果应该是datafile,过程应该是一系列的redo log。有些人记性好呀，把redolog都归档放在脑子里的，那就是所谓的achived logs， 有些人没那么好记性，只记住三天之内的东西，那就是online redo logs。对后一类人而言，如果对结果印象不那么深刻，三天一过，就把学到的东西丢掉了；而对前一类人而言，则可以通过archived logs 得以挽救。

再推而广之，过程是要被记录下来的，对于名人而言，那是传记的形式；对于软件过程管理，那是各种各样的文档；对于小学生而言，那是日记；对于ee而言，那是周报；对于软件开发，那是版本控制CVS；对于console用户，那是操作命令的history。

于是，结果只是一个标志，一个checkpoint。结果通过过程得以实现，也通过过程得以巩固。

 

......

 

 

这个故事告诉我们：传记是要写的，文档是要编的，日记是要涂的，周报是要交的，CVS是要做的，history是要实现的。

Maybe these tools are not Oracle related, just are complemental to the lack of readline functionality for the tools such as sqplus, rman, ftp etc.

 

From the design perspective, these tools stands a level higher than gqlplus. They are more general and more extensible. 

 

The usage of these tools are similiar, just put the program which lack of readline functionality but you want as the arguments of these tools. For example, the ftp client are lack of readlline(history, completation etc), so instead you can run "rlwrap ftp" or "cle ftp" or "rlfe ftp", they achive the same  goal.

 

rlwrap can be reached at http://utopia.knoware.nl/~hlub/uck/rlwrap/

 

rlfe now is distributed as part of the GNU readline library.

 

cle can be reached at http://kaolin.unice.fr/Cle/