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

Got some thoughts? Leave a comment

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.