CODEWITHSUNDEEP
oracle-databaseperformanceplsqlquery-optimizationbulk-operations
Praveen Nandyala
Praveen Nandyala

Reputation: 41

How to delete huge rows in oracle table using parallel sessions query quickly

I am using mentioned query to delete 250 million plus rows from my table and it is taking more time

I have tried with t_delete limit with up to 20000.

Still slow deletion happening.

Please suggest a few optimisations in the same code to done my job faster.

DECLARE
TYPE tt_delete IS TABLE OF ROWID; t_delete tt_delete;
CURSOR cIMAV IS SELECT ROWID FROM moc_attribute_value where id in (select 
id from ORPHANS_MAV);

total Number:=0;
rcount Number:=0;
Stmt1 varchar2(2000);
Stmt2 varchar2(2000);
BEGIN
---    CREATE TABLE orphansInconsistenDelProgress (currentTable 
VARCHAR(100), deletedCount INT, totalToDelete INT);
---    INSERT INTO orphansInconsistenDelProgress (currentTable, 
deletedCount,totalToDelete) values ('',0,0);
Stmt1:='ALTER SESSION SET parallel_degree_policy = AUTO';
Stmt2:='ALTER SESSION FORCE PARALLEL DML';
EXECUTE IMMEDIATE Stmt1;
EXECUTE IMMEDIATE Stmt2;
---   ALTER SESSION SET parallel_degree_policy = AUTO;
---   ALTER SESSION FORCE PARALLEL DML;
COMMIT;

--- MOC_ATTRIBUTE_VALUE

SELECT count(*) INTO total FROM ORPHANS_MAV;
UPDATE orphansInconsistenDelProgress SET currentTable='ORPHANS_MAV', 
totalToDelete=total;
rcount := 0;
OPEN cIMAV;
LOOP
    FETCH cIMAV BULK COLLECT INTO t_delete LIMIT 2000;
            EXIT WHEN t_delete.COUNT = 0;
            FORALL i IN 1..t_delete.COUNT
        DELETE moc_attribute_value WHERE ROWID = t_delete (i);
    COMMIT;
    rcount := rcount + 2000;
    UPDATE orphansInconsistenDelProgress SET deletedCount=rcount;
END LOOP;
CLOSE cIMAV;
COMMIT;
END;
/

Upvotes: 2

Views: 4592

Answers (3)

Rick James
Rick James

Reputation: 142453

If you are keeping only a fraction of the rows, it is likely to be much faster to copy over the rows to keep, then swap tables and delete the old table.

(No I don't know the threshold at which this is faster than DELETEing.)

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36902

A single Oracle parallel query can simplify the code and improve performance.

declare
    execute immediate 'alter session enable parallel dml';

    delete /*+ parallel */
    from moc_attribute_value
    where id in (select id from ORPHANS_MAV);

    update OrphansInconsistenDelProgress
    set currentTable = 'ORPHANS_MAV', 
    totalToDelete = sql%rowcount;

    commit;
end;
/

In general, we want to either let Oracle break the task into pieces or use our own custom chunking. The original code seems to be doing both - it reads the data in chunks, and then submits each chunk to be further divided into a parallel delete. That approach generates lots of tiny pieces, and Oracle likely wastes a lot of time on things like thread coordination.

Deleting a large number of rows is expensive because there's no way to avoid REDO and UNDO. You might want to look into using DDL options, such as truncating a partition, or dropping and recreating the table. (But be careful recreating objects, it's difficult to perfectly recreate complex objects. We tend to forget things like privileges and table options.)

Tuning parallelism and large jobs is complicated. It's important to use the best monitoring tools, to ensure that Oracle is requesting, allocating, and using the right number of parallel processes, and that the execution plan is correct. One strong advantage of using a single SQL statement is that you can use real-time SQL monitoring reports to monitor progress. If you have the Diagnostics and Tuning Pack licenses, find the SQL_ID in GV$SQL and generate the report with select dbms_sqltune.report_sql_monitor('your SQL_ID here');.

Upvotes: 1

yuvalchen
yuvalchen

Reputation: 133

maybe use SQL TRUNCATE TABLE,

Truncate table is faster and uses lesser resources than DELETE TABLE command.

Upvotes: 0

Related Questions