Home » RDBMS Server » Performance Tuning » Delete statement being running for over 2hrs (Oracle 11.2.0.1, Red Hat Linux 2.6.18-128.el5 x86_64 )
Delete statement being running for over 2hrs [message #510297] Fri, 03 June 2011 08:48 Go to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
Hello Team,

I am running the following delete query and it has been running for over 2hrs:

delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM, ACCOUNT_TYP_ID, MEMBER_ID, CASE_NUM, MONTHLY_SOA_AMT, ACCOUNT_CLOSED_DT, ACCOUNT_CREATION_DT, SOA_FREQ_CD, FED_TAX_CERT_IND, ACCOUNT_BALANCE_AMT, STATE_TAX_CERT_CD, ACCOUNT_BALANCE_UPDATE_DT, ACCOUNT_STATUS_CD);
commit;

Here is the explan plain result:

explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM, ACCOUNT_TYP_ID, MEMBER_ID, CASE_NUM, MONTHLY_SOA_AMT, ACCOUNT_CLOSED_DT, ACCOUNT_CREATION_DT, SOA_FREQ_CD, FED_TAX_CERT_IND, ACCOUNT_BALANCE_AMT, STATE_TAX_CERT_CD, ACCOUNT_BALANCE_UPDATE_DT, ACCOUNT_STATUS_CD);
commit;

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

Plan hash value: 611392786

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M| 9018K (91)| 30:03:37 |
| 3 | VIEW | VW_NSO_1 | 16M| 184M| | 745K (1)| 02:29:12 |
| 4 | MINUS | | | | | | |
| 5 | SORT UNIQUE | | 16M| 184M| 309M| | |
| 6 | BITMAP CONVERSION TO ROWIDS | | 16M| 184M| | 20 (0)| 00:00:01 |
| 7 | BITMAP INDEX FULL SCAN | IDX_AC_FED_TAX_CERT_IND | | | | | |
| 8 | SORT UNIQUE | | 16M| 1354M| 1939M| | |
| 9 | SORT GROUP BY | | 16M| 1354M| 1939M| 672K (1)| 02:14:27 |
| 10 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_FACT | 16M| 1354M| | 18923 (3)| 00:03:48 |
| 11 | BITMAP CONVERSION TO ROWIDS| | | | | | |
| 12 | BITMAP INDEX FULL SCAN | IDX_AC_FED_TAX_CERT_IND | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_FACT | 16M| 1508M| | 20 (0)| 00:00:01 |
| 14 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 15 | BITMAP INDEX FULL SCAN | IDX_AC_FED_TAX_CERT_IND | | | | | |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(ROWID="$kkqu_col_1")

I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.

Thanks

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510298 is a reply to message #510297] Fri, 03 June 2011 08:56 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
How many rows are you deleting?

The record retention policy seems...unusual.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510308 is a reply to message #510298] Fri, 03 June 2011 09:44 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
What query can I use to tell? Thanks

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510310 is a reply to message #510308] Fri, 03 June 2011 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What query can I use to tell? Thanks
SELECT COUNT(*) FROM ....

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510320 is a reply to message #510310] Fri, 03 June 2011 10:28 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
Sorry I misunderstood the request:

SELECT COUNT(*) from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM, ACCOUNT_TYP_ID, MEMBER_ID, CASE_NUM, MONTHLY_SOA_AMT, ACCOUNT_CLOSED_DT, ACCOUNT_CREATION_DT, SOA_FREQ_CD, FED_TAX_CERT_IND, ACCOUNT_BALANCE_AMT, STATE_TAX_CERT_CD, ACCOUNT_BALANCE_UPDATE_DT, ACCOUNT_STATUS_CD);



COUNT(*)
----------
3968364
1 row selected.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510321 is a reply to message #510320] Fri, 03 June 2011 10:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It takes time to process a 260TB table.
Sad

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510325 is a reply to message #510321] Fri, 03 June 2011 11:56 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
Is there a way to tell the progression of the delete or determine if delete is actually happening? In addition, is there a way to determine how long it will take?

I am running the delete on my laptop at work and I need to disconnect. I have an Oracle training by 4pm today. Thanks

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510326 is a reply to message #510325] Fri, 03 June 2011 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
No guarantees using below provides useful results

SQL> desc v$session_longops
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID						    NUMBER
 SERIAL#					    NUMBER
 OPNAME 					    VARCHAR2(64)
 TARGET 					    VARCHAR2(64)
 TARGET_DESC					    VARCHAR2(32)
 SOFAR						    NUMBER
 TOTALWORK					    NUMBER
 UNITS						    VARCHAR2(32)
 START_TIME					    DATE
 LAST_UPDATE_TIME				    DATE
 TIMESTAMP					    DATE
 TIME_REMAINING 				    NUMBER
 ELAPSED_SECONDS				    NUMBER
 CONTEXT					    NUMBER
 MESSAGE					    VARCHAR2(512)
 USERNAME					    VARCHAR2(30)
 SQL_ADDRESS					    RAW(8)
 SQL_HASH_VALUE 				    NUMBER
 SQL_ID 					    VARCHAR2(13)
 SQL_PLAN_HASH_VALUE				    NUMBER
 SQL_EXEC_START 				    DATE
 SQL_EXEC_ID					    NUMBER
 SQL_PLAN_LINE_ID				    NUMBER
 SQL_PLAN_OPERATION				    VARCHAR2(30)
 SQL_PLAN_OPTIONS				    VARCHAR2(30)
 QCSID						    NUMBER

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510355 is a reply to message #510326] Fri, 03 June 2011 20:25 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
All, the delete statement has been running for over 14hrs. Based on the Explain Plain I posted is this normal? I don't mean to over post or be pushy. I am on a deadline to have the duplicates deleted by midnight.

Thanks,
CR

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510356 is a reply to message #510355] Fri, 03 June 2011 20:35 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have you tried a select from v$session_longops yet?

select * from v$session_longops where sofar != totalwork;


for example. That might tell you how long it will still take.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510357 is a reply to message #510356] Fri, 03 June 2011 20:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
EXPLAIN PLAN projects about a 30 hour run.
We have no way to determine how accurate (or not) is the 30 hour number.
Given the way Oracle handles transaction isolation, other sessions have limited visibility into the DELETE session of interest.
I just hope that TEMP and UNDO space are sufficient for all the needed activity.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510360 is a reply to message #510357] Fri, 03 June 2011 22:16 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
ThomasG,

I ran several queries based on the view, v$session_longops. TIME_REMAINING return 0 (zero). select * from v$session_longops where sofar != totalwork; returned nothing.

@BlackSwan, thanks for the information.

I guess I am on waiting game Smile I am calling it a quit tonight and will update everyone tomorrow morning.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510451 is a reply to message #510360] Mon, 06 June 2011 02:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If you disconnect and were running the session locally on the laptop, the delete will need to start again from scratch.

Is that table really 260Tb? If so, as Blackswan says, it is going to take a long time alright.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510503 is a reply to message #510451] Mon, 06 June 2011 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Roachcoach wrote on Mon, 06 June 2011 08:09
If you disconnect and were running the session locally on the laptop, the delete will need to start again from scratch.


Plus oracle will have to rollback everything it did up to that point, which will also take a while.

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510634 is a reply to message #510297] Mon, 06 June 2011 21:44 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
What is this supposed to delete ?

All those rows where the GROUP BY column list is duplicated ?
What if some values are triplicated ? You'd still have duplicates after the delete.


I doubt if this table is 500TB in size. The explain plan shows that Oracle expects to delete 2,604Billion rows with a total size of 260TB. I would ask if the statistics are correct.


Have you considered using Parallel DML ?
{code}
ALTER SESSION ENABLE PARALLEL_DML;
DELETE /*+ PARALLEL */ ...
{code}


Hemant K Chitale

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510637 is a reply to message #510634] Mon, 06 June 2011 22:23 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
This table is only 1.7 Gig with about 18 million rows. You're correct...some of the columns contain more than 2 duplicates. Would you please let me know the best way to script the delete using those columns? I have been trying other methods with no success.

Thanks

Report message to a moderator

Re: Delete statement being running for over 2hrs [message #510638 is a reply to message #510637] Mon, 06 June 2011 22:31 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
While you may know & understand what needs to be done, we only know what you post here about your situation.
post DDL for table
what EXACTLY constitutes a duplicate for you?
post SQL that SELECT duplicates
For any 2 or more rows that are classified as duplicates, which row is kept & which rows are discarded?

Report message to a moderator

Previous Topic: Speed Up the gathering of stats.
Next Topic: Database Performance is very slow
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 01:05:52 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.