sql statement executing for a very long > 1 hour [message #409876] |
Wed, 24 June 2009 05:43  |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
Hi,
i would appreciate some input, i have got a sql statement, for some reasons its running for more than an hour without completing. Im not sure what is the reason. Below is the statement.
delete FROM dim_us_rma_receipts a
where not exists
(select 'x' from fact_us_order_mgmt b
where order_category_id = 'RETURN' and a.header_id = b.order_header_id and a.line_id = b.order_line_id);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 9276 (1)| 00:01:52 |
|* 1 | TABLE ACCESS BY INDEX ROWID| FACT_US_ORDER_MGMT | 1 | 19 | 9276 (1)| 00:01:52 |
| 2 | NESTED LOOPS | | 1 | 45 | 9276 (1)| 00:01:52 |
| 3 | INDEX FULL SCAN | DIM_US_RMA_RECEIPTS_U1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | FACT_US_ORDER_MGMT_N8 | 116K| | 470 (1)| 00:00:06 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."HEADER_ID"="A"."ORDER_HEADER_ID" AND "B"."LINE_ID"="A"."ORDER_LINE_ID")
4 - access("ORDER_CATEGORY_ID"='RETURN')
Additional information
fact_us_order_mgmt - has 1 index created on header_id / line_id / order_category_id / and some other columns in this table
dim_us_rma_receipts - has 1 index created on order_header_id / order_line_id
select count(*) from fact_us_order_mgmt where order_category_id = 'RETURN' - 118136
select count(*) from dim_us_rma_receipts - 91643
Appreciate some opinion on the cause and available options to resolve this type of scenario. Thank you very much
[Updated on: Wed, 24 June 2009 06:12] by Moderator Report message to a moderator
|
|
|
|
|
Re: sql statement executing for a very long > 1 hour [message #409899 is a reply to message #409885] |
Wed, 24 June 2009 07:03   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Note the line in your plan:
4 - access("ORDER_CATEGORY_ID"='RETURN')
which corresponds to Line 4 in the Explain Plan:
|* 4 | INDEX RANGE SCAN | FACT_US_ORDER_MGMT_N8 | 116K| | 470 (1)| 00:00:06 |
Line 4 is the second step of a Nested Loop, meaning it is executed once for each row in the outer loop (once for each dim_us_rma_receipts)
Problem is: that index is just ORDER_CATEGORY_ID, but when you access that table for EVERY ROW, you also use order_header_id and order_line_id.
You could try an index with ALL 3 columns defined, but I suspect you would get a better result by not using indexes at all.
Oracle could do this a much better way, but you would need to ensure that order_header_id and order_line_id were non-null in both tables - either with NOT NULL constraints or a WHERE clause.
Try the following syntax. Look for the keyword ANTI in the join step of the plan - that will mean you are performing an anti-join (much faster than the nested loop you are using).
delete
FROM dim_us_rma_receipts a
where header_id is not null
and line_id is not null
where (header_id, line_id) NOT IN (
select order_header_id, order_line_id
from fact_us_order_mgmt b
where order_category_id = 'RETURN'
and order_header_id is not null
and order_line_id
);
Ross Leishman
|
|
|
Re: sql statement executing for a very long > 1 hour [message #409945 is a reply to message #409899] |
Wed, 24 June 2009 09:36   |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
Thank you all for reply.
I tried the sql statement using the anti join, but it did not complete even 20minutes.
I then found another sql statement. It completed in 5secs
delete FROM dim_us_rma_receipts where (header_id,line_id) in
(
select header_id, line_id from dim_us_rma_receipts
minus
select order_header_id , order_line_id from fact_us_order_mgmt where order_category_id = 'RETURN'
)
But when i change it to select, it takes more than 20mins without returning results, my problem is solved, but i just try understand why select is taking long as below
select * from dim_us_rma_receipts where (header_id,line_id) in
(
select header_id, line_id from dim_us_rma_receipts
minus
select order_header_id , order_line_id from fact_us_order_mgmt where order_category_id = 'RETURN'
)
[Updated on: Wed, 24 June 2009 10:18] Report message to a moderator
|
|
|
|
Re: sql statement executing for a very long > 1 hour [message #409965 is a reply to message #409959] |
Wed, 24 June 2009 10:35   |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
Please refer as below, thank you for response
explain plan for
delete FROM dim_us_rma_receipts where (header_id,line_id) in
(
select header_id, line_id from dim_us_rma_receipts
minus
select order_header_id , order_line_id from fact_us_order_mgmt where order_category_id = 'RETURN'
)
PLAN_TABLE_OUTPUT
Plan hash value: 2774830104
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 65 | | 9978 (1)| 00:02:00 |
| 1 | DELETE | DIM_US_RMA_RECEIPTS | | | | | |
| 2 | NESTED LOOPS | | 1 | 65 | | 9978 (1)| 00:02:00 |
| 3 | VIEW | VW_NSO_1 | 1 | 26 | | 9978 (1)| 00:02:00 |
| 4 | MINUS | | | | | | |
| 5 | SORT UNIQUE | | 1 | 26 | | | |
| 6 | INDEX FULL SCAN | DIM_US_RMA_RECEIPTS_U1 | 1 | 26 | | 0 (0)| 00:00:01 |
| 7 | SORT UNIQUE | | 116K| 2163K| 7336K| | |
| 8 | TABLE ACCESS BY INDEX ROWID| FACT_US_ORDER_MGMT | 116K| 2163K| | 9277 (1)| 00:01:52 |
|* 9 | INDEX RANGE SCAN | FACT_US_ORDER_MGMT_N8 | 116K| | | 471 (1)| 00:00:06 |
|* 10 | INDEX RANGE SCAN | DIM_US_RMA_RECEIPTS_U1 | 1 | 39 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("ORDER_CATEGORY_ID"='RETURN')
10 - access("HEADER_ID"="$nso_col_1" AND "LINE_ID"="$nso_col_2")
select * from dim_us_rma_receipts where (header_id,line_id) in
(
select header_id, line_id from dim_us_rma_receipts
minus
select order_header_id , order_line_id from fact_us_order_mgmt where order_category_id = 'RETURN'
)
PLAN_TABLE_OUTPUT
Plan hash value: 1501063034
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1206 | 6798 (1)| 00:01:22 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DIM_US_RMA_RECEIPTS | 1 | 1206 | 2 (0)| 00:00:01 |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE NOSORT | | 1 | 26 | 1 (100)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DIM_US_RMA_RECEIPTS_U1 | 1 | 26 | 0 (0)| 00:00:01 |
| 6 | SORT UNIQUE NOSORT | | 1 | 19 | 6795 (1)| 00:01:22 |
|* 7 | TABLE ACCESS BY INDEX ROWID| FACT_US_ORDER_MGMT | 1 | 19 | 6794 (1)| 00:01:22 |
|* 8 | INDEX SKIP SCAN | FACT_US_ORDER_MGMT_N3 | 1 | | 6793 (1)| 00:01:22 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT /*+ */ "HEADER_ID","LINE_ID" FROM "DIM_US_RMA_RECEIPTS"
"DIM_US_RMA_RECEIPTS" WHERE "LINE_ID"=:B1 AND "HEADER_ID"=:B2)MINUS (SELECT /*+ */
"ORDER_HEADER_ID","ORDER_LINE_ID" FROM "FACT_US_ORDER_MGMT" "FACT_US_ORDER_MGMT" WHERE
"ORDER_HEADER_ID"=:B3 AND "ORDER_CATEGORY_ID"='RETURN' AND "ORDER_LINE_ID"=:B4)))
5 - access("HEADER_ID"=:B1 AND "LINE_ID"=:B2)
7 - filter("ORDER_HEADER_ID"=:B1 AND "ORDER_CATEGORY_ID"='RETURN')
8 - access("ORDER_LINE_ID"=:B1)
filter("ORDER_LINE_ID"=:B1)
|
|
|
Re: sql statement executing for a very long > 1 hour [message #410059 is a reply to message #409965] |
Thu, 25 June 2009 00:08   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
For reasons not entirely clear, Oracle is using exactly opposite methods to perform the two statments.
With the DELETE, it is running the sub-query and using the result to drive indexed lookup on the main table.
With the SELECT, it is driving off the main table and performing the sub-query for every row returned. This is a bit crazy because it will call the sub-query MANY MANY times.
I think the problem is bad statistics on DIM_US_RMA_RECEIPTS. According to the SELECT explain plan, Oracle thinks that there is only ONE ROW in that table. If that is true, then either plan should give similar results. However if there is more than one row in DIM_US_RMA_RECEIPTS, then the SELECT explain plan is very bad.
Suggest you re-gather statistics on DIM_US_RMA_RECEIPTS using DBMS_STATS.GATHER_TABLE_STATS and try again.
Ross Leishman
|
|
|
|
Re: sql statement executing for a very long > 1 hour [message #410139 is a reply to message #409876] |
Thu, 25 June 2009 07:44   |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Just curious, but in attempting to compare a select to a delete, would it be more "fair" (a better test) to do a select * or a select rowid? My thinking is that the delete doesn't really need to retrieve all the data in the table being deleted.
|
|
|
Re: sql statement executing for a very long > 1 hour [message #410140 is a reply to message #410139] |
Thu, 25 June 2009 07:52   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
smartin wrote on Thu, 25 June 2009 13:44 | Just curious, but in attempting to compare a select to a delete, would it be more "fair" (a better test) to do a select * or a select rowid? My thinking is that the delete doesn't really need to retrieve all the data in the table being deleted.
|
Think you mean seleect 1 or select rowid
|
|
|
Re: sql statement executing for a very long > 1 hour [message #410148 is a reply to message #409876] |
Thu, 25 June 2009 08:14  |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Well...I mean the above query plan comparison showed one version with the delete, and another version that did a select *. I'm curious if a rowid would be better than a * in this case. Likewise though I'd think a select 1 (or null or any constant) would be the same id as rowid.
|
|
|