We use TopLink 10.1.3.5 to connect to MS SQL Server 2008.
What we are seeing is that when a query is being run by TopLink a lot of cursors open up and remain open. Our database CPU usage goes up and it affects the whole application.
Our DBA took a look at it and said the database shows FETCH_APICURSOR* being used for select statements.
Is there a way to tell TopLink not to use cursors for queries?
Can you pin point a particular TopLink query tied to the " FETCH_APICURSOR* " call in the app and post how it is being created?
My guess is that the application is specifying the TopLink query object to return a cursor or stream and not closing it in all cases, or keeping them open for a long period - did you say they were leaking, or is it just that a large number are open at a time leading to performance problems?
This streams+cursors are described in the TopLink docs here
or the 10g docs here:
If this is the case, you might want to use a different strategy such as pagination instead of cursors, described here:
The query is a simple named query of Expression type on a descriptor based on a view
Select * from my_view
where col1 like param1
and col2 = param2
and col3 = param3
We do not use cursor or a stream to get the results.
The query is executed by calling session.executeQuery(queryName) which returns a vector.
Are there any database setting that we need to change?
I am not that familar with MS SQL or its drivers, but my guess after a quick search is that this is a lower level JDBC setting issue:
If so, you will have to look over the documentation for the driver you are using for performance tuning. My understanding though is that cursors tied to the resultset should still get closed once the resultset is closed. So if there is a problem with the amount of time they are remaining open, it might be that your queries are returning too much data and could be trimmed using pagination to limit the data being sent.
has numerous options that might help from the application side of things.