Strange behavior with large types of objects

Question

I realized that calling a method in Oracle Object Type takes longer when the instance gets larger.

The code below simply adds the rows to the collection stored in the object and calls empty dummy -procedure in the loop.

Calls take longer when the collection has more lines. When I just delete the dummy call, the performance is much better (the collection still contains the same number of entries):

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

Code to play:

 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 10g and 11g.
Can someone explain / reproduce this behavior?

Answer 1

Found myself, the problem is described in Using SELF IN OUT NOCOPY with member procedures :

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

Thus, with each call to the procedure, my entire object was copied twice, and as the size increased, it took longer and longer.

---

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

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

and still called without parameter

 v_test_type.dummy(); 

---

Performance returns to normal:

 0 0 0 

Answer 2

I can reproduce the behavior in my 11.1.0.7 database. I'm not sure I have an explanation, but I have a theory.

If you move the Extend call out of the loop and just add 200 items to the collection, the performance degradation disappears (see below). This makes me believe that this is not only a call to an object method call, but also interaction with an inefficient extension of the collection by 1 element 200 times, and not 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. 

Here, but perhaps there is some optimization that the compiler can do for calls to expand the collection, which it cannot (or does not) if the procedure call can change the collection.

As a quick test of this speculation, I created a member function, not a member procedure, and named the function in a loop. Since functions do not change the state of an object, they do not exclude the optimization possibilities that I have been thinking about. Of course, if I create an object type using a member function, performance degradation 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 seems to me that Extend is the problematic expression, but the optimizer is smart enough to avoid a penalty if nothing in the loop can change the object.