Carlos Sierra of Oracle Corp. created a very useful tool called TRCA, intended for analyzing Oracle trace files, much like tkprof. The tool is written entirely in PL/SQL, so there is no command line interface distributed with the tool. It is easy to write a command line wrapper, like this one:
The only problem with the TRCA is the fact that it is written entirely in PL/SQL, which makes it significantly slower than orasrp or Method-R profiler, written in D or Perl, respectively. That can be helped by using native compilation. It is not enough to set PLSQL_CODE_TYPE parameter to NATIVE in the invoking session, one has to explicitly compile all the TRCA packages in the TRCADMIN schema. The following query can be used to generate the recompilation script:
select ‘alter package trcadmin.’||object_name||’ compile;’ from dba_objects
where object_type=’PACKAGE’ and owner=’TRCADMIN’;
When that is done while the PLSQL_CODE_TYPE is set to NATIVE, the results look like this:
SQL> select owner,object_name,object_type
2 from dba_objects o, ncomp_dll$ d
3 where o.object_id=d.obj#;
SYS SQLT$_TRCA$_DIR_SET PROCEDURE
SYSTEM DBA_HELPER PACKAGE BODY
TRCADMIN TRCA$X PACKAGE BODY
TRCADMIN TRCA$G PACKAGE
TRCADMIN TRCA$G PACKAGE BODY
TRCADMIN TRCA$P PACKAGE BODY
TRCADMIN TRCA$T PACKAGE BODY
TRCADMIN TRCA$I PACKAGE BODY
TRCADMIN TRCA$E PACKAGE BODY
TRCADMIN TRCA$R PACKAGE BODY
10 rows selected.
NCOMP_DLL$ is the dictionary table in which Oracle stores the result of the native compilation.
The following function may come in handy when working with the TRCA. It returns the trace file name, formatted in Oracle11g fashion, based on SID and SERIAL#. The session must be running in order to do that.
CREATE OR REPLACE
RETURN VARCHAR2 deterministic
v_spid NUMBER :=0;
SELECT instance_name INTO iname FROM V$instance;
FROM v$process p
JOIN v$session s
ON (p.addr =s.paddr)
WHERE s.sid =p_sid
This functions requires access to V$ tables, either through an explicit grant or through SELECT ANY DICTIONARY system privilege.