Performance issue

Hi Team,

Hi Team,

We are using oracle database 19.17 version.

We identified performance issue with attached query, consuming 100% CPU. At the same time we have seen multiple sessions with event TCP Socket (KGAS). Does this event consume memory and CPU or will it wait for IO? Please share your expertise.

------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a1br484bbbwhw, child number 0
-------------------------------------
SELECT ADT.ACCOUNT FROM XXMMS.XXMMS_INV_ITEM_ACCOUNT_DTL_TBL ADT,
XXMMS.XXMMS_INV_ITEM_ACCOUNT_HDR_TBL AHT, (SELECT * FROM JSON_TABLE (
FUSION_API_CALL(:B1 || '/fscmRestApi/resources/11.13.18.05/inventoryTran
sactionTypes?q=TransactionTypeName=''TTD Stock
Consumption''&limit=200000' , :B2 , 'GET', ''), '$.items[*]' COLUMNS (
TRANSACTION_TYPE_ID PATH '$.TransactionTypeId', TRANSACTION_TYPE_NAME
PATH '$.TransactionTypeName', TRANSACTION_ACTION_ID PATH
'$.TransactionActionId', TRANSACTION_TYPEDEFINED_BYCODE PATH
'$.TransactionTypeDefinedByCode', TRANSACTION_TYPE_STARTDATE PATH
'$.TransactionTypeStartDate', TRANSACTION_TYPE_ENDDATE PATH
'$.TransactionTypeEndDate', TRANSACTION_SOURCE_TYPE_ID PATH
'$.TransactionSourceTypeId', TRANSACTION_SOURCETYPE_NAME PATH
'$.TransactionSourceTypeName', TRANSACTION_SOURCETYPE_DEFINEDBYCODE
PATH '$.TransactionSourceTypeDefinedByCode',
TRANSACTION_SOURCETYPE_ENDDATE PATH '$.TransactionSourceTypeEndDate' )
)) ITT WHERE ADT.RECORD_ID = AHT.RECORD_ID AND ADT.END
Plan hash value: 2925341759
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name