Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get rid of merge join [cartesian] in the execution plan?

user130038Jul 16 2014 — edited Jul 16 2014

Hello

ENV: Oracle 10gR2 (10.2.0.3) on Filesystem - 64bit

In PROD database, execution plan for one query in a batch job changed causing it take 1hr30min instead of 3min.

I believe "Merge Join [cartesian] followed by "Buffer [sort]"" is the root cause and I am not sure how to get rid of it.

Here is the new plan (sent by the DBA):

SQL Statement:

SELECT DISTINCT a.rowid txn_rowid, a.load_transaction_id transaction_id 
 FROM load_xe_transaction a, xe_transaction b, load_table_message_log m 
 WHERE m.table_nm = 'LOAD_XE_TRANSACTION' AND a.load_transaction_id = m.load_table_id 
 AND m.error_id = 51106 AND a.action_cd = 'N' 
 AND a.load_process_state_cd = 'E' 
 AND nvl(a.bkoff_modify_by, 0) <> nvl(b.bkoff_modify_by, 0) 
 AND nvl(a.bkoff_modify_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) <> nvl(b.backoff_modify_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.bkoff_account_no, 0) = nvl(b.bkoff_account_no, 0) 
 AND nvl(a.bkoff_security_no, 0) = nvl(b.bkoff_security_no, 0) 
 AND nvl(a.bkoff_transaction_no, 0) = nvl(b.bkoff_transaction_no, 0) 
 AND nvl(a.entry_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) = nvl( b.entry_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.bkoff_entry_cd, 0) = nvl(b.bkoff_entry_cd, 0) 
 AND nvl(a.trade_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) = nvl( b.trade_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.expect_settlement_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) = nvl(b.expect_settlement_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.actual_settlement_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) = nvl(b.actual_settlement_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.txn_desc_txt, 0) = nvl(b.txn_desc_txt, 0) 
 AND nvl(a.rr_no, 0) = nvl(b.rr_no, 0) 
 AND nvl(a.trade_basis_cd, 0) = nvl(b.trade_basis_cd, 0) 
 AND nvl(a.exchange_cd, 0) = nvl(b.exchange_cd, 0) 
 AND nvl(a.bkoff_process_dt, to_date('01-JAN-1900', 'DD-MON-Y YYY')) = nvl(b.bkoff_process_dt, to_date('01-JAN-1900', 'DD-MON-YYYY')) 
 AND nvl(a.net_trade_amt, 0) = nvl(b.net_trade_amt, 0) 
 AND nvl(a.net_setl_amt, 0) = nvl(b.net_setl_amt, 0) 
 AND nvl(a.net_base_amt, 0) = nvl(b.net_base_amt, 0) 
 AND nvl(a.trade_to_base_fx_rt, 0) = nvl(b.trade_to_base_fx_rt, 0) 
 AND nvl(a.fx_conv_dir_cd, 0) = nvl(b.fx_conv_dir_cd, 0) 
 AND nvl(a.setl_to_base_fx_rt, 0) = nvl(b.setl_to_base_fx_rt, 0) 
 AND nvl(a.setl_to_base_fx_conv_dir_cd, 0) = nvl( b.setl_to_base_fx_conv_dir_cd, 0) 
 AND nvl(a.trade_currency_cd, 0) = nvl(b.trade_currency_cd, 0) 
 AND nvl(a.setl_currency_cd, 0) = nvl(b.setl_currency_cd, 0) 
 AND nvl(a.instr_qty, 0) = nvl(b.instr_qty, 0) 
 AND nvl(a.avg_trade_price, 0) = nvl(b.avg_trade_price, 0) 
 AND nvl(a.avg_base_price, 0) = nvl(b.avg_base_price, 0) AND nvl(a.gross_trade_amt, 0) = nvl(b.gross_trade_amt, 0) 
 AND (a.trade_currency_cd = b.base_currency_cd 
 AND abs(nvl(a.gross_base_amt, 0) - nvl(b.gross_base_amt, 0)) <= 0.01 OR a.trade_currency_cd <> b.base_currency_cd 
 AND nvl(a.gross_base_amt, 0) = nvl(b.gross_base_amt, 0)) 
 AND nvl(a.accrued_int_trade_amt, 0) = nvl(b.accrued_int_trade_amt, 0) 
 AND nvl(a.accrued_int_base_amt, 0) = nvl(b.accrued_int_base_amt, 0)
 AND nvl(a.commission_trade_amt, 0) = nvl(b.commission_trade_amt, 0) 
 AND nvl(a.commission_base_amt, 0) = nvl(b.commission_base_amt, 0) 
 AND nvl(a.total_other_charge_trade_amt, 0) = nvl( b.total_other_charge_trade_amt, 0) 
 AND nvl(a.total_other_charge_base_amt, 0) = nvl( b.total_other_charge_base_amt, 0)

Optimizer Mode Used:

ALL_ROWS

Total Cost:

215,558

Execution Steps:

Step #	Step Name

SELECT STATEMENT

HASH [UNIQUE]

HASH JOIN

MERGE JOIN [CARTESIAN]

OW.LOAD_TABLE_MESSAGE_LOG TABLE ACCESS [BY INDEX ROWID]

OW.NU_LOAD_TABLE_MESSAGE_LOG_01 INDEX [RANGE SCAN]

BUFFER [SORT]

OW.XE_TRANSACTION TABLE ACCESS [FULL]

OW.LOAD_XE_TRANSACTION TABLE ACCESS [FULL]

Step #	Description	Est. Cost	Est. Rows Returned	Est. KBytes Returned

This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index NU_LOAD_TABLE_MESSAGE_LOG_01.

This plan step retrieves rows from table LOAD_TABLE_MESSAGE_LOG through ROWID(s) returned by an index.

0.033

This plan step retrieves all rows from table XE_TRANSACTION.

215,468

17,260,685

2,764,406.582

This plan step sorts the buffer row source.

215,468

17,260,685

2,764,406.582

This plan step accepts two sets of rows and builds the set of all possible combinations of row pairs. The result set grows exponentially with the size of the row sets joined.

215,473

0.193

This plan step retrieves all rows from table LOAD_XE_TRANSACTION.

0.198

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

215,557

0.392

This plan step has no supplementary description information.

This plan step designates this statement as a SELECT statement.

215,558

0.392

This happened two months ago (I have just been assigned to look at this issue) - I don't know if I can retrieve the original plan before it changed to above.

I have NO access to PROD database.

I found this on the internet when I searched for possible solution: http://www.dba-oracle.com/t_merge_join_cartesian.htm

Should I refresh the statistics for "XE_TRANSACTION" table only OR "LOAD_TABLE_MESSAGE_LOG" as well? Both tables are huge. In DEV (which was refreshed from PROD about 2.5months ago) the size of these tables is:

XE_TRANSACTION; -- 17272538

LOAD_TABLE_MESSAGE_LOG; -- 7816803

Does the following command have the right options to refresh the Stats?

EXEC DBMS_STATS.gather_table_stats('OW', 'XE_TRANSACTION', estimate_percent => dbms_stats.auto_sample_size, Cascade=> true);

OR should I calculate 100% stats:

EXEC DBMS_STATS.gather_table_stats('OW', 'XE_TRANSACTION', estimate_percent => 100, Cascade=> true);

I checked the query plan in DEV env and found it to be different (below):

OPERATION

OBJECT_NAME

CARDINALITY

COST

CPU_COST

IO_COST

SELECT STATEMENT

99098

???

HASH

99098

???

HASH JOIN

99097

???

TABLE ACCESS

LOAD_TABLE_MESSAGE_LOG

???

NESTED LOOPS

???

TABLE ACCESS

LOAD_XE_TRANSACTION

???

INDEX

NU_LOAD_TABLE_MESSAGE_LOG

???

TABLE ACCESS

XE_TRANSACTION

17270388

98787

???

Please let me know if any further info is required.

Please advise!

Regards

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 13 2014

Added on Jul 16 2014

#general-database-discussions

7 comments

2,515 views