Thursday, January 21, 2010

Trace Analyzer "Directory alias does not exist in DBA_DIRECTORIES"

My previous post (Trace for dummies) remind me to post additional topic for those who find tkprof result too complicated. The solution is Trace Analyzer (AKA "trca"). Trace Analyzer is original Oracle product and can be downloaded from MetaLink note 224270.1.

Trca is not just "another replacement" for tkprof but big enhancement! Here is briefly why:
  1. Result is saved in directory where sqlplus was started. This eliminate needs to connect to database server side what is for many developer not possible! (tkprof is server only based tool)
  2. Output is full featured html with links and easy navigation through file. (tkprof is text only)
  3. trca outputs bind variables values (tkprof doesn't at all)
  4. Separates user recursive and internal recursive calls and provides more detailed wait event information
  5. Gives optimizer statistics for indexes and tables (tkprof doesn't at all)
  6. Provides information about the hottest blocks (tkprof doesn't at all)
On net there are plenty of documents that clearly describe installation (and readme.txt file is OK also) but nowhere to find solution for problem that arise after successful installation on Windows platforms only (AFAIK).

Problem reproduction

As said, regardless successful instalation, when you first time start trca in a way:
SQL> @trcanlzr.sql xe_ora_356.trc
end of output looks like:
Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: xe_ora_356.trc

...analyzing xe_ora_356.trc


Trace Analyzer executed successfully.
There are no fatal errors in trcanlzr_error.log file.
Review file trcanlzr_87242.log for parsing messages and totals.

...copying reports into local SQL*Plus client directory


...trcanlzr 43161 reports were copied from server to local SQL*Plus directory


...review file trcanlzr_87242.log for parsing messages and totals.

SQL> 
All looks OK. But when you look in directory (where html should be generated) you see:
C:\oracle\product\11g\trca\run>dir
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of C:\oracle\product\11g\trca\run

21.01.2010  21:26    </dir>         .
21.01.2010  21:26    </dir>         ..
06.09.2008  09:57             5.683 trcanlzr.sql
06.09.2008  09:57             4.876 trcanlzrnc.sql
20.01.2010  23:20                56 trcanlzr_87242.html
20.01.2010  23:20                56 trcanlzr_87242.log
20.01.2010  23:20                56 trcanlzr_87242.txt
20.01.2010  23:20             8.297 trcanlzr_error.log
02.07.2008  11:28             1.967 trcapurge.sql
               7 File(s)         20.991 bytes
               2 Dir(s)  29.721.235.456 bytes free

C:\oracle\product\11g\trca\run>
In all files with 56 bytes there is same content:
*** Directory alias  does not exist in DBA_DIRECTORIES
And there is no html generated content! Something is wrong...

Analyze trace analyzer

Mine approach was to look first in dba_directories:
SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\oracle\product\diag\rdbms\xe\xe\trace
TRCA$OUTPUT                    C:\oracle\product\diag\rdbms\xe\xe\trace

SQL> 
Because default directory (input and output) for trca is user_dump_dest, on on 11g it looks like:
SQL> show parameter user_dump_dest  

NAME            TYPE     VALUE  
--------------- -------- ----------------------------------------
user_dump_dest  string   C:\oracle\product\diag\rdbms\xe\xe\trace
SQL>
And this is exactly what trace directories point to. Grants are also OK so all looks OK also!

Now it's time to see what trca is performing. Analyzing trcanlzr.sql and resulting output in trcanlzr_error.log I found one interesting part. For source code in trcanlzr.sql:
SELECT column_value FROM TABLE(trcanlzr.trca$g.directories);
result in trcanlzr_error.log looks like:
Directories
-------------------------
()
()
user_dump_dest
When you look in source (trace analyzer packages are not wrapped!), you see:
FUNCTION directories
RETURN varchar2_table PIPELINED
IS
BEGIN /* directories */
  PIPE ROW (RPAD(SUBSTR(g_input_dir||'('||get_object_status(g_input_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_input_dir));
  PIPE ROW (RPAD(SUBSTR(g_output_dir||'('||get_object_status(g_output_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_output_dir));
  PIPE ROW (RPAD('user_dump_dest', 24)||g_udump);
  RETURN;
END directories;
Obviously our first two rows (which are empty) come as a result for g_input_dir=TRCA$INPUT and g_output_dir=TRCA$OUTPUT.

Analyzing get_directory_path and get_object_status functions, very soon I realize that they operate with upper values ... and this is the core of the problem! Do not forget that our path values from dba_directories are in lower case (as they should be!).

Resolution

Run again as priviledged user tacdiri.sql and tacdiro.sql scripts from installation dir and put the whole path in upper case (in our case "C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE").

When you check dba_directories again you must see all values in upper case:
SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE
TRCA$OUTPUT                    C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE

SQL> 

Rerun trcanlzr.sql and result is finally there!

P.S.
For some slower connections to server (or bigger trace files) do not panic if you do not get result on your PC immediatelly-just wait a while. They'll come.

Cheers!

6 comments :

  1. I don't like trace analyzer mostly because installation procedure which is way to complicated if you compare it with another similar tools.

    Try OraSRP (http://www.oracledba.ru/orasrp/) as replacement for tkprof. It's the best free profiler for extended sql traces for me.

    Just unpack it and that's it...

    Regards,
    Marko

    ReplyDelete
  2. Marko,
    Until now I was not aware for OraSROP-will try after the weekend. TH for pointing out this fact.
    However, seems to me that it cannot be run from client side (as trca) so developer are not able to run it without privilege to connect on server...this is where trca beats.
    Damir

    ReplyDelete
  3. Hi Damir,

    sorry but I forgot to mention that I use OraSRP with this simple solution from Tom Kyte (http://bit.ly/6IcBGT)

    I change it a little to work with OraSRP not tkprof.

    Regards,
    Marko

    ReplyDelete
  4. Marko,

    Now you made me smile.

    Your first post start with "Just unpack it and that's it"...
    Good sales approach!
    ;-)
    Regards,
    Damir

    ReplyDelete
  5. Hi,

    in my first comment I meant for OraSRP that is enough just to unpack it to work... I forgot that you were mentioning elimination of needs to connect to database server :/ (I have to read more carefully next time)

    Yes, you have to run OraSRP on server side and in that point trca beats, but with this little workaround from T.Kyte you can run it from client side also.

    My intention was just to point you to another free solution for profiling extended sql traces which is my favorite :)

    Kind regards,
    Marko

    ReplyDelete
  6. GOT BORED BY SEARCHING FOR PHONE NUMBERS IN TELEPHONE DIRECTORY, NOW U DON’T WORRY FOR SEARCHING,WE MAKE YOUR JOB EASY FOR SEARCHING PHONE NUMBERS CLICKHERE AND LOGIN INTO OUR SITE AND ENJOY THE SEARCH

    THANK YOU

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign