Hi,
Following query is performing Full table scan on ACT_HI_PROCINST which impacting the performance of Application Can you suggest any index we can Create on SUPER_PROCESS_INSTANCE_ID_ column and also what kind of index creation will increases the performance of sql query execution ?
SQL Query :
select * from ( select a.*, ROWNUM rnum from (
select RES.*
from ACT_HI_PROCINST RES
WHERE RES.SUPER_PROCESS_INSTANCE_ID_ = :1
order by RES.ID_ asc
) a where ROWNUM < :2 ) where rnum >= :3
Bind Variables :
1 - (VARCHAR2(2000)):fe441ed4-0195-11e5-aaf8-001b21db7229
2 - (NUMBER):2147483647
3 - (NUMBER):1
Execution plan.
———–
Plan hash value: 545228885
——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 5 | 16730 | 84722 (1)| 00:16:57 |
|* 1 | VIEW | | 5 | 16730 | 84722 (1)| 00:16:57 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 5 | 16665 | 84722 (1)| 00:16:57 |
|* 4 | SORT ORDER BY STOPKEY| | 5 | 1610 | 84722 (1)| 00:16:57 |
|* 5 | TABLE ACCESS FULL | ACT_HI_PROCINST | 5 | 1610 | 84721 (1)| 00:16:57 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter("RNUM">=:3)
2 - filter(ROWNUM<:2)
4 - filter(ROWNUM<:2)
5 - filter("RES"."SUPER_PROCESS_INSTANCE_ID_"=SYS_OP_C2C(:1))
Regards,
Sakumar
