Select query running slow from app but running well in SQL developer

User_QIWNX

1.ORDERS_SHIPMENT_RELATION_VIEW is a DB view.

2 Below query is invoked through JDBC.executeQuery() and having binding variable

View :

 CREATE OR REPLACE FORCE EDITIONABLE VIEW "TESTDB"."ORDERS_SHIPMENT_RELATION_VIEW" ("ORDER_ID", "TC_ORDER_ID", "DO_STATUS", "SHIPMENT_ID") AS 

 SELECT L.ORDER_ID,

     L.TC_ORDER_ID,

     O.DO_STATUS,

     L.SHIPMENT_ID

   FROM LPN L, ORDERS O

  WHERE L.ORDER_ID = O.ORDER_ID AND L.SHIPMENT_ID > -1

  UNION ALL

  SELECT L.ORDER_ID,

     L.TC_ORDER_ID,

     O.DO_STATUS,

     L.PLAN_LOAD_ID

   FROM LPN L, ORDERS O

  WHERE L.ORDER_ID = O.ORDER_ID AND L.PLAN_LOAD_ID > -1

  UNION ALL

  SELECT SAO.ORDER_ID,

     O.TC_ORDER_ID,

     O.DO_STATUS,

     SAO.SHIPMENT_ID

   FROM STOP_ACTION_ORDER SAO, ORDERS O

  WHERE O.ORDER_ID = SAO.ORDER_ID AND O.DO_STATUS < 150

  UNION ALL

  SELECT O.ORDER_ID,

     O.TC_ORDER_ID,

     O.DO_STATUS,

     O.SHIPMENT_ID

   FROM ORDERS O

  WHERE O.SHIPMENT_ID > -1 AND O.DO_STATUS < 150

  UNION ALL

  SELECT OM.ORDER_ID,

     O.TC_ORDER_ID,

     O.DO_STATUS,

     OM.SHIPMENT_ID

   FROM ORDER_MOVEMENT OM, ORDERS O

  WHERE O.ORDER_ID = OM.ORDER_ID AND O.DO_STATUS < 150;

Query:

SELECT DISTINCT ORDERS.TC_ORDER_ID TCDOId, ORDERS.ORDER_ID distributionOrderId, ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

ordrMovShipmentId, ORDERS.TC_ORDER_ID TCDistributionOrderId, ORDERS.TC_COMPANY_ID companyId, 

ORDERS.O_FACILITY_ALIAS_ID originFacilityName, ORDERS.O_FACILITY_NAME origFacility, ORDERS.O_ADDRESS_1 

originAddress1, ORDERS.O_ADDRESS_2 originAddress2, ORDERS.O_ADDRESS_3 originAddress3, ORDERS.D_FACILITY_ALIAS_ID 

destnFacilityName, ORDERS.D_FACILITY_NAME destFacility, ORDERS.D_ADDRESS_1 destnAddress1, ORDERS.D_ADDRESS_2 

destnAddress2, ORDERS.D_ADDRESS_3 destnAddress3, ORDERS.PICKUP_START_DTTM pickupStartDttm, 

ORDERS.PICKUP_TZ origTZ, ORDERS.DELIVERY_END_DTTM deliveryEndDttm, ORDERS.DELIVERY_TZ destTZ 

FROM ORDERS INNER JOIN ORDERS_SHIPMENT_RELATION_VIEW ON ORDERS.ORDER_ID = ORDERS_SHIPMENT_RELATION_VIEW.ORDER_ID 

WHERE ((ORDERS.TC_ORDER_ID ='201142354' and ORDERS.ORDER_ID =20492675 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142358' and ORDERS.ORDER_ID =20492690 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142359' and ORDERS.ORDER_ID =20492704 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142379' and ORDERS.ORDER_ID =20492708 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142387' and ORDERS.ORDER_ID =20492677 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142396' and ORDERS.ORDER_ID =20492680 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142397' and ORDERS.ORDER_ID =20492750 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142403' and ORDERS.ORDER_ID =20492694 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142407' and ORDERS.ORDER_ID =20492734 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142429' and ORDERS.ORDER_ID =20492781 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142440' and ORDERS.ORDER_ID =20492719 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142443' and ORDERS.ORDER_ID =20492686 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142452' and ORDERS.ORDER_ID =20492702 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142465' and ORDERS.ORDER_ID =20492768 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142525' and ORDERS.ORDER_ID =20493944 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142532' and ORDERS.ORDER_ID =20493913 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142612' and ORDERS.ORDER_ID =20494472 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142691' and ORDERS.ORDER_ID =20495361 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142692' and ORDERS.ORDER_ID =20495395 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) or (ORDERS.TC_ORDER_ID ='201142729' and ORDERS.ORDER_ID =20496169 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID 

=24438 ) ) ORDER BY ORDERS.TC_ORDER_ID asc {executed in 74560 ms}