Hi Steve, Here are the results from my testing. There's definitive a problem with plsql native compilation in 11gR1 as far as I can see. 11.1.0.7 - 64 bit --------------------- interpreted Elapsed: 00:00:00.06 native Elapsed: 00:02:05.11 << interpreted + inlining Elapsed: 00:00:00.04 native + inlining Elapsed: 00:02:01.91 << 11.2.0.1 - 64 bit ------------------ interpreted Elapsed: 00:00:00.07 native Elapsed: 00:00:00.10 interpreted + inlining Elapsed: 00:00:00.07 native + inlining Elapsed: 00:00:00.11 for the inline test I used the inline pragma: create or replace procedure junk as PRAGMA INLINE (dbms_utility, 'YES'); l_callstack VARCHAR2(4096) := dbms_utility.format_call_stack; begin null; end junk; / Regards, Andre 2010/1/4 Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx> > Thanks to all who replied. I have trimmed this down to a *much* > simpler test case. There is NO SQL so the opportunity for tuning is > limited. > > I'm pretty sure it must be a bug with 11.1 but I'd be grateful if > someone also running 11.1 could try my test case ... > > [stbaldwin@opbld03 ~]$ sqlplus usr/xxx > > SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jan 4 03:24:37 2010 > > 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> set timing on > SQL> set echo on > SQL> @jproc > SQL> create or replace procedure junk as > 2 l_callstack VARCHAR2(4096) := > dbms_utility.format_call_stack; > 3 begin > 4 null; > 5 end junk; > 6 / > > Procedure created. > > Elapsed: 00:00:00.02 > SQL> @interp > SQL> alter session set plsql_code_type = interpreted; > > Session altered. > > Elapsed: 00:00:00.00 > SQL> alter procedure junk compile; > > Procedure altered. > > Elapsed: 00:00:00.01 > SQL> @sb8 > SQL> begin > 2 for i in 1 .. 10000 loop > 3 junk; > 4 end loop; > 5 end; > 6 / > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.04 > SQL> @native > SQL> alter session set plsql_code_type = native; > > Session altered. > > Elapsed: 00:00:00.00 > SQL> alter procedure junk compile; > > Procedure altered. > > Elapsed: 00:00:00.02 > SQL> @sb8 > SQL> begin > 2 for i in 1 .. 10000 loop > 3 junk; > 4 end loop; > 5 end; > 6 / > > PL/SQL procedure successfully completed. > > Elapsed: 00:02:03.38 > > As you can see, it is now a standalone procedure that calls > dbms_utility.format_call_stack *and nothing else*. I don't know what > happens in dbms_utility.format_call_stack but all my code is pure > pl/sql so there is presumably no context switching. > > I have tried natively compiling dbms_utility but it makes no > difference to the results. > > Thanks again, > > Steve > > On Sun, Dec 27, 2009 at 3:32 AM, Andre van Winssen <dreveewee@xxxxxxxxx> > wrote: > > > > > > Hi Steve, > > > > > > > > I ran it against 11.2 multiple times and cannot reproduce the behavior > you > > describe. I see following consistent results: > > > > > > > > plsql_code_type = interpreted => 3.21 secs > > > > plsql_code_type = native => 2.9 secs > > > > > > > > (nr of iterations set to 10000) > > > > > > > > To be able to test this I had to add a missing table > db_error_stack_context > > which is referenced type declarations in msc$log_p (e.g. SUBTYPE stSource > IS > > db_error_stack_context.context_at%TYPE): > > > > > > > > create table db_error_stack_context > > > > (context_at varchar2(1024) > > > > ,context_value varchar2(1024) > > > > ,context_name varchar2(1024) > > > > ); > > > > > > > > Regards, > > > > Andre >