Strange behavior with large Object Types
- by Peter Lang
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?
