Why so many cursors with name FETCH API_CURSOR000000000 are created by CA Service Catalog? What functionality within Catalog is calling those cursor calls? How can we lower the frequency of these SQL cursor calls?
Article ID: 108842
Updated On:
Products
CA Service Management - Asset Portfolio Management
CA Service Management - Service Desk Manager
Issue/Introduction
In CA Service Catalog environment, we can see several cursors opened by CA Service Catalog. We can see this by running the following SQL Query:
select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id
Why so many cursors with name FETCH API_CURSOR000000000 are created by CA Service Catalog?
What functionality within Catalog is calling those cursor calls?
How can we lower the frequency of these SQL cursor calls?
select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id
Why so many cursors with name FETCH API_CURSOR000000000 are created by CA Service Catalog?
What functionality within Catalog is calling those cursor calls?
How can we lower the frequency of these SQL cursor calls?
Environment
CA Service Catalog 14.1 and later
Resolution
i. Why these SQL cursor calls (FETCH API_CURSOR000000000) are sent so many times? - There are places in the application from where these are called and this is by design. There are certain calls which are generating from the framework like JPA and some explicitly from the application logic.
ii. What functionality within Catalog is calling those cursor calls (FETCH API_CURSOR000000000)? There are no specific areas which make this call. This is application wide by design.
iii. How can we lower the frequency of these SQL cursor calls(FETCH API_CURSOR000000000)? - The queries which are embedded in the cursor comes from various parts of the application. In the 14.1 CP5 patch and 17.1 the queries were tuned so in this version the frequency of the occurrence is lower than in previous versions and also have improved response time.
ii. What functionality within Catalog is calling those cursor calls (FETCH API_CURSOR000000000)? There are no specific areas which make this call. This is application wide by design.
iii. How can we lower the frequency of these SQL cursor calls(FETCH API_CURSOR000000000)? - The queries which are embedded in the cursor comes from various parts of the application. In the 14.1 CP5 patch and 17.1 the queries were tuned so in this version the frequency of the occurrence is lower than in previous versions and also have improved response time.
Feedback
Yes
No
Powered by
