BizTalk Oracle Database Adapter First Load Performance

I found this performance problem with BizTalk Oracle DB Adapter a month ago when developing some orchestrations using this adapter.

When re-deploying my solution and restarting the host instances, i need to wait about 15 - 20 minutes, you can imagine how difficult to wait that long, especially after only changing one or two lines of code then re-deploying the codes for testing.

After doing some investigations and tracing the process, i found that actually BizTalk itself was loading very quickly, but it stopped at the send port to the Oracle DB. My orchestration receives messages from web services and inserts information to the oracle database.
Then i traced the database process and finally i found these results below :


SELECT /*+ RULE */ '', a.owner, decode (b.object_type, 'PACKAGE', CONCAT( CONCAT (b.object_name, '.'), a.object_name), b.object_name),decode(a.position, 0, 'RETURN_VALUE', a.argument_name), decode(a.position, 0, 5, decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)), 0, a.data_type, a.data_precision, a.data_length, a.data_scale, a.radix, 2, '' , '', 0, 0, '', a.position, '' FROM ALL_ARGUMENTS a, ALL_OBJECTS b WHERE ( b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION' ) AND b.object_id = a.object_id AND a.data_level = 0 AND a.OBJECT_NAME LIKE '[Procedure / Function name]' ESCAPE '\' AND b.OWNER = '[User Account]' ORDER BY 2,3, a.overload, 18

When loading the send port, BizTalk will try to load all the oracle procedures & functions schema information which are accessible by the specified database user account in the send port. The user account which i was using has access to all 2000+ procedures the current database where i'm actually only using less than 20 of them :(

Moral of the story :
Use or create a specific application user account which only has access to the required oracle procedures in the send port.

Below is the sql query and please note that you will need to have access to the v$sql to run this :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
AND Upper(MODULE) = 'RUNTIMEAGENT.EXE'
Order By last_load_time DESC;