从PHP运行长时间的Oracle存储过程

Question

我有一个存储过程，我使用以下方式从PHP运行：

//Request does not change
$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';            

//Statement does not change
$stmt = oci_parse($conn,$sql);                     
oci_bind_by_name($stmt,':POP',$pop);           
oci_bind_by_name($stmt,':SEG',$seg);           
oci_bind_by_name($stmt,':DUR',$dur);           
oci_bind_by_name($stmt,':VIEW',$view);           
oci_bind_by_name($stmt,':PAGE',$page);    

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn)

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt,":OUTPUT_CUR", $cursor,-1,OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt);

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS )) {
    print_r($data}
}

问题是我有数百万行数据和复杂的存储过程逻辑。当我通过SQL开发人员执行SP_GET_MY_DATA时，需要大约2小时才能完成。

当我尝试在PHP中执行时，它会超时。我不能增加PHP中的max_execution_time。

请问如何在Oracle上或者使用PHP避免超时呢？请帮忙。

- dang

---

使用DBMS_SCHEDULER Oracle包将其作为程序运行。 - OldProgrammer

@OldProgrammer - 你能给我一个相同的例子吗？ - dang

不要用谷歌搜索“oracle dbms_scheduler”，直接阅读文档。 - OldProgrammer

Answer 1

我在DBA stack exchange上的回答中，详细介绍了如何使用Oracle Scheduler异步运行长时间运行的过程。详情请参见https://dba.stackexchange.com/a/67913/38772

总结一下：

-- submit this as a background job
BEGIN
  dbms_scheduler.create_job ( 
      job_name => 'MY_BACKGROUND_JOB'
    , job_type => 'STORED_PROCEDURE'    
    , job_action => 'SP_GET_MY_DATA'
    , enabled => TRUE
    , auto_drop => TRUE
  );
END;

如果您想将参数传递给过程，那么您需要做更多的工作。您可能会发现这个答案有帮助: https://dba.stackexchange.com/q/42119/38772/ 如果需要更详细的参考信息，请查看Oracle文档中相关章节:https://docs.oracle.com/database/121/ADMIN/scheduse.htm

- Joshua Huber

---

这很好。但是，如果我想让DBMS_Scheduler按顺序运行作业，我该怎么做？我不想因为太多请求而使我的Oracle服务器超载。 - dang

@dang，如果您想按顺序执行多个存储过程，您可以编写一个新的存储过程，该过程仅按顺序调用其他存储过程，基本上是我所谓的包装程序。然后，您可以让计划程序作业调用该新过程。 - Joshua Huber

Joshua - 啊，我的意思是 - 假设有10个不同的存储过程，我将通过PHP调用它们，然后有15个用户访问这些10个调用，因此我将在DBMS_Scheduler中创建150个作业。如果我有150个作业，我的Oracle服务器会变慢。我想知道的是 - 我能否按顺序运行作业？也就是说，Oracle一次只处理一个作业，然后处理下一个作业，而不是同时运行所有150个作业。 - dang

@dang，如果是这样的话，你需要一个队列。Oracle确实有高级队列功能（AQ），但我一直觉得它有点复杂。使用数据库表作为队列是很诱人的，但许多人会警告你不要这样做--请参见http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html和https://softwareengineering.stackexchange.com/questions/231410/why-database-as-queue-so-bad。这将是使用RabbitMQ或ZeroMQ作为队列解决方案的非常好的案例。您甚至可以使用PHP（或任何语言）来实现队列工作者。 - Joshua Huber

Answer 2

不要增加 max_execution_time 的值，将其设置为 0，允许程序无限制地运行。如果你需要返回大量的行，请确保要么增加内存（使用 ini_set 函数），要么允许立即缓冲刷新，以便能够直接输出到客户端。

后一种方法还可以防止客户端因为没有收到数据而提前断开连接。（ob_implicit_flush(true);）

- DevionNL

---

谢谢您的回答，但这不会导致内存问题吗？ - dang

如果你隐式刷新，那么不会。输出会立即发送到客户端而不是保留在输出缓冲区中（即在脚本完全完成之前发送输出），与fetch_assoc一起使用实际上会占用很少的内存，但客户端将接收所有行，因此客户端方面可能会有些填充。 - DevionNL