Careful in 11.2 with dbms_utility

Its common to have an instrumentation infrastructure where you might be calling DBMS_UTILITY.FORMAT_CALL_STACK to record whereabout 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

Advertisements

3 thoughts on “Careful in 11.2 with dbms_utility

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s