从PHP运行长Oracle存储过程

I have a stored procedure which I run from PHP using:

我有一个从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}
}

The problem is I have millions of rows and complex logic in stored procedure. When I execute SP_GET_MY_DATA through SQL developer, it takes around 2 hours to complete it.

问题是我在存储过程中有数百万行和复杂的逻辑。当我通过SQL developer执行SP_GET_MY_DATA时，大约需要2个小时完成。

PHP is timing out when I do it. I cannot increase the max_execution_time in PHP as well.

我这么做的时候PHP就超时了。我也不能在PHP中增加max_execution_time。

How can I run this on Oracle or using PHP without timing out? Please help.

如何在Oracle或PHP上运行它而不超时?请帮助。

2 个解决方案

#1

I answered how to use Oracle Scheduler to run a long running procedure asynchronously pretty comprehensively in this answer on the DBA stack exchange. See https://dba.stackexchange.com/a/67913/38772

我回答了如何在DBA堆栈交换中使用Oracle调度器异步运行长时间的过程。参见https://dba.stackexchange.com/a/67913/38772

TL;DR is

TL,博士

-- 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;

You'll have to do a little more work if you want to pass in parameters to the procedure. You may find this answer helpful https://dba.stackexchange.com/q/42119/38772/

如果要将参数传递给过程，就需要做更多的工作。您可能会发现这个答案非常有用:https://dba.stackexchange.com/q/42119/38772/

For additional reference with all the gory details, the relevant chapter from Oracle's documentation is at https://docs.oracle.com/database/121/ADMIN/scheduse.htm

对于所有gory细节的附加参考，Oracle文档中的相关章节位于https://docs.oracle.com/database/121/admin/schede.htm

#2

-2

Don't increase max_execution_time, set it to 0, allow it to run indefinitely. If you are going to return loads of rows, make sure to either increase memory (ini_set) or allow immediate buffer flushing so that it can output directly to client.

不要增加max_execution_time，将它设置为0，允许它无限运行。如果要返回行负载，请确保增加内存(ini_set)或允许立即刷新缓冲区，以便它可以直接输出到客户端。

The latter will also prevent clients from prematurely disconnecting because they didn't get any data. (ob_implicit_flush(true);)

后者还将防止客户端由于没有获得任何数据而过早断开连接。(ob_implicit_flush(真正的);

#1