Hi Malay,
Thanks for your reply.
I have taken the dump from the dev environment and created two test schema of both dev and prod and imported the same aomount of data in both the test schemas in PROD and DEV.
after that i have taken 100046 and 10056 trace files.
still i notice the diffrent execution path in PROD.I in a confuse state.
IN DEVLOPEMENT DATABASE
----------------------
SELECT DISTINCT tds.*
FROM testdev.contract_trans npq, testdev.contract_trans_group tds
WHERE npq.contract_trans_group_id = tds.id
AND tds.contract_id <> :contractid
AND tds.tradingday_date BETWEEN :fromtradingday AND :totradingday
AND tds.category_id = :categoryid
AND tds.state_id = :entitystateid
AND EXISTS
(SELECT *
FROM testdev.contract_trans npq1,
testdev.contract_trans_group tds1
WHERE npq1.contract_trans_group_id = tds1.id
AND tds1.contract_id = :contractid
AND (npq.network_point_id = npq1.network_point_id
OR (npq.network_point_id IS NULL
AND npq1.network_point_id IS NULL))
AND tds.tradingday_date = tds1.tradingday_date
AND tds.category_id = tds1.category_id
AND tds.state_id = tds1.state_id)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 1.62 1.57 0 0 0 0
Fetch 2 0.81 0.78 0 1208 0 364
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.43 2.37 0 1208 0 364

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 5 (SYSTEM)

Rows Row Source Operation
------- ---------------------------------------------------
364 HASH UNIQUE (cr=1208 pr=0 pw=0 time=783194 us)
545 FILTER (cr=1208 pr=0 pw=0 time=775342 us)
545 HASH JOIN (cr=1208 pr=0 pw=0 time=774670 us)
99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=382 pr=0 pw=0 time=465 us)
18225 HASH JOIN (cr=826 pr=0 pw=0 time=359469 us)
1000 HASH JOIN (cr=445 pr=0 pw=0 time=108528 us)
46 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=223 pr=0 pw=0 time=48274 us)
675 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=0 pw=0 time=51497 us)
99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=381 pr=0 pw=0 time=182 us)


PRODUCTION DATABSAE
=================================================

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 80.03 78.40 219 319722 0 364
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 80.03 78.41 219 319722 0 364

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 5 (SYSTEM)

Rows Row Source Operation
------- ---------------------------------------------------
364 HASH UNIQUE (cr=319722 pr=219 pw=0 time=78406184 us)
545 FILTER (cr=319722 pr=219 pw=0 time=78385884 us)
545 HASH JOIN (cr=319722 pr=219 pw=0 time=78384017 us)
675 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=219 pw=0 time=328724 us)
2380118 NESTED LOOPS (cr=319500 pr=0 pw=0 time=71521450 us)
837 HASH JOIN (cr=603 pr=0 pw=0 time=315980 us)
46 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=0 pw=0 time=48647 us)
99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=381 pr=0 pw=0 time=100990 us)
2380118 TABLE ACCESS FULL CONTRACT_TRANS (cr=318897 pr=0 pw=0 time=70077111 us)



FROM THE ABOVE I HAVE NOTICE NESTED LOoP SHOWS MORE ROWS.HERE I A CONFUSED WHY THERE IS DIFFERENCE IN EXECUTION PATH having same amount of data.
I guess we may need to change any tuning parameters for the optimizer.
Please advice us