Home » RDBMS Server » Performance Tuning » Lot of time being taken to delete the data from a table (Oracle 10G)
Lot of time being taken to delete the data from a table [message #552620] Fri, 27 April 2012 01:28 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
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 #552628 is a reply to message #552620] Fri, 27 April 2012 01:57 Go to previous messageGo to next message
John Watson
Messages: 4862
Registered: January 2010
Location: Global Village
Senior Member
Analyze the table, the statistics are wrong.
Re: Lot of time being taken to delete the data from a table [message #552631 is a reply to message #552622] Fri, 27 April 2012 02:08 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Hi Michael ,

Firstly I am really sorry for not following the required guidelines as this was my first query posting on this portal.
I will take care of that from next time.

I have checked and there are no triggers associated to this tables and also I have deleted all the rows from child tables.

Yes I agree that creating another table and then transferring the needed data again into this table would be the best option .
But My concern over that approach was that this is our core table which is used in all the pages of my application so would there be any major risk in taking this approach?
Also , what all back up should I take before going for this approach apart from taking the needed data , existing indexes and constraints?

Just for knowledge , could you please let me know how did you identify that parallel hint is not being utilized?
Re: Lot of time being taken to delete the data from a table [message #552632 is a reply to message #552628] Fri, 27 April 2012 02:09 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Hi John ,

I have already executed the below statement

ANALYZE TABLE RESOURCE COMPUTE STATISTICS;

Do I need to analyze something else also?
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 Go to previous messageGo to next message
John Watson
Messages: 4862
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 #552641 is a reply to message #552634] Fri, 27 April 2012 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also the plan you gave us is not the one for the statement you posted.

|* 2 | COUNT STOPKEY => 2 - filter(ROWNUM<500)

There is no rownum in your statement.

Regards
Michel
Re: Lot of time being taken to delete the data from a table [message #552645 is a reply to message #552631] Fri, 27 April 2012 03:09 Go to previous messageGo to next message
John Watson
Messages: 4862
Registered: January 2010
Location: Global Village
Senior Member
Quote:
could you please let me know how did you identify that parallel hint is not being utilized?

alter session enable parallel dml;
Re: Lot of time being taken to delete the data from a table [message #552647 is a reply to message #552645] Fri, 27 April 2012 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Parallel execution shows up in the explain plan. There's nothing there to indicate it.
However, since this is, as Michel pointed out, the wrong plan, nothing has been proved.
I suggest you post the correct plan.
Re: Lot of time being taken to delete the data from a table [message #552648 is a reply to message #552647] Fri, 27 April 2012 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or the correct query
Re: Lot of time being taken to delete the data from a table [message #552655 is a reply to message #552648] Fri, 27 April 2012 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I now suspect there is a loop on DELETE statements with intermediate commit every 500 rows and thus the bad peformances may be quite obvious.


Regards
Michel
Re: Lot of time being taken to delete the data from a table [message #552657 is a reply to message #552648] Fri, 27 April 2012 03:34 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Ok I just forgot to mention that for testing purpose , i was using this query for which i showed the explain plan

DELETE FROM resource where created_by = 'MIGN'
and rownum < 500

I used the below statement to gather the statisitcs
EXECUTE dbms_stats.gather_table_stats(ownname => 'CSP' , tabname => 'RESOURCE' , method_opt => 'for all columns size auto')

After executing the above statistics , the new explain plan is like below

SQL> set autotrace on ;
SQL> DELETE FROM csp_resource where created_by = 'WCRM_MIGN'
2 and rownum < 500;

499 rows deleted.
Elapsed: 00:01:35.89

Execution Plan
----------------------------------------------------------
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|485K|29M|2800(4)| 00:00:
34 |

--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<500)
3 - filter("CREATED_BY"='WCRM_MIGN')
Statistics
----------------------------------------------------------
52089 recursive calls
60467 db block gets
4441951 consistent gets
370 physical reads
643956 redo size
379 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed


Hi James ,

Now the index is not being used , so then how can I improve this
Re: Lot of time being taken to delete the data from a table [message #552658 is a reply to message #552657] Fri, 27 April 2012 03:41 Go to previous messageGo to next message
John Watson
Messages: 4862
Registered: January 2010
Location: Global Village
Senior Member
Your post is unreadable. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read You did say that you would do this:
Quote:
I am really sorry for not following the required guidelines as this was my first query posting on this portal.
I will take care of that from next time.
Re: Lot of time being taken to delete the data from a table [message #552659 is a reply to message #552658] Fri, 27 April 2012 03:47 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Hi James ,

But the explain plan is not a part of my code . It is automatically generated.
As far as my code is concerned , it is just

DELETE FROM resource where created_by = 'MIGN'
and rownum < 500

So how am i supposed to format the explain plan
Re: Lot of time being taken to delete the data from a table [message #552662 is a reply to message #552659] Fri, 27 April 2012 03:51 Go to previous messageGo to next message
John Watson
Messages: 4862
Registered: January 2010
Location: Global Village
Senior Member
You have been asked to read the forum guide and to formatting code by Michel, and again by me. You have ignored the requests. And you have not once said "thank you".
Such behaviour is not nice when you are asking for unpaid assistance.

Good bye.
Re: Lot of time being taken to delete the data from a table [message #552664 is a reply to message #552662] Fri, 27 April 2012 03:53 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Ok Thanks John And Michael . Thanks a lot for your time.
I will try to get it resolved
Re: Lot of time being taken to delete the data from a table [message #552666 is a reply to message #552664] Fri, 27 April 2012 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You prefer to leave and do not get help just because you don't want to help us to help you formatting your post? I really don't understand!

Regards
Michel
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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 #552687 is a reply to message #552686] Fri, 27 April 2012 05:53 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Hi Cookiemonster,

Thanks for your help.
I went through the guidelines but isnt that for pasting the actual code.
But i dont have a code as i just have a explain plan that has to be posted.

I have already posted the result of the query which you suggested.
select * from table(dbms_xplan.display)
Re: Lot of time being taken to delete the data from a table [message #552688 is a reply to message #552687] Fri, 27 April 2012 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you didn't take enough time to read the links and obviously did not click on "Preview" button to see if it posts in the same way than others' posts. Compare your plan and cookiemonster's one, don't you see the difference?

Regards
Michel
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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 #552693 is a reply to message #552689] Fri, 27 April 2012 06:21 Go to previous messageGo to next message
harsh shah
Messages: 9
Registered: April 2012
Location: Pune
Junior Member
Oh here we go now ,

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.
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.
I mean are you telling me that you are not able to understand the explain plan from what i have pasted
Thanks all for all your valuable time and sorry for wasting that time of yours .
Re: Lot of time being taken to delete the data from a table [message #552694 is a reply to message #552693] Fri, 27 April 2012 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dont understand why are people here more interested in getting the correct format of the issue done instead of the actual issue .


It is a test, if you can't properly poist then you are able to understand the answers it is not worth we waste our time to try to explain them.

Quote:
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.


Why don't you jkust repost with a ocrrect format instead of trying to understand if we are able to read your post or not?
We don't ask you to think just to post, so don't think and post even if you don't understand why you should do it.
You know we have no problem you stay with yours.

Quote:
Thanks all for all your valuable time and sorry for wasting that time of yours .


Once again you choose to leave, you should forget the computer world if you leave at the first tiny issue.

Regards
Michel

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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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 Go to previous message
John Watson
Messages: 4862
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 Smile
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?

Previous Topic: Locking of two sessions
Next Topic: OWB Cube load SQL tuning
Goto Forum:
  


Current Time: Sat Dec 20 18:45:53 CST 2014

Total time taken to generate the page: 0.05943 seconds