Native compiled code *much* slower??

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Thu, 24 Dec 2009 06:48:22 +1100
Message-ID: <d282b3ab0912231148y120cb404q1310bbfeb6226db4_at_mail.gmail.com>

Has anyone found native compiled PL/SQL code to be much slower than interpreted? Is this perhaps indicative that we have a setting screwed up somewhere?

From what I read in the docs, native compiled code should be 'at worst' as fast as interpreted, and if there is not a lot of SQL, potentially much faster.

Have a look at this ...

SQL> alter session set plsql_code_type = interpreted;

Session altered.

Elapsed: 00:00:00.00
SQL> alter package msc$log_p compile;

Package altered.

Elapsed: 00:00:00.13
SQL> _at_sb2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL> alter session set plsql_code_type = native;

Session altered.

Elapsed: 00:00:00.00
SQL> alter package msc$log_p compile;

Package altered.

Elapsed: 00:00:00.25
SQL> _at_sb2

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.49
SQL> _at_sb2

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.49
SQL> alter session set plsql_code_type = interpreted;

Session altered.

Elapsed: 00:00:00.00
SQL> alter package msc$log_p compile;

Package altered.

Elapsed: 00:00:00.11
SQL> _at_sb2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> _at_sb2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Note that my small script takes around 100 times longer to execute if the package it calls is natively compiled rather than interpreted.

Here's some other info :

[stbaldwin_at_opbld03 ~]$ sqlplus msc#utility/xxxx

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Dec 23 13:46:41 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option

SQL> select * from v$version;

BANNER

---

Oracle Database 11g Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production

Thanks for any help !!

Steve

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 23 2009 - 13:48:22 CST