Python循环调用Oracle存储过程执行缓慢，如何优化性能？

我编写了一个连接Oracle数据库的Python脚本，先通过SELECT语句获取约600个Reference ID，再通过For循环逐个调用名为DeleteStoredProcedure的存储过程执行删除操作。相关代码如下：

sql = f"SELECT STATEMENT"

cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    cursor.callproc('DeleteStoredProcedure', [i[0]])
    print("Deleted:", i[0])

该存储过程可根据Reference ID删除三张不同表中的数据，在Oracle Toad中执行速度较快，但Python脚本中循环执行耗时约3分钟，若数据量达1万条以上耗时将更长。请问有什么方法可以提升性能？

1. 修改存储过程支持批量删除（最优方案） #

当前逐个调用存储过程的核心开销是网络往返次数过多（600次调用对应600次网络请求）。最有效的优化是调整存储过程，让它支持接收批量Reference ID，一次性完成所有删除逻辑：

• 先在Oracle中定义自定义集合类型，作为存储过程的输入参数
• 在存储过程内部通过集合关联三张表，用IN子句批量删除数据
• Python端将所有Reference ID打包成集合参数，一次调用完成所有操作

示例步骤 #

Oracle端创建集合与修改存储过程

-- 创建存储过程需要的集合类型（假设Reference ID为数字类型）
CREATE OR REPLACE TYPE REF_ID_LIST AS TABLE OF NUMBER;
/

-- 批量删除存储过程
CREATE OR REPLACE PROCEDURE DeleteStoredProcedure_Batch(p_ref_ids REF_ID_LIST)
IS
BEGIN
  -- 批量删除三张表数据
  DELETE FROM table1 WHERE ref_id MEMBER OF p_ref_ids;
  DELETE FROM table2 WHERE ref_id MEMBER OF p_ref_ids;
  DELETE FROM table3 WHERE ref_id MEMBER OF p_ref_ids;
  COMMIT;
END;
/

Python端调用批量存储过程

sql = "SELECT STATEMENT"
cursor.execute(sql)
result = cursor.fetchall()
ref_ids = [row[0] for row in result]

# 获取Oracle自定义集合类型对象
ref_id_list_type = connection.gettype("REF_ID_LIST")
ref_id_array = ref_id_list_type.newobject()
ref_id_array.extend(ref_ids)

# 一次调用完成批量删除
cursor.callproc('DeleteStoredProcedure_Batch', [ref_id_array])
print(f"Deleted {len(ref_ids)} records")

2. 数组绑定批量执行（无需修改存储过程） #

如果无法调整存储过程，可利用Python驱动的数组绑定功能，把多次存储过程调用打包成一次网络请求：

import cx_Oracle  # 或oracledb（Oracle官方新驱动）

sql = "SELECT STATEMENT"
cursor.execute(sql)
result = cursor.fetchall()
ref_ids = [row[0] for row in result]

# 批量绑定参数执行存储过程
cursor.setinputsizes(None, cx_Oracle.NUMBER)  # 根据ID实际类型调整
cursor.executemany("BEGIN DeleteStoredProcedure(:1); END;", ref_ids)
connection.commit()
print(f"Deleted {len(ref_ids)} records")

3. 减少不必要的开销 #

• 去掉循环中的print语句：频繁打印会增加IO耗时，改为最后打印总处理数量即可
• 统一事务提交：避免每次调用存储过程后隐式提交，改为所有操作完成后一次性提交，减少事务开销

4. 优化数据库层性能 #

• 确认三张表的ref_id字段已创建索引，确保删除操作的查找效率
• 检查存储过程内部是否存在冗余逻辑、锁等待或触发器，这些会拖慢删除速度
• 优化SELECT语句：添加必要索引、过滤条件，减少数据传输量

5. 调整连接配置 #

• 使用最新版本的oracledb（Oracle官方替代cx_Oracle的新驱动），新版本有性能优化
• 调整游标预取行数，提升查询数据的效率：

cursor.prefetchrows = 1000  # 预取更多行，减少查询时的网络往返

内容的提问来源于stack exchange，提问作者Suhail Ahmad