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?

book

Article ID: 108842

calendar_today

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?

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.