Careful in 11.2 with dbms_utility

November 2, 2011

1–2 minutes

Its common to have an instrumentation infrastructure where you might be calling DBMS_UTILITY.FORMAT_CALL_STACK to record where in the code path you may be..

But this can bite you in 11.2

A simple demo of how much slower it has become is shown below. Relevant parameters are below:


NAME                                     VALUE
---------------------------------------- -------------
plsql_ccflags
plsql_code_type                          INTERPRETED
plsql_debug                              FALSE
plsql_optimize_level                     2
plsql_v2_compatibility                   FALSE
plsql_warnings                           DISABLE:ALL


SQL> connect scott/tiger@db11107
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition 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 IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

SQL> connect scott/tiger@db11202
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31

3 responses to “Careful in 11.2 with dbms_utility”

Sokrates

November 2, 2011 at 3:41 pm

Sorry, this does not reproduce for me:

SQL> show parameter plsql

NAME TYPE VALUE
———————————— ———– ——————————
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> set timi on
SQL> declare
2 x varchar2(1000);
3 begin
4 for i in 1..10000 loop
5 x:=dbms_utility.format_call_stack;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Reply
connormcdonald

November 5, 2011 at 1:09 pm

“Another reason I should have gone Linux…” 🙂

Reply
Gary

November 6, 2011 at 11:22 am

The new XE 11.2.0.2 on Windows doesn’t show it either.
Does it reproduce ? Just wondering if there is some reason it became invalid and had to be recompiled.

BANNER
——————————————————————————–
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production
PL/SQL Release 11.2.0.2.0 – Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

14:20:36 XE> show parameter plsql

NAME TYPE VALUE
———————————— ———– ——————————
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string ENABLE:ALL, DISABLE: 7203
14:20:40 XE> set timing on
14:20:40 XE> declare
14:20:40 2 x varchar2(1000);
14:20:40 3 begin
14:20:40 4 for i in 1 .. 10000 loop
14:20:40 5 x := dbms_utility.format_call_stack;
14:20:40 6 end loop;
14:20:40 7 end;
14:20:40 8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Reply