Native compiled code *much* slower??
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 24 Dec 2009 06:48:22 +1100
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> @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> @sb2
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.49
SQL> @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> @sb2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> @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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
