MQSeries.net :: View topic

tsuru · Posted: Thu Oct 08, 2009 3:27 am Post subject:

We have message flows calling Oracle 10g stored procedures, but we are getting performance issues. Looking at Oracle sessions, we realized that BrokerÂ´s ODBC connection are looking for stored procedures parameters generating some SQL:

(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',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),a.data_precision,a.data_length,a.data_scale,a.radix,a.position FROM ALL_ARGUMENTS a,all_OBJECTS b WHERE (b.object_type IN ('PROCEDURE','FUNCTION','PACKAGE')) AND b.object_id=a.object_id AND a.data_level=0 AND a.owner like 'APPS'escape'\'AND b.OBJECT_NAME like 'TKT_EAI_IMPLANTACAO_PKG'escape'\' union all select /*+ RULE */ c.owner,decode(b.object_type,'PACKAGE',CONCAT(CONCAT(c.synonym_name,'.'),a.object_name),c.synonym_name),decode(a.position,0,'RETURN_VALUE',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),a.data_precision,a.data_length,a.data_scale,a.radix,a.position FROM ALL_ARGUMENTS a,all_OBJECTS b, all_synonyms c WHERE (c.table_owner=b.owner AND c.table_name=b.object_name) AND b.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') AND b.object_id=a.object_id AND a.data_level=0 AND c.owner like 'APPS'escape'\'AND c.synonym_NAME like 'TKT_EAI_IMPLANTACAO_PKG'escape'\' union all select /*+ RULE */ c.owner,c.synonym_name,decode(a.position,0,'RETURN_VALUE',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),a.data_precision,a.data_length,a.data_scale,a.radix,a.position FROM ALL_ARGUMENTS a,all_synonyms c WHERE (c.table_owner=a.package_name AND c.table_name=a.object_name) AND a.data_level=0 AND c.owner like 'APPS'escape'\'AND c.synonym_NAME like 'TKT_EAI_IMPLANTACAO_PKG'escape'\') order by 1,2,10

This query runs in about 10 minutes!! Right after that the stored procedure is called by Broker.

The Oracle DBA says that Oracle 10g does not support "Rule Hints" (see bold text above) and so it is the cause for the performance degradation.

Does anyone had such problems with Oracle 10g stored procedures?