| Author |
Message
|
| Santosh_Ghalsasi |
 Posted: Tue Feb 08, 2011 11:36 pm Post subject: Performance issue - calling oracle stored proc usig esql |
 |
|
Novice
Joined: 01 Feb 2011
Posts: 19
|
Hi,
We have one scenario where we are calling oracle stored procedure using call statement of esql and using the resulting parameters(INOUT/OUT) for further processing. Following are the inputs.
1. We have created definitions of SP(Stored Procedure) using esql and compute node.
2. My code is deployed on AIX and oracle DB is on remote machine. we are using DataDirect Oracle ODBC Driver 5.3 to connect to DB. For this we have made a stanza in the odbc64.ini file on AIX box.
The entry looks like this.
# Oracle stanza
[ORACLE_DSN]
Driver=/opt/IBM/mqsi/6.1/ODBC64/V5.3/lib/UKora23.so
Description=DataDirect 5.3 64bit Oracle Wire Protocol
HostName=<IP>
PortNumber=<Port>
SID=<SID>
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
3. There are no results sets returned by SP, only INOUT/OUT parameters are returned.
The issue we are facing is the SP calling takes so much time which we need to avoid.
Could anyone please suggest any settings for performance tuning at esql. broker, driver level?
Thanks, |
|
| Back to top |
|
 |
| fatherjack |
| Posted: Wed Feb 09, 2011 1:35 am Post subject: Re: Performance issue - calling oracle stored proc usig esql |
|
|
Knight
Joined: 14 Apr 2010
Posts: 522
Location: Craggy Island
|
| Santosh_Ghalsasi wrote: |
| The issue we are facing is the SP calling takes so much time which we need to avoid. |
How long is 'so much time'? What is your SP doing? If you call it from somewhere other than broker does it respond more quickly? How slow is your network? How well tuned is your database? Etc. Etc.
_________________
Never let the facts get in the way of a good theory. |
|
| Back to top |
|
|
| Santosh_Ghalsasi |
| Posted: Wed Feb 09, 2011 2:18 am Post subject: |
|
|
Novice
Joined: 01 Feb 2011
Posts: 19
|
Hi,
1. When we run the flow in debug, it takes 10-15 mins to respond.
2. The stored procedure is provided by some other system with which we are interacting. It is an ORACLE APPS application. It gets the data provided by us, does some logical operations(not authorised to ask what it does) and returns the INOUT/OUT parameters.
3. If we ask them to call it locally it executes in milliseconds.
4. No problem of network performance.
5. Database tuning is not done. But the stored procedure gives result in milliseconds.
I think its not hitting quickly to Oracle though DataDirect Driver.
May be we could configure some parameters in ODBC stanza to increase the performance.
Hope the ODBC stanza given in question helps to figure out something.
Thanks, |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Feb 09, 2011 2:27 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Turn on ODBC Tracing.
That will tell you where the delay is.
Don't forget to turn it off afterwards
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| fatherjack |
| Posted: Wed Feb 09, 2011 2:42 am Post subject: |
|
|
Knight
Joined: 14 Apr 2010
Posts: 522
Location: Craggy Island
|
| Santosh_Ghalsasi wrote: |
| 1. When we run the flow in debug, it takes 10-15 mins to respond. |
15 minutes! Is that down to you being in debug? How long does it take in normal execution?
'Cos I doubt any amount of tuning will get you down from 15 minutes to millseconds.
_________________
Never let the facts get in the way of a good theory. |
|
| Back to top |
|
|
|
|
