Oracle RDBMS 12.1.0.1 SE ACL Performance

We have come across an issue with Oracle RDBMS 12.1.0.1 Standard Edition (SE) that impacts the performance all requests to external network resource from the database, e.g. calls to webservices. The issue appears to be due to a dependency in the Oracle code on the Result Cache (a feature that is unavailable in SE).

There is a simple test case that highlights the problem i.e.

set timing on
declare
http_request utl_http.req;
http_response utl_http.resp;
text varchar2(32767);
i number;
begin
i := 0;
while i < 1000
loop
http_request := utl_http.begin_request('http://localhost:8087/index.html&#039;);
http_response := utl_http.get_response(http_request);
utl_http.read_text(http_response, text, 32766);
utl_http.end_response(http_response);
i := i + 1;
end loop;
end;
/

You will need to use a URL that is accessible from your database, and create an appropriate ACL to give you access to it.

If you run that code in a 12.1.0.1 SE database you will see an elapsed time of several minutes e.g.

12.1.0.1 SE
Elapsed: 00:03:26.81

On further investigation it should be clear that it is this internal Oracle routine that’s at fault

SQL HASH_VALUE EXECUTIONS Seconds Sec/Exec
-------------------------------------------------------------------------------- ---------- ---------- ------------ ---------------
begin :1 := dbms_network_acl_admin.get_host_aclids(:2,:3); end; 2185038870 2000 201.29 0.10064

The same test case executed in either 12.1.0.1 Enterprise Edition or 12.1.0.2 SE2 will run much faster e.g.

12.1.0.2 SE2
Elapsed: 00:00:03.23

The CPU time spent on the internal Oracle routine will also be much lower i.e.

SQL HASH_VALUE EXECUTIONS Seconds Sec/Exec
-------------------------------------------------------------------------------- ---------- ---------- ------------ ---------------
begin :1 := dbms_network_acl_admin.get_host_aclids(:2,:3); end; 2185038870 2000 0.71 0.00036

We believe this is because the code behind dbms_network_acl_admin.get_host_aclids in 12.1.0.1 SE is dependent on the Result Cache.

The Result Cache is a feature Oracle have disabled in SE and hence the performance of the internal Oracle routine is impacted.

An SR has been logged with Oracle who have agreed it’s due to the Result Cache but have yet to offer a solution.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.