Performance issue with query
Hi Experts,
Our environment 2 node RAC database 19.17
We have an sql query causing high CPU usage. Can you guide us how can we fix it.
It runs for almost 1 hour.
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 8dkya3t99amat, child number 0 ------------------------------------- SELECT COUNT(*) FROM APPS.SVIMS_INV_QTY_STG WHERE TRANSACTION_ID = APEX_JSON.GET_VARCHAR2(P_PATH => 'jsonResponse[%d].id',P0 => :B1 )
Plan hash value: 2773155628
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5161 (100)| |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| SVIMS_INV_QTY_STG | 1 | 35 | 5161 (5)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TRANSACTION_ID"="APEX_JSON"."GET_VARCHAR2"('jsonResponse[%d].id',
TO_CHAR(:B1)))
21 rows selected.
0