英文:
PL/SQL multi-threading
问题
我正在尝试为现有的单线程项目开发新的多线程进程。我尝试使用"DBMS_PARALLEL_EXECUTE"包。但问题是，当我创建例如5个块时，在执行过程中，块会相互等待。如果一个块在工作，另一个块会等待。我经过多次测试得出这个结论。
我希望您帮助我修复我的代码，使所有块能够同时并行执行，而不是等待彼此。
在附件中，您可以看到所有块分别工作，但主要作业的总时间（1696.2）等于所有块的工作时间。因此，我认为所有块都在等待彼此。您可以在屏幕2上看到计算结果。
屏幕1：
屏幕2：
英文:
I have a task about bulk process of accounts(count of accounts > 20000) using pl sql.
For accounts there are a lot of validations and also for now it works on single thread mode. And now existing job works more than 1 hour for this accounts.
I'd like implement multi thread mode for this project. Read a lot informations about multi threading on pl sql. And I found that I can use "DBMS_PARALLEL_EXECUTE" package.
I developed new logic with "DBMS_PARALLEL_EXECUTE" and It is works, but I see that during execution, all chunks waits each other.
For example: I create 5 chunk. After job completed I see that 1st chunk worked for example 10 minutes, 2nd 11 minutes, 3rd 9 minutes, 4th 5 minutes and 5th 13 minutes. According to this results job has to be compeleted in 13 minutes if all chunks works parallely at the same time.
But job completes working in 10 + 11 + 9 + 5 + 13 minutes. And it means that each chunk works after another one.
But I wanna all chunks work at the same time parallel. How can I achive that, run all chunks parallel at the same time?
I put here example of my code which implemented "DBMS_PARALLEL_EXECUTE". (just draft codes for test)
DECLARE
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
v_task VARCHAR2(20) := 'T89';
v_plsql_block VARCHAR2(1000);
v_status VARCHAR2(50);
v_status_desc VARCHAR2(100);
v_additional_sql VARCHAR2(1000);
v_original_sql VARCHAR2(1000);
v_count_of_chunks NUMBER := 5;
v_start_id NUMBER := 1;
v_per_thread_count NUMBER := 0;
v_count_all_acc NUMBER;
BEGIN
v_original_sql := 'SELECT DISTINCT
oac.cust_acc
FROM
xxtb_ovrdft_acc oac
WHERE
NOT EXISTS (
SELECT
1
FROM
xxtb_failed_acc acc
WHERE
acc.curr_acc = oac.cust_acc
AND acc.err_status = :status
)';
SELECT
COUNT(DISTINCT oac.cust_acc)
INTO v_count_all_acc
FROM
xxtb_ovrdft_acc oac;
v_per_thread_count := round(v_count_all_acc / v_count_of_chunks);
FOR i IN 1..v_count_of_chunks LOOP
BEGIN
v_task := v_task || i;
dbms_output.put_line('task_NAME =' || v_task);
dbms_parallel_execute.create_task(v_task);
dbms_parallel_execute.create_chunks_by_sql(task_name => v_task, sql_stmt => 'SELECT level start_id, level end_id FROM dual connect by level <=1',
by_rowid => false);
v_additional_sql := v_original_sql
|| ' and ACC_ID between '
|| v_start_id
|| ' and '
|| CASE
WHEN i = v_count_of_chunks THEN
v_count_all_acc
ELSE v_per_thread_count * i
END;
v_plsql_block := 'begin fcjlive.xxpks_overdraft_payments.exec_do_payments('
|| ''''
|| v_additional_sql
|| ''''
|| ', :start_id, :end_id); end;';
dbms_output.put_line('full sql = ' || v_plsql_block);
v_start_id := v_start_id + v_per_thread_count;
dbms_parallel_execute.run_task(task_name => v_task, sql_stmt => v_plsql_block, language_flag => dbms_sql.native, parallel_level =>
2);
v_status := dbms_parallel_execute.task_status(v_task);
-- If there is an error, RESUME it for at most 2 times.
WHILE (
l_try < 2
AND v_status != dbms_parallel_execute.finished
) LOOP
l_try := l_try + 1;
dbms_parallel_execute.resume_task(v_task);
v_status := dbms_parallel_execute.task_status(v_task);
END LOOP;
SELECT
decode(v_status, 6, '6-FINISHED', 5, '5-PROCESSING',
7, '7-FINISHED_WITH_ERROR', 8, '8-CRASHED', v_status)
INTO v_status_desc
FROM
dual;
dbms_output.put_line('task_status = ' || v_status_desc);
--DBMS_PARALLEL_EXECUTE.resume_task (task_name => v_task);
IF v_status = 5 THEN
dbms_output.put_line('Wait 17 seconds');
dbms_lock.sleep(17);
BEGIN
dbms_output.put_line('Resume task');
dbms_parallel_execute.resume_task(task_name => v_task);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Resume ended with error: '
|| sqlcode
|| ' '
|| sqlerrm
|| ' '
|| dbms_utility.format_error_backtrace);
END;
SELECT
decode(v_status, 6, '6-FINISHED', 5, '5-PROCESSING',
7, '7-FINISHED_WITH_ERROR', 8, '8-CRASHED', v_status)
INTO v_status_desc
FROM
dual;
dbms_output.put_line('task_status=' || v_status_desc);
ELSIF v_status != dbms_parallel_execute.finished THEN
BEGIN
dbms_parallel_execute.drop_chunks(v_task || i);
dbms_parallel_execute.drop_task(v_task || i);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
dbms_parallel_execute.drop_chunks(v_task);
dbms_parallel_execute.drop_task(v_task);
END;
END LOOP;
FOR i IN 1..v_count_of_chunks LOOP
BEGIN
dbms_parallel_execute.drop_chunks(v_task || i);
dbms_parallel_execute.drop_task(v_task || i);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
dbms_parallel_execute.drop_chunks(v_task);
dbms_parallel_execute.drop_task(v_task);
END;
I am trying develop new multithread process for existed single thread project.
And I try to use "DBMS_PARALLEL_EXECUTE" package.
But problem is that, when I create for example 5 chunks, during execution chunks waits for each other. If one chunk works another one waits. I decided this, after several tests.
I would like you help me to fix my code and execute all chunks at the same time parallely. I do not want they waits each other.
On attachment you can see that, all chunks seperately works but main job's total time (1696.2) = all chunks working time. Due to this reason I think all chunks waits each other. You can see calculation on screen 2
Screen 1
Screen 2
答案1
得分: 1
dbms_parallel_execute 似乎不是合适的工具。
相反，你应该考虑简单地传递两个参数，thread_no 和 thread_count，它们在基本/最外层游标上的 MOD 函数中使用。这样更容易理解和管理。概念（不使用你的对象，只是传达这个概念）：
CREATE OR REPLACE PROCEDURE p_dummy (thread_no, thread_count)
AS
BEGIN
FOR rec_account IN (SELECT *
FROM accounts
WHERE MOD(cust_acc, thread_count) = thread_no - 1)
LOOP
--处理
END LOOP;
END;
然后，从你的客户端异步循环启动这个过程。或者，如果你必须从单个 PL/SQL 入口点开始，可以使用 dbms_scheduler 为每个过程调用创建一次性作业，每次更改 thread_no 参数。添加一个带有 thread_no 列的状态/日志表，过程会在结束时填充该列，并且你的原始循环会休眠和轮询，直到作业完成，并检查表以获取状态。
最后，请确保已经进行了适当的 SQL 优化，以确保你的慢作业不是由于一些需要帮助的 SQL 导致的。很少情况下，PL/SQL 的 CPU 工作占运行时间的大部分。如果最终发现是糟糕的 SQL 导致性能下降，修复它可能会完全消除多线程的需求。
英文:
dbms_parallel_execute doesn't sound like the right tool.
Instead, you should consider simply passing in two parameters, thread_no, and thread_count, which are used in a MOD function on your base/outermost cursor. Much easier to understand and manage. Concept (not using your objects, just conveying the notion):
CREATE OR REPLACE PROCEDURE p_dummy (thread_no, thread_count)
AS
BEGIN
FOR rec_account IN (SELECT *
FROM accounts
WHERE MOD(cust_acc,thread_count) = thread_no - 1)
LOOP
--process
END LOOP;
END;
Then kick off the procedure in an async loop from your client. Or if you have to start with a single PL/SQL point of entry, use dbms_scheduler to create one-off jobs for each of the procedure calls, changing the thread_no parameter with each one. Add a status/logging table with a thread_no column that the procedure populates with its end-state and have your originating loop sleep and poll until the jobs are done and check the table to get status.
Lastly, make sure you've done the appropriate SQL tuning to ensure that your slow job isn't due to some SQL that needs some help. Rarely does PL/SQL CPU work constitute the majority of runtime. IF it turns out you have a bad SQL bogging you down, fixing that could remove the need to multithread this entirely.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论