Native Compilation and TRCA

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:

http://mgogala.byethost5.com/trca_cli

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
  FUNCTION get_trace_file(
      p_sid    NUMBER,
      p_serial NUMBER)
    RETURN VARCHAR2 deterministic
  IS
    filler VARCHAR2(20):=’_ora_’;
    v_spid NUMBER      :=0;
    iname  VARCHAR2(16);
  BEGIN
    SELECT instance_name INTO iname FROM V$instance;
    SELECT p.spid
    INTO v_spid
    FROM v$process p
    JOIN v$session s
    ON (p.addr   =s.paddr)
    WHERE s.sid  =p_sid
    AND s.serial#=p_serial;
    RETURN(iname||filler||v_spid||’.trc’);
  END;
  /

This functions requires access to V$ tables, either through an explicit grant or through SELECT ANY DICTIONARY system privilege.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Miscellaneous Oracle and tagged , , , . Bookmark the permalink.

One Response to Native Compilation and TRCA

  1. You mention that PDF looks better for some purpose and that’s true for blogs too. I collect good articles in PDF format in a little database that holds an abstract, author, source and the file including the full path to a directory on disk. I can do .docx but .pdf is more common. Putting your blog in that format makes it easier to include any valuable posts in my collection.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s