如何在PL/SQL中同时在不同的会话中执行存储过程 [英] How to execute a stored procedure in a different session in same time in pl/sql

问题描述

无论如何，PL/SQL中是否有可能调用多个会话并同时在多个会话中运行一个过程.

Is there anyway possible in PL/SQL to invoke multiple sessions and run a procedure in the multiple sessions simultaneously.

我想在有250个用户登录该应用程序的实时应用程序中使用它.用户通过客户端工具连接到Oracle. (Power Builder是前端工具)

I want to use this in a real time applications where there are 250 users logging into the application. The users are connected to Oracle through a client tool. (Power Builder is the Front End Tool)

例如，如果用户调用存储过程，则该存储过程必须使用不同的参数值运行10次.
我不想在同一会话中依次连续运行10次，因为这可能需要很长时间.
我正在寻找一种方法，可以在10个不同的会话中同时运行存储过程.

For example, if an user calls an stored procedure, that stored procedure has to be run for 10 times with different parameter values.
I don't want to run this sequentially one after another for 10 times in the same session because it may take long time.
I am looking for a way where I can run the stored procedure in 10 different sessions simultaneously.

我考虑过使用DBMS_JOB.SUBMIT放置10个作业，但是由于 繁重的工作负荷(可能在Job Scheduler中同时安排250个用户* 10 = 2500个工作，依此类推)，我们的DBA小组正在寻找其他更好的方法.

I thought about placing 10 jobs using DBMS_JOB.SUBMIT but because of the heavy job load ( 250 users * 10 = 2500 jobs may be scheduled in the Job scheduler at the same time and so on) our DBA group is looking for some other better way.

推荐答案

或者创建JOB，也可以使用 DBMS_PARALLEL_EXECUTE 软件包.

Alternatively to creating JOBs you may use the DBMS_PARALLEL_EXECUTE package.

这里有一些热门歌曲:

将create_chunks_by_sql与by_rowid => FALSE一起使用，即使用ID，并创建与所需的存储过程执行完全相同的块数.

Use create_chunks_by_sql with by_rowid => FALSE, i.e. using ID and create exact the same number of chunks as the required execution of the stored procedure.

在run_task中，将parallel_level设置为所需的并行度.如果您需要限制并行度，则此数字与上面的数字相同或更低.

In run_task set the parallel_level to the required degree of parallelism. This is the same number as above or lower if you need o throttle the parallelism.

通过 参数sql_stmt例如

BEGIN
  test_proc(:start_id,:end_id);
END;

可选地，如您所见，可以将块号传递给该过程，因此可以将其用作threadId.

Optionally as you see it is possible to pass the chunk number to the procedure, so you may use it as a threadId.

这是一个完整的例子

创建任务和3个区块

DECLARE
  l_stmt CLOB;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => 'parallel PL/SQL');

  l_stmt := 'SELECT rownum, rownum FROM dual connect by level <= 3';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name => 'parallel PL/SQL',
                                             sql_stmt  => l_stmt,
                                             by_rowid  => FALSE);
END;
/

以DOP = 3运行任务

DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN
  l_sql_stmt := 'BEGIN
                   test_proc(:start_id,:end_id);
                END;';   

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => 'parallel PL/SQL',
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 3);
END;
/

删除任务

BEGIN
  DBMS_PARALLEL_EXECUTE.drop_task('parallel PL/SQL');
END;
/

这篇关于如何在PL/SQL中同时在不同的会话中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！