| Lot of time being taken to delete the data from a table [message #552620] |
Fri, 27 April 2012 01:28  |
 |
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
|
Junior Member |
|
|
Hi Guys ,
I have a table which contains 8,21,177 amount of data totally.
Now I am trying to delete around 4,84,000 of data from this table by using just one filter i.e. my query is something like below
DELETE /*+ parallel(resource,4) */ FROM resource where created_by = 'MIGN'
This is going to delete 4,84,000 rows of data . But my current issue is this is taking lots of time to delete the data . To be precise , its almost taking 25 hours to delete this data
The created_by column is indexed .
Please see my explain plan.
Execution Plan
----------------------------------------------------------
Plan hash value: 2389236532
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | DELETE STATEMENT | | 499 | 20459 | 39 (0)| 00:00:
01 |
| 1 | DELETE | RESOURCE | | | |
|
|* 2 | COUNT STOPKEY | | | | |
|
|* 3 | INDEX RANGE SCAN| IND_CREATD_BY | 703 | 28823 | 4 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<500)
3 - access("CREATED_BY"='MIGN')
Statistics
----------------------------------------------------------
52093 recursive calls
63033 db block gets
4436638 consistent gets
5114 physical reads
655168 redo size
403 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed
Can Someone please help me to find out what is wrong in this case.
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552622 is a reply to message #552620] |
Fri, 27 April 2012 01:46  |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
The problem is not with your statement, it is with your environment, log size, log switch frequency, concurrent workload, io or cpu shortage...
Run Statspack/AWR report to the issue.
ALso check reference keys with on delete cascade, triggers.
In addition, as you delete more than 1/2 of your table data, maybe it is better to create a table with the data you want to keep, truncate the original tables and insert back (in append mode) the saved data.
Note that your "parallel" is useless as not taken into account.
Regards
Michel
[Updated on: Fri, 27 April 2012 01:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552634 is a reply to message #552632] |
Fri, 27 April 2012 02:15 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Oracle thinks it has to delete only 703 rows. That is why it using an index range scan, which is insane when deleting half the rows. You need to use dbms_stats.gather_table_stats with
method_opt=>'for all columns size auto'
to build up histograms. Or just drop the index, and if you really want it (why?) re-create it afterwards.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552676 is a reply to message #552666] |
Fri, 27 April 2012 04:32 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You format an explain plan by enclosing it in code tags as described in the link. Then you get something like this:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
That's formatted, you only have to type 2 words.
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552683 is a reply to message #552676] |
Fri, 27 April 2012 05:35 |
|
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
|
Junior Member |
|
|
Hi Michael ,
Its not that case . I did try and format the code second time and posted it but then i didnt get very positive reply for that . That was the reason i didnt continue it further. This issue needs quick resolution for me as it needs to go live by next week. So i didnt have much time to go through the entire OraFAQ guidelines . If it was the code then i would have done it but as it was the explain plan , i wasnt able to do that . I tried to do whatever best possible formatting from my side.
You are all the masters and i thought it would be foolish of me to argue on how to format
But i would sincerely request you if you could help in resolving this issue as I have people chasing around me for this issue. Please let me know If the below formaating would work for you. This is as per suggested by cookiemonster
When i tried using the select * from table(dbms_xplan.display) , I got the below output
Plan hash value: 2106142224
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 499 | 31437 | 2800 (4)| 00:00:34 |
| 1 | DELETE | CSP_RESOURCE | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| CSP_RESOURCE | 481K| 28M| 2800 (4)| 00:00:34 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<500)
3 - filter("CREATED_BY"='WCRM_MIGN')
This is my original query
DELETE FROM csp_resource where created_by = 'WCRM_MIGN'
and rownum < 500
--------------
update: added [code] tags. JW.
[Updated on: Fri, 27 April 2012 07:21] by Moderator Report message to a moderator |
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552686 is a reply to message #552683] |
Fri, 27 April 2012 05:41 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
harsh shah wrote on Fri, 27 April 2012 11:35
But i would sincerely request you if you could help in resolving this issue as I have people chasing around me for this issue. Please let me know If the below formaating would work for you. This is as per suggested by cookiemonster
I suggested you read the link Michel and John posted.
Here it is again:
Please read and follow How to use [code] tags and make your code easier to read?
It will take you 30 seconds to read it.
It'll tell you what you need to do to make your explain plan look like mine.
It takes no effort
|
|
|
|
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552689 is a reply to message #552687] |
Fri, 27 April 2012 06:15 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Oh Good Grief!
Look at my explain plan. See how all the columns line up? That's because I've used code tags.
Now see yours. Do the columns line up? No they don't.
Which do you think is easier to read?
You've spent far more time explaining why you haven't used code tags then it would have taken you to repost the explain with code tags.
So stop arguing and post the explain plan in code tags like we asked the first time.
|
|
|
|
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552696 is a reply to message #552693] |
Fri, 27 April 2012 06:57 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
harsh shah wrote on Fri, 27 April 2012 12:21
I dont understand why are people here more interested in getting the correct format of the issue done instead of the actual issue .
What is stopping anyone to understand the explain plan pasted by me . If i as a normal developer am able to understand from what i have pasted , then i am sure anybody can understand what i have pasted.
This is like harrasing instead of helping.
Since the explain plan is only 5 lines, yes we can read it.
However people post longer stuff all the time and that's almost impossible to follow without code tags.
Lots of people want help here and a lot of stuff is unreadable without code tags.
So we insist on code tags for eveything in order to make everyone's life easier.
Otherwise we're just going to have constant arguments about whether or not they are necessary in certain cases.
We're not the only forum with rules like this you know.
You're insisting you should be exempt from the rules everyone else has to follow, what makes you so special?
You have an urgent problem, so do lots of other posters here. And we all have day jobs.
The easier you make it for everyone else to follow your issue, here or on any other forum, the more likely it is you'll get helpful answers.
harsh shah wrote on Fri, 27 April 2012 12:21
I am sorry as i have not studied the OraFaq standards in detail. Next time i will do a brief course on that and then raise an issue.
The code tages page has 3 lines of text and 2 images. Not 30 lines, or 300 lines, 3.
You keep acting like we're asking you to do something complicated and/or time consuming.
Neither is true.
In fact using code tags requires you to type a grand total 11 extra characters in your post.
Which you would know if you actually clicked the link and read what it says.
|
|
|
|
| Re: Lot of time being taken to delete the data from a table [message #552701 is a reply to message #552693] |
Fri, 27 April 2012 07:25 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've added the [code] tags to your last post of an execuition plan. It took me 12 seconds 
Do you see that it is now readable?
Having gathered startistics, you are now getting a plan that makes a bit more sense. And if you enable parallel DML (I've already told you how to do that, though you didn't bother to reply) it should be pretty quick.
Happy now?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
