HI All,
Oracle EE : 11.2.0.4
OS: OL 7.0
On Virtual Machine
In our environment, Below query consuming more CPU and the same statement runs multiple times with different values in the Ora_Hash function.
select TRACTRY_UPLD_ID,TRACTRY_FILE_NM,EQPMT_ID,CLNT_TYP_CD,TRACTRY_FILE_ENCDNG_TYP_CD,TRACTRY_FILE_ENCDNG_VRSN_VAL,TRACTRY_TXT,TMPEQ_TRNEXL_ID_VAL,
CLNT_CRT_DT,TRACTRY_TYP_CD,ROW_CRT_DTM from
ESN_TRACT.TRACT_upload where TRACTRY_UPLD_ID = (select
min(TRACTRY_UPLD_ID) from ESN_TRACT.TRACT_upload WHERE
ORA_HASH(TMPEQ_TRNEXL_ID_VAL, 79)= 61 )
Plan hash value: 357387853
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TRACT_UPLOAD | 1 | 202 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | XPX_TRACT_UPLOAD | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
|* 4 | TABLE ACCESS FULL | TRACT_UPLOAD | 1 | 13 | 8544 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRACTRY_UPLD_ID"=)
4 - filter(ORA_HASH("TMPEQ_TRNEXL_ID_VAL",79)=61)
27 rows selected.
Is there any way to bring down the CPU consumption for the same.
Thanks in advance for all your help.
Regards.