Home » SQL & PL/SQL » SQL & PL/SQL » sql statement executing for a very long > 1 hour
sql statement executing for a very long > 1 hour [message #409876] Wed, 24 June 2009 05:43 Go to next message
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 #409879 is a reply to message #409876] Wed, 24 June 2009 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
delete FROM dim_us_rma_receipts a

Quote:
0 | SELECT STATEMENT

Something inconsistent there.

Regards
Michel

Re: sql statement executing for a very long > 1 hour [message #409885 is a reply to message #409879] Wed, 24 June 2009 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is it possible that someone has a lock on one of the rows you are trying to delete?
Re: sql statement executing for a very long > 1 hour [message #409899 is a reply to message #409885] Wed, 24 June 2009 07:03 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #409959 is a reply to message #409876] Wed, 24 June 2009 10:23 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post explain plans for the new delete and select.
Re: sql statement executing for a very long > 1 hour [message #409965 is a reply to message #409959] Wed, 24 June 2009 10:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #410067 is a reply to message #410059] Thu, 25 June 2009 00:45 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thanks rleishman,

i will try gathering statistics on table using DBMS_STATS.GATHER_TABLE_STATS .
Re: sql statement executing for a very long > 1 hour [message #410139 is a reply to message #409876] Thu, 25 June 2009 07:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12404
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 Go to previous message
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.

Previous Topic: Select Statement
Next Topic: get the name of the column being updated by query
Goto Forum:
  


Current Time: Mon Dec 05 13:00:21 CST 2016

Total time taken to generate the page: 0.12461 seconds