Strange behavior with large Object Types

Posted by Peter Lang on Stack Overflow See other posts from Stack Overflow or by Peter Lang
Published on 2010-04-15T18:54:53Z Indexed on 2010/04/17 7:13 UTC
Read the original article Hit count: 225

Filed under:
|
|
|
|

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?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about sql