Slow SQL with bind variables involving Object Types

Summary of the problem:

A simple insert inside PL/SQL procedure took 2-3 milliseconds consistently. The insert had no disk reads, a few tens of db block gets. For such a load the insert must have taken no more than 10s or 100s of microseconds. The time spent was reported as CPU_TIME in v$sql-views.
The insert had a few bind variables which where calls to member functions of Object Types. After these bind variables were replaced with simple PL/SQL variables the insert performance returned to where it had to be – under one millisecond.

Database version: 10.2.0.4.

Details:

The object type was declared like this:

CREATE TYPE OBJECT_TYPE1 AS OBJECT
(...
pri_cod_acct_no CHAR(16);
...
MEMBER FUNCTION cod_acct_no RETURN CHAR IS BEGIN RETURN pri_cod_acct_no;END;
...
CONSTRUCTOR FUNCTION cls_ch_acct_mast( var_pi_input_value CHAR) RETURN SELF AS RESULT IS
BEGIN pri_cod_acct_no:=var_pi_input_value; END;
);

The insert was like this:

declare
t OBJECT_TYPE1;
begin
t:=OBJECT_TYPE1('123456789');
...
insert into TABLE1 values (...
t.cod_acct_no, ....
);
...
end;
/

where t.code_acct_no was a call to member function of object type returning VARCHAR2.

The performance was back to normal after the code was changed to first assign the member function to PL/SQL variable and then that variable was used as bind variable:

declare
t OBJECT_TYPE1;
m TABLE1%rowtype;
begin
...
m.cod_acct_no:=t.cod_acct_no;

insert into TABLE1 values (...
m.cod_acct_no, ....
);
...
end;
/

The same results were achieved if a simple PL/SQL variable was replaced with a simple PL/SQL function call.

As for the reason why such a performance degradation with object types then V$SQL_BIND_METADATA reports interesting data:
– data type of such a bind variable is ADT (I think it stands for Abstract Data Type?)
– data length is 4000
It must be that oracle does much more CPU work with ADTs then whith simple PL/SQL variables and functions as far as bind variables are concerned.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s