oci_set_prefetch

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_set_prefetch — 设置预提取行数

说明

oci_set_prefetch(resource $statement, int $rows = ?): bool

在成功调用 oci_execute() 之后设定预提取的行数。rows 的默认值为 1。

注意:
在 PHP 5.0.0 之前的版本必须使用 ocisetprefetch() 替代本函数。该函数名仍然可用，为向下兼容作为 oci_set_prefetch() 的别名。不过其已被废弃，不推荐使用。

成功时返回 true，或者在失败时返回 false。

参见 oci8_.default_prefetch INI 选项。

参数

statement: 有效的 OCI8 报表标识符由 oci_parse() 创建，被 oci_execute() 或 REF CURSOR statement 标识执行。
rows: The number of rows to be prefetched, >= 0

返回值

成功时返回 true，或者在失败时返回 false。

更新日志

版本	说明
PHP 5.3.2 (PECL OCI8 1.4)	Before this release, `rows` must be >= 1.
PHP 5.3 (PECL OCI8 1.3.4)	Before this release, prefetching was limited to the lesser of `rows` rows and 1024 * `rows` bytes. The byte size restriction has now been removed.

范例

示例 #1 Changing the default prefetch value for a query


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');

$stid = oci_parse($conn, 'SELECT * FROM myverybigtable');
oci_set_prefetch($stid, 300);  // Set before calling oci_execute()
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

oci_free_statement($stid);
oci_close($conn);

?>

示例 #2 Changing the default prefetch for a REF CURSOR fetch


<?php
/*
  Create the PL/SQL stored procedure as:

  CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
  END;
*/

$conn = oci_connect('hr', 'welcome', 'localhost/XE');

$stid = oci_parse($conn, 'BEGIN myproc(:rc); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);

// Change the prefetch before executing the cursor.
// REF CURSOR prefetching works when PHP is linked with Oracle 11gR2 Client libraries
oci_set_prefetch($refcur, 200);
oci_execute($refcur);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);

?>

If PHP OCI8 fetches from a REF CURSOR and then passes the REF CURSOR back to a second PL/SQL procedure for further processing, then set the REF CURSOR prefetch count to 0 to avoid rows being "lost" from the result set. The prefetch value is the number of extra rows fetched in each OCI8 internal request to the database, so setting it to 0 means only fetch one row at a time.

示例 #3 Setting the prefetch value when passing a REF CURSOR back to Oracle


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/orcl');

// get the REF CURSOR
$stid = oci_parse($conn, 'BEGIN myproc(:rc_out); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);

// Display two rows, but don't prefetch any extra rows otherwise
// those extra rows would not be passed back to myproc_use_rc().
// A prefetch value of 0 is allowed in PHP 5.3.2 and PECL OCI8 1.4
oci_set_prefetch($refcur, 0);
oci_execute($refcur);
$row = oci_fetch_array($refcur);
var_dump($row);
$row = oci_fetch_array($refcur);
var_dump($row);

// pass the REF CURSOR to myproc_use_rc() to do more data processing
// with the result set
$stid = oci_parse($conn, 'begin myproc_use_rc(:rc_in); end;'); 
oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);

?>

注释

注意:
In PHP versions before 5.0.0 use ocisetprefetch() instead.

参见

oci8.default_prefetch ini option

add a note

User Contributed Notes 1 note

down

bmichael at goldparrot dot com ¶

19 years ago


If your are using Oracle's OCI libraries, on any project, which PHP does, you can use this limit.


I have done network level testing on the effect of this parameter.  It does improved efficiency.  Big Time.


Oracle uses SQL*Net as the transport mechanism for data between your connection and the database.  That is why you must setup Oracle properly.


This parameter tells SQL*NET to Buffer more results.  When SQL*NET (at the server) gets a request for data, it bundles up X rows (1,2,3,1000, etc) for transport.  It sends the appropriate SQL*NET headers back to the client, Waits for an ACK then begins sending data in MTU sized chunks (ethernet is something like 1500 bytes and ATM's WANS are around 1000 bytes).  The chunk size can also be tuned in SQL*NET, but with much less improvements.


TCP/IP then takes the data across the wire, breaking it up into multiple TCP/IP packets.


Once the exchange is done, the SQL*NET client sends an

ACK back to the SQL*NET Listener (the Oracle Server) and the transaction is complete.


Each round trip, SQL*NET looks up inside the server memory (UGA - user global area) to find the query results. It then grabs the rows necessary to send.  If it is one row, versus 1000 rows.  The process is the same. 


There is much I could tell you on how the Database itself reacts.  If you can significantly lessen the amount of round trips you are making... WOW.  


For more info on Oracle OCI go to http://otn.oracle.com

add a note

官方地址：https://www.php.net/manual/en/function.oci-set-prefetch.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 科技开发编程 Elasticsearch HTML/CSS/XML 网络 JAVA NoSQL C/C++ 面试 Golang 数据库 Git 算法正则表达式操作系统 Redis 互联网 MySql JavaScript 运维软件架构设计 Mac OS TCP/IP 国际 Excel Vim Windows Socket Oracle VR MongoDB 运营 Python MemCache 商业硬件电子设计 nginx 娱乐摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 [PHP] inet_pton/inet_ntop IP地址转换函数 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 PHPMailer设置utf8 PHPMailer字符集CharSet 如何在Composer中为GitLab实例设置访问令牌 array_multisort() 多字段排序 laravel框架查看sql语句方式 [原创]诡异的php-fpm超时问题 PHP获取指定函数定义在哪个文件中及行号 PHP5 扩展SOAP 调用 webservice php设置cookie为httponly防止xss攻击利用php soap实现web service PHP-FPM进程数的设定利用PHP SOAP实现WEB SERVICE PHPstorm 里面Terminal 不能使用 esc键吗退出编辑模式吗纯php实现DES以及TripleDES加密算法 Composer的Packagist资源 PHP7添加redis扩展 PHP注释规范

略微加速

PHP官方手册 - 互联网笔记

oci_set_prefetch

说明

参数

返回值

更新日志

范例

注释

参见

User Contributed Notes 1 note