| Advice needed for performance improvement in OCI program [message #365277] |
Thu, 11 December 2008 05:14  |
clancypc
Messages: 36
Registered: December 2006
|
Member |
|
|
I have amended a program to lookup a value in an oracle database. I am passing it a phone number (cli) and need the account number associated with it returned. The cli field has a uniq index on it to make the lookup quick (or so I thought). Now I normally have 10 instances of this program running at a time, all connecting to the same database, using the same user ID and password.
The code is fairly simple, I will try to summarise what I am doing:
OCIInitialize
OCIEnvInit
OCIHandleAlloc (to set up the error handle)
OCIHandleAlloc (to set up the statement handle)
OCILogon
Start the lookup loop
( I cheat a bit here and build the sql string with the where value embedded rather than use a bind variable but I use the define variable for the output)
OCIStmtPrepare
OCIDefineByPos
OCIStmtExecute
Finish lookup loop when all data has been processed.
OCILogoff
OCIHandleFree (Error Handle)
OCIHandleFree (Statement Handle)
OCIHandleFree (Environment Handle)
When I run the program as it is now it normally doesnt take more than about 30 seconds to complete. When I try running a version that does an oracle lookup, it frequently takes more than five minutes to complete and I have had some instances running for more than two hours.
Can anyone suggest/ make recommendations as to where I am going wrong or how I can improve the performace of this program?
Thanks
|
|
|
|
| Re: Advice needed for performance improvement in OCI program [message #365362 is a reply to message #365277] |
Thu, 11 December 2008 13:31  |
vicenzo
Messages: 28
Registered: December 2007
Location: Paris
|
Junior Member |
|
|
| clancypc wrote on Thu, 11 December 2008 12:14 |
When I run the program as it is now it normally doesnt take more than about 30 seconds to complete. When I try running a version that does an oracle lookup, it frequently takes more than five minutes to complete and I have had some instances running for more than two hours.
|
Even 30 seconds seems extremly slow...
Your pseudo code seems fairly simple...
What you could check is the time spent in OCILogon and the time to execute the query.
If most of the time is spent in logging to database, it might be a network or sql*net configuration issue.
Otherwise, it's a database issue.
|
|
|
|
| Re: Advice needed for performance improvement in OCI program [message #376219 is a reply to message #365362] |
Tue, 16 December 2008 08:49 |
clancypc
Messages: 36
Registered: December 2006
|
Member |
|
|
| vicenzo wrote on Thu, 11 December 2008 19:31 |
Your pseudo code seems fairly simple...
What you could check is the time spent in OCILogon and the time to execute the query.
If most of the time is spent in logging to database, it might be a network or sql*net configuration issue.
Otherwise, it's a database issue.
|
How do I measure the time spent in OCILogon and the time to execute the query?
If its a database issue, what would I be looking for?
Thanks
[Updated on: Tue, 16 December 2008 08:52] Report message to a moderator |
|
|
|
| Re: Advice needed for performance improvement in OCI program [message #376558 is a reply to message #376219] |
Wed, 17 December 2008 17:57 |
vicenzo
Messages: 28
Registered: December 2007
Location: Paris
|
Junior Member |
|
|
| clancypc wrote on Tue, 16 December 2008 15:49 |
How do I measure the time spent in OCILogon and the time to execute the query?
|
Just add some traces in your code !
| clancypc wrote on Tue, 16 December 2008 15:49 |
If its a database issue, what would I be looking for?
|
indexes, ...
|
|
|
|
| Re: Advice needed for performance improvement in OCI program [message #380584 is a reply to message #376558] |
Mon, 12 January 2009 07:30 |
clancypc
Messages: 36
Registered: December 2006
|
Member |
|
|
OK,
I have added trace statements to my code and most of the queries take less than a second to return. I cant think of any C funtions that measure time in fractions of a second. I am trying to get the oracle DBA's to allow my user access to the v$ tables, I will be able to see much more when I have that access.
Is there a limit on how many sessions can be open under one user id? I am just wondering if I have say ten instances of the program running, each one logged on with the same user name and password could that potentially cause a problem?
The query that they are all running is quite basic.
SELECT acc_number, migrated FROM cli_inf
where cli_bill_number = '<insert bill number>';
The cli_bill_number column has a unique index on it. The primary key for the table is a combined key of the acc_number and the cli_bill_number.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
