Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Deleting a million rows slows down SELECT statement.

Posted on 2010-09-16
15
Medium Priority
?
749 Views
Last Modified: 2012-05-10
Oracle 10.2.0.1.0
RHEL 4.6

We have six tables that contain an ID. We want to delete all the rows in these tables for the same ID.

We are using the following delete statement: delete from <table name> where id=1;

We do this same delete for all six tables.

Once we do this, any code performing a select on these tables takes a long time to finish. This happens even on empty tables. There will be up to 1 million rows with this same ID. We have tried several things. Performing commits after all the deletes are done, performing a commit after each delete. The only thing that really seems to work is to completely truncate the tables. I have looked online and found that some people move the data they need to keep to a temp table, delete the old table, and rename the temp table to the old table name. We can’t do this because the tables need to be accessible at all times.

Any suggestions?
0
Comment
Question by:newbie216
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 33693836
remember to gather new statistics on your tables and indexes after a major data purge.

you might also want to coalesce your indexes, assuming the slow queries are on indexed reads of the id column
0
 
LVL 6

Expert Comment

by:sridharv9
ID: 33695213
Agree with comment ID:33693836. Gathering fresh statistics after huge delete/any DML activity  should pretty much take care of this issue.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33695871
Hi,

The is the effect of 'delayed block cleanount'.
When you update a lot of rows (such as your delete) Oracle will not go to each block touched in order to then blocks after the commit (i.e removing the locks). Then next sessin that reads those blocks will do that cleaning when it sees that the session that has locked the rows has commited. Se the select is longer because it has some more work to do.

Try to do a full table scan (select /*+ FULL(t) from table t) after the delete so that it does all the block cleanouts, and then see if the following selects have normal performance.

Regards,
Franck.
0
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33697777
Can you provide the current sql statement ( exactly as it is ) and it's explain plan.
0
 

Author Comment

by:newbie216
ID: 33698213
Just a heads up - I am not a DBA, so if I ask questions that don’t make sense or more clarification is needed just let me know. I am actually helping some other people out while I have some free time.

---------------------------------------------------------------

sdstuber & sridharv9

What do you suggest as far as gathering stats? It seems everyone does their own thing here, but here are two ways I found to do it.

First:

dbms_stats.gather_database_sta ts(
cascade=> TRUE,
gather_sys=> FALSE,
estimate_percent=> null,
degree=> DBMS_STATS.DEFAULT_DEGREE,
no_invalidate=> TRUE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO',
options=> 'GATHER');
END;
/

Source: <http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/dbms_statsgather_table_statsis-it-ran-automatically-in-oracle-10g-2658373>


Second:

DBMS_STATS.gather_schema_stats(
ownname=>’<schema>’,
estimate_percent=>dbms_stats.auto_sample_size
cascade=>TRUE,
method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

Source: http://www.dba-oracle.com/art_builder_dbms_stats.htm

---------------------------------------------------------------

franckpachot

I tried your suggestion and it did not help. Here is what I did:

delete from <table name> where id=1;
commit;
select /*+ FULL(t) */ * from <table name> t;

This was performed on all six tables. Let me know if I did something wrong.

---------------------------------------------------------------

nav_kum_v

I will have to work out the explain plan on Monday. I will be out for the rest of this weekend, but I will get something to you as fast as I can. As I have never done this before it may take me some time to figure out how to do this.

Thanks everyone.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 33700736
This is expected oracle behavior. Its slow when you delete millions.

Have you tried CTAS (create as select)? This is the same as creating the TEMP table then switch. But it will take very less downtime (in secs).

1) use CTAS to cretae temp_tbl as select from org_tbl where
2) do the index,stats on the temp_tbl
3) rename the temp_tbl to orig

Try this in your TEST and make you own judgement.

HTH,

~sve
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33701190
since you're just modifying one table, I would gather stats on that one table
if you can do a full compute (percent null) then I would, if that will take too long and consume too many resources then set to whatever sample size you can accomodate

dbms_stats.gather_table_stats(ownname => yourowner,tabname =>yourtable, estimate_percent=>null, cascade =>TRUE);
0
 
LVL 18

Expert Comment

by:sventhan
ID: 33701860
Here is what I've been using

execute dbms_stats.gather_schema_stats(OWNNAME => owner_name -
                                        ESTIMATE_PERCENT => dbms_stats.auto_sample_size, -
                                        DEGREE => dbms_stats.auto_degree, -
                                        GRANULARITY => 'AUTO', -
                                        CASCADE => dbms_stats.auto_cascade);


estimate_percent => 0.000001 < ============= Can be set to minimum if you've a large table (200m+ rows)
0
 

Author Comment

by:newbie216
ID: 33737687
I have been out sick so sorry for the late reply.

---------------------------------------------------------------

sventhan

I will keep this in mind if we have no other choice. I still need to make sure this table is up 24/7, so I don't think your solution will work for us.

---------------------------------------------------------------

sdstuber

I tried doing what you suggest, but it does not seem to work. This is the script I am running:

delete from <table name> where id=1;
commit;
exec dbms_stats.gather_table_stats(ownname => '<owner>', tabname => '<table name>', estimate_percent => null, cascade => TRUE);

This is done for six different tables. It still seemed to have problems. Why would a truncate on the table work fine, but deleting specific rows not work? Is the logging causing a problem?

To coalesce the indexes I found the following example:

alter index <index name> coalesce;

Can I just add this for all indexes that apply? Is there another suggestion?

---------------------------------------------------------------

sventhan

I will have to try this out. I am just worried about doing this for the entire schema. I am not sure how long this will take and we do have some time constraints. We have to be able to delete all 1 million rows for a specific id and have the software working within an hour. The software will still need to work for other ids, so I can't move the table around or have the table be inaccessible.

---------------------------------------------------------------

Thanks for the replies. I will be out on travel for a week (work not pleasure), so my replies and testing will be even more sporadic than usual. I will do my best to keep on top of these suggestions and work them out as quickly as possible.
0
 

Author Comment

by:newbie216
ID: 33738069
I tried to run the coalesce command: alter index <index name> coalesce;

I received the following error:

ora-00439: feature not enabled: Coalesce Index

Appearantly this is not available in Standard Edition. Is this true? I did not mention that we were running Standard Edition when I wrote this question up. Sorry about that. If this changes anything else let me know.
0
 
LVL 6

Expert Comment

by:sridharv9
ID: 33836425
From metalink notes:


Option                          10G     10G      10G     10G      Notes  
                                Stand.     Stand.    Entrp.  Pers.      
                                Edit.           Edit.   Edit.   Edit.
                             One                
---------------------------------------------------------------------------------
Index coalesce             N        N         Y         Y       Defragments free space    
                                                                       in index leaf blocks
                                                         while a table is online.
0
 

Author Comment

by:newbie216
ID: 33879724
I understand that the standard edition cannot use index coalesce to clean up space, but is there anything else that can be done?
0
 
LVL 6

Accepted Solution

by:
sridharv9 earned 2000 total points
ID: 33879912
I would go with  rebuild index , if necessary perform it  online.
0
 

Author Comment

by:newbie216
ID: 34087023
sridharv9:

I am working on a lot of things right now, but I will try your suggestion and see what that does for us. I am mainly replying to the question does not come up abandonded.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question