Strange behavior with large Object Types

Question

I recognized that calling a method on an Oracle Object Type takes longer when the instance gets bigger.

The code below just adds rows to a collection stored in the Object Type and calls the empty dummy-procedure in the loop.

Calls are taking longer when more rows are in the collection. When I just remove the call to dummy, performance is much better (the collection still contains the same number of records):

Calling dummy:               Not calling dummy:
11                           0
81                           0
158                          0

Code to reproduce:

Create Type t_tab Is Table Of VARCHAR2(10000);

Create Type test_type As Object(
  tab t_tab,
  Member Procedure dummy
);

Create Type Body test_type As
  Member Procedure dummy As Begin
    Null;  --# Do nothing
  End dummy;
End;


Declare
  v_test_type  test_type := New test_type( New t_tab() );

  Procedure run_test As
    start_time  NUMBER := dbms_utility.get_time;
  Begin
    For i In 1 .. 200 Loop
      v_test_Type.tab.Extend;
      v_test_Type.tab(v_test_Type.tab.Last) := Lpad(' ', 10000);
      v_test_Type.dummy();  --# Removed this line in second test
    End Loop;
    dbms_output.put_line( dbms_utility.get_time - start_time );
  End run_test;

Begin
  run_test;
  run_test;
  run_test;
End;

I tried with both 10g and 11g.
Can anyone explain/reproduce this behavior?

Answer 1

I can reproduce the behavior on my 11.1.0.7 database. I'm not certain that I have an explanation, but I do have a theory.

If you move the Extend call outside the loop and just add 200 elements to the collection, the decrease in performance disappears (see below). That leads me to believe that it is not solely the act of calling the object method that is the problem-- there appears to be some interaction with the inefficient extension of the collection by 1 element 200 times rather than by 200 elements 1 time.

SQL> ed
Wrote file afiedt.buf

  1  Declare
  2    v_test_type  test_type := New test_type( New t_tab() );
  3    Procedure run_test As
  4      start_time  NUMBER := dbms_utility.get_time;
  5    Begin
  6      v_test_Type.tab.Extend(200);
  7      For i In 1 .. 200 Loop
  8        v_test_Type.tab(v_test_Type.tab.Last) := Lpad(' ', 10000);
  9        v_test_Type.dummy();  --# Removed this line in second test
 10      End Loop;
 11      dbms_output.put_line( dbms_utility.get_time - start_time );
 12    End run_test;
 13  Begin
 14    run_test;
 15    run_test;
 16    run_test;
 17* End;
SQL> /
11
9
10

PL/SQL procedure successfully completed.

Speculating here, but perhaps there is some optimization that the compiler is able to make to the calls to extend the collection that it cannot (or does not) make if a call to a procedure might modify the collection.

As a quick test of that speculation, I created a member function rather than a member procedure and called the function in the loop. Since functions don't modify object state, they wouldn't preclude the sort of optimizations I was speculating about. Sure enough, if I create the object type with a member function, the decline in performance disappears

SQL> ed
Wrote file afiedt.buf

  1  Create or replace Type test_type As Object(
  2    tab t_tab,
  3    Member Procedure dummy,
  4    Member Function dummy2 return number
  5* );
SQL> /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  Create or replace Type Body test_type As
  2    Member Procedure dummy As Begin
  3      Null;  --# Do nothing
  4    End dummy;
  5    Member Function dummy2
  6      return number
  7    Is
  8    Begin
  9      Return 1;
 10    End dummy2;
 11* End;
 12  /

Type body created.

SQL> ed
Wrote file afiedt.buf

  1  Declare
  2    v_test_type  test_type := New test_type( New t_tab() );
  3    Procedure run_test As
  4      start_time  NUMBER := dbms_utility.get_time;
  5      l_num       NUMBER;
  6    Begin
  7      For i In 1 .. 200 Loop
  8        v_test_Type.tab.Extend;
  9        v_test_Type.tab(v_test_Type.tab.Last) := Lpad(' ', 10000);
 10        l_num := v_test_Type.dummy2();  --# Removed this line in second test
 11      End Loop;
 12      dbms_output.put_line( dbms_utility.get_time - start_time );
 13    End run_test;
 14  Begin
 15    run_test;
 16    run_test;
 17    run_test;
 18* End;
 19  /
11
9
9

PL/SQL procedure successfully completed.

In the end, it looks to me like the problematic statement is the Extend but that the optimizer is smart enough to be able to avoid the penalty if nothing in the loop is able to modify the object.

Answer 2

Found out myself, the problem is described in Using SELF IN OUT NOCOPY with Member Procedures:

In member procedures, if SELF is not declared, its parameter mode defaults to IN OUT.

So with every procedure call my whole object was copied twice, and as size was increasing this took longer and longer.

---

The solution is to use SELF IN OUT NOCOPY test_type as first parameter of my procedure declaration:

Create Type test_type As Object(
  tab t_tab,
  Member Procedure dummy(SELF IN OUT NOCOPY test_type)
);

and is still called without parameter

v_test_type.dummy();

---

Performance is back to normal:

0
0
0