Re: Native compiled code *much* slower??

  • From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
  • To: stbaldwin@xxxxxxxxxxxxxxxx
  • Date: Thu, 24 Dec 2009 08:48:08 +0100

What's in: msc$log_p?
And in sb2.sql?

On Wed, Dec 23, 2009 at 8:48 PM, Steve Baldwin
<stbaldwin@xxxxxxxxxxxxxxxx>wrote:

> 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
>
>
>


-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

Other related posts: