Dear all ,
in our prod env. one select query taking too much time it is based on data
when i am passing Actual doc no its working fine but if i pass data which doesn't exist in system its taking around 2 hrs and get time out .
here is query
SELECT a.*
(SELECT id
FROM tra tr
WHERE tr.e_id = me.exp_moid)
AS i_id
FROM details ed, mo_detail me
WHERE ed.e_id = me._id
AND ed.doc_number ='001'
AND doc_type_code = 'P'
AND me.flight_number ='SR056'
AND ed.e_type = 'AD'
AND ed.e_status = 'All'
AND ed.direction IN ('Arrival')
and ed.datetime BETWEEN sysdate-10 AND sysdate
plan for query
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 777 | 12390 (1)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | TRA | 1 | 10 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | TRA_EVE_FK | 1 | | 4 (0)| 00:00:01 | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS | | 7 | 777 | 12372 (1)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 7 | 777 | 12372 (1)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| DETAIL | 7 | 644 | 12355 (1)| 00:00:01 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | DETAIL_INX13 | 17607 | | 451 (1)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | MOV_PK | 1 | | 2 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | EVE | 1 | 19 | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------