Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQLTXPLAIN and TRCANLZR use PL/SQL NATIVE compilation mode

with 4 comments

Starting on SQLT/TRCA release 11.4.5.4 both tools switched PL/SQL compilation mode of their libraries from INTERPRETED to NATIVE. This change was introduced to reduce overhead in PL/SQL code used by these two tools.

Some customers report that installing SQLT or TRCA they get some PL/SQL errors similar to the ones below. That means those systems are not correctly setup to benefit of the NATIVE compilation mode for PL/SQL. If you get similar errors you have two options: configure your system so it can benefit of this PL/SQL optimization, or revert to use INTERPRETED mode for SQLT/TRCA.

Typical errors observed:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00923: native compilation failed: C compilation or linking
 failed:spdzj.c:Could not invoke program
 /opt/Prod/SUNW/bin/cc:No such file or directory

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [spdzj.c:child process terminated due
 to signal:]

If you want to revert to use PL/SQL compilation mode of INTERPRETED on SQLT follow these steps:

  1. Modify sqlt/install/sqcommon1.sql removing the two dashes “–” at the beginning of this line (27): –DEF plsql_code_type = ‘INTERPRETED';
  2. Re-install SQLT by executing sqlt/install/sqcreate.sql
  3. If problem persists perform a fresh install executing sqlt/install/sqdrop.sql followed by sqlt/install/sqcreate.sql

Be aware that you may have to perform these steps above every time you upgrade your SQLT tool until you address the root cause of your PL/SQL configuration issue.

To perform the same on TRCA look for script trca/install/tacommon1.sql and modify its line 27 removing the two dashes “–” at the beginning of the line, so it becomes: DEF plsql_code_type = ‘INTERPRETED';. Then proceed to install the tool with trca/install/tacreate.sql as usual.

Written by Carlos Sierra

April 2, 2013 at 11:24 am

4 Responses

Subscribe to comments with RSS.

  1. Carlos,
    Thanks for the error post, I have suffered the PLS-00923 error installing SQLT on a Windows Server 2003 Oracle 10.2.0.4 installation. I followed your suggestion above, but, I had to perform the TRCA edit on TACPKG.SQL not TACCOMMON1.SQL as suggested above.

    The version of SQLT I am installing is 11.4.5.4 dated 2013-02-04

    Tony Sleight

    April 3, 2013 at 2:26 am

    • Tony,
      You may have an older version of TRCA. By the way, today I will upload a new version of SQLT and TRCA.
      Cheers — Carlos

      Carlos Sierra

      April 5, 2013 at 9:44 am

  2. These are really impressive ideas in regarding blogging.
    You have touched some nice factors here.
    Any way keep up wrinting.

    Funny Youtube

    May 8, 2013 at 9:10 am


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

Follow

Get every new post delivered to your Inbox.

Join 1,551 other followers

%d bloggers like this: