We found a bug on 11.1.0.7 for some menu parsing code in E-Business suite. We have an SR open in development for this.
Allan On Jan 4, 2010 4:57am, Andre van Winssen <dreveewee@xxxxxxxxx> wrote:
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 (eg 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
