CODEWITHSUNDEEP
python-3.xoracle-databasecx-oracle
Kaushik Vezzu
Kaushik Vezzu

Reputation: 25

Updating rows in database through python script gets stuck suddently

I am intending to update nearly 500k rows in database. I have a python script which does this by taking 1000 as the batch size (if i give batch size more than 1000, it is restricting me stating 1000 is the maximum limit). This is a sample update query which i will be running for every 100 account id's

UPDATE <table_name>
        SET INDICATOR = 'N'
        WHERE ACCOUNT_ID IN ('456456456',.........,'123123123') AND CONVERSION = 'C2' AND INDICATOR = 'Y';

While i am running this script all of a sudden it gets stuck at cursor.executemany(sql query) .

For example, I have 100 000 data, i have 100 batches, each 1000 account ids. When i start executing the script, lets say 65 batches will be executed in no time and for 66 batch, it will get stuck at the above said line. This will not happen all the time and happens rarely.

The solution which i got is, when is gets stuck there, i go to the database and execute EXEC DBMS_STATS.GATHER_TABLE_STATS('<schema name>', '<tablename>'); this plsql procedure. After executing this command within no time all other batches will be executed.

This is my issues. Why it is getting stuck all of a sudden? What is the above command doing so that the execution is resuming? What i need to do to avoid the sudden stoppage in my code. I dont want to use the above plsql procedure.

Can you please help me understand what is happening and how do i rectify it.

Upvotes: 0

Views: 64

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10691

Your problem sounds like a lock issue. But regarding the general technique, with executemany() you shouldn't need the IN clause. Just bind all the values. Follow the same technique as shown in https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html#batch-execution-of-sql

Untested code:

sql = """UPDATE mytab 
         SET INDICATOR = 'N' 
         WHERE ACCOUNT_ID = :1 AND CONVERSION = 'C2' AND INDICATOR = 'Y'"""
data = [
    (456456456,),
    (123123123,),
]
cursor.executemany(sql, data)

Also, upgrade from cx_Oracle to python-oracledb, see the release announcement.

Upvotes: 0

Eduardo Cunha
Eduardo Cunha

Reputation: 131

Try using parallelism in your update, as per hint:

Your update today:

UPDATE <table_name> SET INDICATOR = 'N' WHERE ACCOUNT_ID IN ('456456456',.........,'123123123') AND CONVERSION = 'C2' AND INDICATOR = 'Y';

Try this:

UPDATE /*+parallel(12) enable_parallel_dml */ <table_name> SET INDICATOR = 'N' WHERE ACCOUNT_ID IN ('456456456',.........,'123123123') AND CONVERSION = 'C2' AND INDICATOR = 'Y';

Take care of your environment's resources because parallelism requires CPU, please test in DEV first.

Upvotes: 0

Related Questions