SQL & PL/SQL

Hi

below query is running 1 minutes on test server however it is stucking on production for more than 8 hours .

I compared explain plan for both queries ,on test server,optimizer is using hash join however on production ,optimizer is using Merge Cartesian join .

I tried query with ordered hit ,we get rid of mergecartesian join but cost got increased
now please advice ,if we should go by this ordered hit method on production or not not
or suggets any other ways .

below is query
SELECT DISTINCT acct_promo_hdr.row_id, '401000', 'EXPORTED',
acct_promo_hdr.src_num, 'PLAN_ACCOUNT_PROMOTION',
promo_hdr_org.NAME
FROM siebel.s_src acct_promo_hdr,
siebel.s_mdf_alloc deal,
siebel.s_bu promo_hdr_org
-- siebel.s_contact contact,
-- siebel.s_user usr
WHERE acct_promo_hdr.sub_type = 'PLAN_ACCOUNT_PROMOTION'
AND acct_promo_hdr.row_id = deal.promo_id
AND acct_promo_hdr.bu_id = promo_hdr_org.row_id
-- AND acct_promo_hdr.created_by = contact.row_id
-- AND contact.row_id = usr.row_id
AND EXISTS (
SELECT 1
FROM approval acb_approval
WHERE acb_approval.status NOT IN
('11', '12', '65', '77')
AND acct_promo_hdr.src_num =
acb_approval.commit_num)
AND deal.status_cd IN
('Cancel',
'Cancelled',
'Committed',
'On Hold',
'Processing Claim',
'Paid In Part',
'Paid In Full',
'Hold'
)


explain plan on production

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 115701554

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 207 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 1 | 133 | 207 (2)| 00:00:03 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 133 | 206 (1)| 00:00:03 |
| 4 | NESTED LOOPS | | 1 | 112 | 205 (1)| 00:00:03 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 57 | 67 (2)| 00:00:01 |
| 6 | SORT UNIQUE | | 1 | 30 | 64 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | APPROVAL | 1 | 30 | 64 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 3 | 81 | 3 (34)| 00:00:01 |
| 9 | TABLE ACCESS FULL | S_BU | 3 | 81 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| S_SRC | 1 | 55 | 137 (0)| 00:00:02 |
|* 11 | INDEX RANGE SCAN | S_SRC_U2 | 1 | | 137 (0)| 00:00:02 |
|* 12 | INDEX RANGE SCAN | S_MDF_ALLOC_F8 | 12 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | S_MDF_ALLOC | 11 | 231 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("ACB_APPROVAL"."STATUS"<>'11' AND "ACB_APPROVAL"."STATUS"<>'12' AND
"ACB_APPROVAL"."STATUS"<>'65' AND "ACB_APPROVAL"."STATUS"<>'77')
11 - access("ACCT_PROMO_HDR"."BU_ID"="PROMO_HDR_ORG"."ROW_ID" AND
"ACCT_PROMO_HDR"."SUB_TYPE"='PLAN_ACCOUNT_PROMOTION')
filter("ACCT_PROMO_HDR"."SUB_TYPE"='PLAN_ACCOUNT_PROMOTION' AND
"ACB_APPROVAL"."COMMIT_NUM"=TO_NUMBER("ACCT_PROMO_HDR"."SRC_NUM"))
12 - access("ACCT_PROMO_HDR"."ROW_ID"="DEAL"."PROMO_ID")
13 - filter("DEAL"."STATUS_CD"='Cancel' OR "DEAL"."STATUS_CD"='Cancelled' OR
"DEAL"."STATUS_CD"='Committed' OR "DEAL"."STATUS_CD"='Hold' OR "DEAL"."STATUS_CD"='On
Hold' OR "DEAL"."STATUS_CD"='Paid In Full' OR "DEAL"."STATUS_CD"='Paid In Part' OR
"DEAL"."STATUS_CD"='Processing Claim')

35 rows selected.