SQL Tuning -- large, long-running outer join query
- From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2006 08:08:48 -0500
We have this purchased app that is generating the following query. It
runs
for 8 hours then crashes due to ORA-1555. I'm reluctant to tinker with
the undo settings or increase the undo tablespace size, as it appears
from
longops that this thing would likely run for another 7-8 hours before
completing. I'm at a loss on how to tune this -- I can't touch the
physical structure, and I don't know enough to see any hints that could
possibly help.
Any ideas?
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(+);
------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost |
------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 465K|
778M| | 2246K|
| 1 | NESTED LOOPS OUTER | | 465K|
778M| | 2246K|
| 2 | NESTED LOOPS OUTER | | 465K|
379M| | 1130K|
|* 3 | HASH JOIN OUTER | | 465K|
185M| 144M| 14070 |
|* 4 | HASH JOIN OUTER | | 465K|
139M| 124M| 7336 |
| 5 | TABLE ACCESS FULL | TS_SALES_ORDLNS | 465K|
118M| | 3178 |
| 6 | TABLE ACCESS FULL | TS_SALES_ORDLNS_LN | 472K|
20M| | 2133 |
| 7 | TABLE ACCESS FULL | TS_SALES_ORDLNS_HD | 474K|
47M| | 3920 |
| 8 | TABLE ACCESS BY INDEX ROWID| IA_SALES_ORDLNS | 1 |
436 | | 3 |
|* 9 | INDEX RANGE SCAN | I01_IA_SALES_ORDLNS | 1 |
| | 2 |
| 10 | TABLE ACCESS BY INDEX ROWID | OD_SALES_ORDLNS | 1 |
899 | | 3 |
|* 11 | INDEX RANGE SCAN | I01_OD_SALES_ORDLNS | 1 |
| | 2 |
------------------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("TS_SALES_ORDLNS"."SALES_ORDER_NUM"="TS_SALES_ORDLNS_HD"."SALES_O
RD_NUM"(+) AND
"TS_SALES_ORDLNS"."SALES_ORDER_ITEM"="TS_SALES_ORDLNS_HD"."SALES_ORD_ITE
M"(+))
4 -
access("TS_SALES_ORDLNS"."SALES_ORDER_NUM"="TS_SALES_ORDLNS_LN"."SALES_O
RD_NUM"(+) AND
"TS_SALES_ORDLNS"."SALES_ORDER_ITEM"="TS_SALES_ORDLNS_LN"."SALES_ORD_ITE
M"(+))
9 - access("TS_SALES_ORDLNS"."KEY_ID"="IA_SALES_ORDLNS"."KEY_ID"(+))
11 - access("TS_SALES_ORDLNS"."KEY_ID"="OD_SALES_ORDLNS"."KEY_ID"(+))
------------------------------------------------------------------------
------
Jeffery D Thomas
DBA
Enterprise Database and Middleware Services
Thomson, Inc.
Email: jeff.thomas@xxxxxxxxxxx
EDMS Portal:
http://indysaixdb01.indy.tce.com:7778/portal/page?_pageid=53,44513,53_44
669&_dad=portal&_schema=PORTAL
------------------------------------------------------------------------
------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
