sql:

          SELECT maint.* FROM (
           SELECT 
                 th.*
             FROM trans_history th, events_que ceq 
            WHERE ceq.queue_id BETWEEN s_queue_id AND p_queue_id
                  AND ceq.stat = 'N'
                  AND th.flag = 'Y'
                  AND ceq.id = th.id
                  AND ceq.module = 'OPEN'
                  AND th.tdate < SYSDATE - ( t_mins_back / (24 * 60))  --> every 6 minutes based on sysdate 
         ORDER BY ceq.event_id ) maint 
         WHERE ROWNUM<= batch_size  



Explain plan:


Plan
SELECT STATEMENT  ALL_ROWSCost: 38  Bytes: 3,051  Cardinality: 9  					
	7 FILTER  				
		6 NESTED LOOPS  			
			4 NESTED LOOPS  Cost: 38  Bytes: 3,051  Cardinality: 9  		
				2 TABLE ACCESS BY INDEX ROWID TABLE EVENTS_QUE Cost: 11  Bytes: 216  Cardinality: 9  	
					1 INDEX RANGE SCAN INDEX CONSMREVNTSQUE_N6 Cost: 3  Cardinality: 17  
				3 INDEX UNIQUE SCAN INDEX (UNIQUE) NEW_UNIQUE_U1 Cost: 2  Cardinality: 1  	
			5 TABLE ACCESS BY INDEX ROWID TABLE TRANS_HISTORY Cost: 3  Bytes: 315  Cardinality: 1