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