Re: SQL Tuning -- large, long-running outer join query
- From: Joseph Amalraj <joseph@xxxxxxxxxxxxxx>
- To: jeff.thomas@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Jan 2006 07:33:02 -0800 (PST)
The 2 nested loops are causing the delay.
try to changing to "hash join" changing parameters
by increasing db_file_multiblock_read_count at session level
using logon trigger
also check the current values of
optimizer_index_cost_adj
optimizer_max_permutations
Joseph Amalraj
SELET (boatload of columns)
FROM
TS_SALES_ORDLNS, /* 465,175 rows, 20,930 blocks */
OD_SALES_ORDLNS, /* 18,733,125 rows, 1,247,303 blocks */
IA_SALES_ORDLNS, /* 18,631,135 rows, 603,410 blocks */
TS_SALES_ORDLNS_HD, /* 474,465 rows, 25,819 blocks */
TS_SALES_ORDLNS_LN /* 472,645 rows, 14,043 blocks */
WHERE
TS_SALES_ORDLNS.SALES_ORDER_NUM = TS_SALES_ORDLNS_HD.SALES_ORD_NUM(+)
AND TS_SALES_ORDLNS.SALES_ORDER_ITEM =
TS_SALES_ORDLNS_HD.SALES_ORD_ITEM(+)
AND TS_SALES_ORDLNS.SALES_ORDER_NUM =
TS_SALES_ORDLNS_LN.SALES_ORD_NUM(+)
AND TS_SALES_ORDLNS.SALES_ORDER_ITEM =
TS_SALES_ORDLNS_LN.SALES_ORD_ITEM(+)
AND TS_SALES_ORDLNS.KEY_ID = OD_SALES_ORDLNS.KEY_ID(+)
AND TS_SALES_ORDLNS.KEY_ID =IA_SALES_ORDLNS.KEY_ID(+);
Other related posts:
