Jump to content
skywalker

Delete query very slow

Recommended Posts

Hi,

I am executing following sql from stored procedure and it is taking more than 20 minutes to delete 5 million records from table. 

Can some one please advise?

DELETE
FROM AGMS_HOST_ASSET_GROUP
WHERE HOST_ID IN
  (SELECT ID
  FROM AGMS_USER_HOST UH
  WHERE UH.IP_INT IN
    (SELECT * FROM TABLE(:B2 )
    )
  AND UH.SUBSCRIPTION_ID = :B1
  )
AND ASSET_GROUP_ID IN
  (SELECT * FROM TABLE(:B3 )
  )

The binding (:B2) is an oracle array which has 10_000 ids and I am collecting these ids in batch on 10_000 only.

The binding (:B3) is an oracle array but it has only 5-10 ids.

The table AGMS_HOST_ASSET_GROUP has 12 million rows where as AGMS_USER_HOST has 70 million rows.

Execution plan of this sql is attached below.

After gather stats:execution-plan.thumb.jpg.fc5eee2690ff521372764efe8219f7da.jpg

EXEC dbms_stats.gather_table_stats('GRC','AGMS_USER_HOST',cascade=>TRUE);
EXEC dbms_stats.gather_table_stats('GRC','AGMS_HOST_ASSET_GROUP',cascade=>TRUE);

Share this post


Link to post
Share on other sites

Hi, and welcome to the forum!

There are many things that can improve delete performance, please read:

http://www.dba-oracle.com/t_oracle_delete_tuning.htm

iIs this a partitioned table or a large table?

If that is true and you have cou_count > 1, you can parallelism the delete.

if most of the table rows are being deleted, look at using CTAS.

Since this is in a stored procedure, look at using bulking:

http://www.dba-oracle.com/t_delete_performance_speed.htm

Look at the forall PL/SQL operator:

http://www.dba-oracle.com/t_forall_improve_delete_performance.htm

Lastly, creating a temp table using the WITH clause can speed up deletes;

http://dba-oracle.com/oracle_subquery_factoring_with_clause_dml.htm

Good luck!

 

Share this post


Link to post
Share on other sites

- DELETE Statement with One Condition

If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.

Let's start with a simple example of a DELETE query that has one condition in the WHERE clause.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Enter the following DELETE statement:

Try It

DELETE FROM suppliers
WHERE supplier_name = 'Microsoft';

There will be 1 record deleted. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

This example would delete all records from the suppliers table where the supplier_name is 'Microsoft'

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×