| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bulk collect got truncated?
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;
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;
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;
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;
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;
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;
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;
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;
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;
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
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
^^^^^^^
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).
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).
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).
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).
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).
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
 |
 |