Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bulk collect got truncated?

RE: Bulk collect got truncated?

From: Mark Moynahan <Mark.Moynahan_at_apollogrp.edu>
Date: Wed, 28 May 2003 11:15:10 -0800
Message-ID: <F001.005A4AEA.20030528111510@fatcity.com>


Is it possible that you are running out memory on the OS?

A different question I have is why bulk collect such a large amount at once. Why not do a cursor with a limit on the fetch? This would allow you to process in smaller batches instead of one gigantic fetch and insert.

Mark

-----Original Message-----
Sent: Wednesday, May 28, 2003 11:07 AM
To: Multiple recipients of list ORACLE-L

Hi dear listers,

Some of you may still remember this thread, bulk collect truncated to 65535 records sometimes.
I've got this case reproduceable and tried all suggestions ,

In a brief,
 SELECT returns 318847 rows,
 INSERT INTO FROM SELECT - 318847 rows,  PL/SQL plain FOR cr IN (select ..) LOOP - - 318847 rows  PL/SQL with BULK COLLECT many different code versions - sometimes returs 65535 records instead, the rest is truncated

What might be interesting, in case when it fails, it doesn't retrieve requiered rows from disk. I can judge it by much shorter responce time and 10046 trace doesn't show db file sequential read events what always showup when number of rows is correct.

10046 trace provides interesting details, 65535 records is approximately the point (=/- 50 records) where it usually does first db file sequential read in case of successful execution.

So Waleed, apparently there is a problem here, 9.0.1.4 Solaris. Time to TAR now.

Below is the spool from my recent session.

Have a good day,
Vadim

set serveroutput on
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index by binary_integer;

  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
318847

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.03
SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index by binary_integer;

  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set events = '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set sql_trace= true;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index by binary_integer;

  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set sql_trace= false;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index by binary_integer;

  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT /*+ index(p sub_svc_parm_ix2) */ count(*) FROM CBQA4SP.sub_svc_parm p
  2 WHERE parm_id =10;

  COUNT(*)


    318847

Elapsed: 00:00:00.03
SQL>
SQL> DECLARE

  2     TYPE t_sub_svc_id IS TABLE OF number index by binary_integer;
  3     TYPE t_subsvcext_key IS TABLE OF varchar2(255) index by
binary_integer;
  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL>
SQL> DECLARE

  2     TYPE t_sub_svc_id IS TABLE OF number;
  3     TYPE t_subsvcext_key IS TABLE OF varchar2(255) ;
  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.01
SQL> /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> disconnect;
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production With the Partitioning option
JServer Release 9.0.1.4.0 - Production
SQL> connect CBQA4SP/CBQA4_at_db901v.lakota Connected.
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> autoprint OFF
serveroutput OFF
serveroutput OFF
flagger OFF
/
318847

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.00
SQL> alter session set sql_trace= true;

Session altered.

Elapsed: 00:00:00.00
SQL> / Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE

  2     TYPE t_sub_svc_id IS TABLE OF number;
  3     TYPE t_subsvcext_key IS TABLE OF varchar2(255) ;
  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set sql_trace=false;

Session altered.

Elapsed: 00:00:00.00
SQL> / Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE

  2     TYPE t_sub_svc_id IS TABLE OF number;
  3     TYPE t_subsvcext_key IS TABLE OF varchar2(255) ;
  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     dbms_output.put_line(esubsvcid.count);
 13 end;
 14 /
65535

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> blockterminator "." (hex 2e)
serveroutput ON size 2000 format WORD_WRAPPED flagger OFF
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

SQL> var n number;
SQL> 
SQL> DECLARE
  2     TYPE t_sub_svc_id IS TABLE OF number;
  3     TYPE t_subsvcext_key IS TABLE OF varchar2(255) ;
  4     esubsvcid       t_sub_svc_id;
  5     eval            t_subsvcext_key;
  6  BEGIN
  7     SELECT /*+ index(p sub_svc_parm_ix2) */
  8            sub_svc_id, val
  9       BULK COLLECT INTO esubsvcid, eval
 10       FROM CBQA4SP.sub_svc_parm p
 11      WHERE parm_id =10;
 12     :n := esubsvcid.count;

 13 end;
 14 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> print n

         N


     65535

SQL> connect CBQA4SP/CBQA4_at_db901v.lakota Connected.
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> print n

         N


     65535

-----Original Message-----
Sent: Tuesday, May 20, 2003 6:07 PM
To: Multiple recipients of list ORACLE-L

This is a good sign. I would be very concerned if there is a problem since I have many important apps that use bulk load.

Keep us updated.

Regards,

Waleed

-----Original Message-----
Sent: Tuesday, May 20, 2003 4:02 PM
To: Multiple recipients of list ORACLE-L

No, Waleed, I didn't.

Trying to reproduce the problem now, but can't, neither count nor last. Weird.

Thanks,
Vadim

-----Original Message-----
Sent: Tuesday, May 20, 2003 2:13 PM
To: Multiple recipients of list ORACLE-L

When this happens, did you try displaying esubsvcparmid.count?

Waleed

-----Original Message-----
Sent: Tuesday, May 20, 2003 11:17 AM
To: Multiple recipients of list ORACLE-L

Hi all,

Is there any "best practice" regarding practical limits on PL/SQL tables? We've got an issue here which may potentially invalidate data, no error messages.

Here is the case:

Oracle9i Enterprise Edition Release 9.0.1.4.0, Solaris.

DECLARE
   TYPE t_subsvcparmid IS TABLE OF sub_svc_parm.sub_svc_parm_id%TYPE;    esubsvcparmid t_subsvcparmid;
BEGIN
   SELECT /*+ index(sub_svc_parm, sub_svc_parm_ix2) */

          sub_svc_parm_id
     BULK COLLECT INTO esubsvcparmid
     FROM sub_svc_parm

    WHERE parm_id = 10;

        DBMS_OUTPUT.PUT_LINE(esubsvcparmid.LAST);

..................................

The select is expected to return close to 200K records, and usually it does, but sometimes number of records is restricted to 65535 = 2^16-1, it can be seen both from DBMS_OUTPUT and 10046 trace:

FETCH
#2:c=4370000,e=33876545,p=14895,cr=66960,cu=0,mis=0,r=65535,dep=1,og=4,tim=1 052942377932150

	
^^^^^^^

Did anybody see such weird behavior?

TIA Vadim Gorbounov

Liberate Tech.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Gorbounov,Vadim
  INET: vadim.gorbounov_at_liberate.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Gorbounov,Vadim
  INET: vadim.gorbounov_at_liberate.com
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Gorbounov,Vadim
  INET: vadim.gorbounov_at_liberate.com
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mark Moynahan
  INET: Mark.Moynahan_at_apollogrp.edu
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed May 28 2003 - 14:15:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US