Delete statement takes a long time to commit

[trishtzy]

1. What versions are you using?
   platform.platform: Linux-4.15.0-107-generic-x86_64-with-glibc2.2.5
   sys.maxsize > 2**32: True
   platform.python_version: 3.8.2
   cx_Oracle.version: 8.0.1
   cx_Oracle.clientversion: (19, 8, 0, 0, 0)

2. Describe the problem

I need to execute a delete statement that completes under 7 minutes.

I've about 168k rows of data to be deleted. When I use execute the delete statement in TOAD application, it completes under 2 minutes. But when using python script and cx_oracle, it takes more than 15 minutes to be completed.

Is there a better way of deleting 100k rows of data using cx_oracle?

3. Include a runnable Python script that shows the problem.

delete_sql = "delete from ARCHIVE_WORK where pxinsname = :1"
try:
    cursor.executemany(delete_sql, dealsToDelete)
    connection.commit()
except cx_Oracle.Error as error:
    error_alert(error)

where dealsToDelete is a list of lists.
dealsToDelete = [['D-123'], ['D-234'], ['D-345']]
So if there are 168k rows, length of dealsToDelete is 168k.

[cjbj]

How do you execute it in TOAD?

[trishtzy]

Hey @cjbj thanks for the quick response.

In TOAD, I executed:

DELETE FROM ARCHIVE_WORK WHERE status IN ('Completed','Withdrawn') AND PXUPDATEDATETIME < SYSDATE;

I did try the same command in cx_oracle where I call

raw_query = """
  DELETE FROM ARCHIVE_WORK WHERE status IN ('Completed','Withdrawn') AND PXUPDATEDATETIME < SYSDATE;
"""
cursor.execute(raw_query)
connection.commit()

It took more than 15 mins and the temporary container timed out.

[cjbj]

Did it take the same time in TOAD? Did you commit in TOAD?

Performance of a SQL statement that is run on the DB is not something that is in cx_Oracle's control. Neither is the time taken to commit.

Do you need to rethink your data model and processes? Would partitioning help? The best place to ask would be https://asktom.oracle.com/

[trishtzy]

TOAD took 2-3mins and I did commit. Hence I was wondering what's the difference when using cx_oracle.

[cjbj]

What is the timing with SQL*Plus?

How are you making sure each tool is deleting the same set of records?

[trishtzy]

Hey, looks like there were additional index processes that were going on that particular table, hence deletion took a long time.

That Toad query that executed fast might be a one-off event.

Thanks for your replies and questions! Closing thread