10g和11g里面 批量操作BULK COLLECT的优势

晶晶小妹

FOR X IN(SELECT * FROM T) 在10G下被悄悄的换成了批量操作。这和我说的不冲突啊。
我说的是批量操作和单行操作的不同，单行操作会有更多的逻辑读，这将占用更多的锁和CPU资源，因此，10G中把能换成批量操作的地方换为批量操作这也是很正常的啊。

Yong Huang

原帖由 onunix 于 2008-12-10 03:26 发表
位置在中文版本的第11章 索引,433页

Official documentation? If yes, what book of documentation? If not, what's the book title and author? Thanks.

Yong Huang

onunix

原帖由 晶晶小妹 于 2008-12-10 19:49 发表
FOR X IN(SELECT * FROM T) 在10G下被悄悄的换成了批量操作。这和我说的不冲突啊。
我说的是批量操作和单行操作的不同，单行操作会有更多的逻辑读，这将占用更多的锁和CPU资源，因此，10G中把能换成批量操作的地方换为批量操作这也是很正常的啊。

呵呵,误会你的意思了

onunix

原帖由 Yong Huang 于 2008-12-10 22:52 发表


Official documentation? If yes, what book of documentation? If not, what's the book title and author? Thanks.

Yong Huang

tom  <<9i&10g编程艺术>>  中文翻译的版本, 英文版本就不知道多少页,但猜测还是 第11章 "索引" 那一章.

onunix

原帖由 Yong Huang 于 2008-12-10 10:58 发表


You need to have a habit of telling us where you find the example if it was not written by you. It's a way to honor their copyright and also exempt you from responsibility for errors.

When you compare performance in this case, elapsed time is not as important as, for instance, buffer_gets per execution. Find these values in v$sql or, if 10g, preferably v$sqlstats. Whether the SQL actually does a bulk collect behind the scenes can be detected by rows_processed / executions, as well as buffer_gets / executions.

Yong Huang

Yong Huang,你说的根据rows_processed / executions,看不出的,我试了一下,还是跑前面我贴出来的脚本.结果如下:

SQL> select sql_text, rows_processed,executions from v$sqlstats  where sql_text like '%where rownum < 100000%';

SQL_TEXT                                                                                               ROWS_PROCESSED    EXECUTIONS
-------------------------------------------------------------------------------- -------------- ----------
declare         type tnt_ic01 is table of PDATA_CELL_1%rowtype;        nt_ic01 t              1          1

declare       type tnt_ic01 is table of PDATA_CELL_1%rowtype;        nt_ic01 tnt              1          1


SQL>


2个过程(一个用bulk ,一个不用),看不出区别.

onunix

这个rows_processed和查询出多少行数据库记录没有关系,也许是别的意思.

Yong Huang

原帖由 onunix 于 2008-12-12 00:46 发表


Yong Huang,你说的根据rows_processed / executions,看不出的,我试了一下,还是跑前面我贴出来的脚本.结果如下:

SQL> select sql_text, rows_processed,executions from v$sqlstats  where sql_text like '%where rownum < 100000%';

SQL_TEXT                                                                                               ROWS_PROCESSED    EXECUTIONS
-------------------------------------------------------------------------------- -------------- ----------
declare         type tnt_ic01 is table of PDATA_CELL_1%rowtype;        nt_ic01 t              1          1

declare       type tnt_ic01 is table of PDATA_CELL_1%rowtype;        nt_ic01 tnt              1          1


SQL>

2个过程(一个用bulk ,一个不用),看不出区别.

That's the PL/SQL text. Can you find the actual SQL inside the PL/SQL block? For instance,

SQL> begin
  2    delete from t where rownum = 1;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where parsing_schema_name = 'YONG';

SQL_TEXT
-------------------------------------------------------------------
...
DELETE FROM T WHERE ROWNUM = 1       <-- this is the one
...
begin   delete from t where rownum = 1; end;       <-- not this one

Note the actual SQL will have slightly different text, changing letter case to uppercase, sometimes adding extra spaces, removing comments, etc.

Yong Huang

onunix

原帖由 Yong Huang 于 2008-12-13 03:14 发表


That's the PL/SQL text. Can you find the actual SQL inside the PL/SQL block? For instance,

SQL> begin
  2    delete from t where rownum = 1;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where parsing_schema_name = 'YONG';

SQL_TEXT
-------------------------------------------------------------------
...
DELETE FROM T WHERE ROWNUM = 1        

重新做了一次，并增加了另外一种方法：

declare

       type tnt_ic01 is table of ic01%rowtype;

      nt_ic01 tnt_ic01 := tnt_ic01();

      n_startTime number;
      
      dd number;
      
      cursor myCur is select * from ic01 where rownum < 99998;

    begin

      n_startTime := dbms_utility.get_time;
      
      for tempCur in myCur loop
                   
                dd := tempCur.a;
                   
       
     end loop;

     
     dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

     dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );

   end;

3种方法得到的:rows_processed都为8000（表中共8000行记录），得到的executions都为1,可见，这个执行次数和一次取多少行没有关系。从rows_processed和executions不能看出一次取多少行。

[ 本帖最后由 onunix 于 2008-12-15 12:02 编辑 ]

Yong Huang

原帖由 onunix 于 2008-12-14 22:00 发表

重新做了一次，并增加了另外一种方法：

declare
       type tnt_ic01 is table of ic01%rowtype;
      nt_ic01 tnt_ic01 := tnt_ic01();
      n_startTime number;
      dd number;
      cursor myCur is select * from ic01 where rownum < 99998;
    begin
      n_startTime := dbms_utility.get_time;
      for tempCur in myCur loop
                dd := tempCur.a;
     end loop;
     dbms_output.put_line( 'COUNT: ' || nt_ic01.count );
     dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );
   end;

3种方法得到的:rows_processed都为8000（表中共8000行记录），得到的executions都为1,可见，这个执行次数和一次取多少行没有关系。从rows_processed和executions不能看出一次取多少行。

Rows_processed versus executions is used to check bulk insert. See
http://groups.google.com/group/c ... ad/1f8f5882ec68096b
I just realize you're checking the efficiency of fetching or collecting rows from the already-obtained result set. So checking consistent gets is the way to go, not rows_processed / executions. But anyway, if you test, compare results between 10g and 9i.

Back to the original message "10g中，PL/SQL中简单的FOR X IN(SELECT * FROM T)会悄悄地一次批量获取100行，而在9i中，只会一次获取一行". I think I found this undocumented performance improvement:

http://asktom.oracle.com/pls/ask ... 3067171813508366601
Well, it would seem hardly worth it to do the bulk collect here wouldn’t it?  The reason – in Oracle 10g, PLSQL is silently array fetching 100 rows at a time for us, when we do “for x in ( select * from t “ – PLSQL has already bulk collected 100 rows.  We no longer needed to do that extra code, the extra work.

Bug 4595711 "IMPL.CUR IN FOR LOOP FETCHING PACKG.VAR RETURNS WRONG VALUES IN 10G"
So it is performing an array fetch of 100 rows at a time in order to improve
performance. We haven't implemented the same with explicit cursors because
the user has full control and can implement the equivalent using a bulk
fetch.
...
Note there is no explicit mention of this change in the doc as it says only
that the new plsql optimiser improves performance in a large number of areas.

Bug 5996685 "FOR LOOP SLOWER IN 10G THAN 8I"
Note in 10g+ the cursor for loop fetch in test 2 will be much quicker than
earlier releases because it performs array fetches of 100 rows each time.

Yong Huang

[ 本帖最后由 Yong Huang 于 2008-12-15 14:40 编辑 ]

onunix

原帖由 Yong Huang 于 2008-12-16 03:17 发表


. But anyway, if you test, compare results between 10g and 9i.


.....
Yong Huang

sorry, 我这里没有oracle 9i的版本,因为工作的原因,只有10g,没有其他的版本.