because procedural code is slower than a single sql statement in general.

insert into t2 select * from t1;

it is all done efficiently, in the server, in bulk - we told the server everything we wanted to do and it could optimize the heck out of it.

for x in (select * from t1)
loop
insert into t2 values ( .... );
end loop


now we have to slow by slow send rows out of the database, only to turn around and send them back in.


more code = more bugs
more code = slower
more code = undesirable

I always advise 1 and 2, in all circumstances, not sure what you mean.

append - be careful with that, it is not necessarily faster than noappend and only applies in somewhat rare circumstances.

parallel - see append - but even less often.

the SGA takes care of itself, do not worry about protecting it. In fact, if anything - slow by slow processing will have a more negative effect on sga utilization than anything done in bulk (you tend to not have any nice efficient full scans - or less of them - and we end up trying to cache all of it for your procedural code, even though no one will reuse it - whereas with big bulk operations - we know to age it right out).

When I said "when you cannot do it in SQL, use as little plsql as possible", I was being quite precise and very literal.

when you cannot do it in sql. period, just when you CANNOT CODE the sql to do it.

... you do not forsee any issues of caching millions of rows in the sga
using a single select ? ...

sigh, the SGA is pretty well handled by the software.

you could trash the contents of a buffer cache much easier with slow by slow code than you would with BULK CODE.

If you end up reading every row in a table via an index - we'll end up trying to cache the entire table and index in the SGA - even though YOU are going to read it ONCE for your batch process.

if you end up full scanning segments - we smartly get them in and out of the buffer cache really fast - without wiping out lots of useful data

so your hypothesis actually works against you in this case - your presumption is not accurate.

it should take almost no undo????

you would be using a direct path insert, I would presume the indexes would be disabled or non-existent.

ops$tkyte%ORA10GR2> create table t
  2  ( data varchar2(1000) )
  3  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert  /*+ append */ into t
  2  select rpad( '*', 1000, '*' )
  3  from dual
  4  connect by level <= 100000
  5  /

100000 rows created.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> insert into t select rpad( '*', 1000, '*' )
  2  from dual connect by level <= 100000;

100000 rows created.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       237

so, use a single statement.

... We have not special situation but have hired oracle consultant and what he suggest is to use bulk insert instead of single sql. ...

why? do they want it to take longer?


... so he suggested us to use bulk insert which i think will be slower. ...

you sir, are correct. But you know what, you should ask this expert to SHOW YOU the numbers, not the other way around. They should PROVE TO YOU that what they say is correct.



In your test, I would surmise that run two ran faster only because run1 did the work of getting all of the blocks into the buffer cache - as evidenced by the reduced IO's - run the test the other way and see what you see then :)


writing code to replace that single insert would be *not smart*, as in *not intelligent*, as in *the wrong way*


I hope you do know that:

...
INSERT /*+ append */ INTO t2 VALUES l_data(i);
.....


is identical to

INSERT INTO t2 VALUES l_data(i);

(except in 11gR1 where append actually does 'work' by accident, fixed in 11gr2). Direct path loads for single row inserts would be deadly - they do not happen fortunately.

also, the indentation of your 'commit' makes me think you think it is "in" the forall loop - it is not. It is after - the forall is not really a loop - it is a single statement.


Let's do your test - removing the complex query and just loading data (so we are comparing "loading to loading" and not the performance of the query loading from from execution to execution.

ops$tkyte%ORA10GR2> drop table t1;
Table dropped.

ops$tkyte%ORA10GR2> drop table t2;
Table dropped.

ops$tkyte%ORA10GR2> create table t1 as select owner, object_name, object_id from all_objects where 1=0;
Table created.

ops$tkyte%ORA10GR2> create table t2 as select owner, object_name, object_id from all_objects where 1=0;
Table created.


ops$tkyte%ORA10GR2> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  for i in 1 .. 10
  3  loop
  4      insert /*+ append */ into t1 select owner,object_name, object_id from all_objects;
  5      commit;
  6  end loop;
  7  end;
  8  /
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2  TYPE ARRAY IS TABLE OF t2%ROWTYPE;
  3  l_data ARRAY;
  4  CURSOR c IS select owner,object_name, object_id from all_objects;
  5  BEGIN
  6  for i in 1 .. 10
  7  loop
  8      OPEN c;
  9      LOOP
 10          FETCH c BULK COLLECT INTO l_data LIMIT 10000;
 11          FORALL i IN 1..l_data.COUNT
 12              INSERT INTO t2 VALUES l_data(i);
 13          commit;
 14          EXIT WHEN c%NOTFOUND;
 15      END LOOP;
 16      CLOSE c;
 17  end loop;
 18  end;
 19  /
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runstats_pkg.rs_stop();
Run1 ran in 1835 cpu hsecs
Run2 ran in 3624 cpu hsecs
run 1 ran in 50.63% of the time

Name                                  Run1        Run2        Diff
...
LATCH.session allocation           104,502     105,622       1,120
LATCH.enqueues                         532       1,758       1,226<b>
STAT...DB time                       1,909       3,634       1,725
STAT...Elapsed Time                  1,906       3,635       1,729
STAT...recursive cpu usage           1,838       3,618       1,780
STAT...CPU used when call star       1,841       3,627       1,786
STAT...CPU used by this sessio       1,838       3,627       1,789</b>
STAT...active txn count during           4       2,109       2,105
STAT...cleanout - number of kt           4       2,110       2,106
STAT...consistent gets - exami     202,590     204,708       2,118
LATCH.undo global data                 306       2,813       2,507
STAT...calls to kcmgcs                  41       2,770       2,729
STAT...db block gets direct          2,740           0      -2,740
STAT...physical writes direct        2,740           0      -2,740
STAT...physical writes non che       2,740           0      -2,740
STAT...physical writes               2,740           0      -2,740
STAT...commit cleanouts succes          96       2,878       2,782
STAT...commit cleanouts                100       2,883       2,783
STAT...free buffer requested           147       3,067       2,920
STAT...redo subscn max counts           59       2,985       2,926
STAT...calls to get snapshot s     115,094     118,047       2,953
LATCH.cache buffer handles           2,860       6,568       3,708
STAT...table scan rows gotten    1,101,167   1,104,931       3,764
LATCH.object queue header oper         155       6,284       6,129
LATCH.SQL memory manager latch          20      11,379      11,359
STAT...redo entries                  1,191      16,061      14,870
STAT...db block changes              1,579      21,969      20,390
STAT...db block gets                 4,520      26,343      21,823
STAT...db block gets from cach       1,780      26,343      24,563
STAT...physical read bytes          40,960      16,384     -24,576
STAT...IMU undo allocation siz       2,544      44,452      41,908
LATCH.row cache objects            241,283     308,810      67,527
STAT...IMU Redo allocation siz         320     523,212     522,892
LATCH.simulator hash latch          85,225     702,283     617,058
LATCH.SQL memory manager worka       1,804     786,059     784,255
STAT...physical read total byt     860,160      16,384    -843,776
STAT...undo change vector size      44,512   1,381,504   1,336,992
STAT...buffer is pinned count      729,915   4,218,843   3,488,928
STAT...index scans kdiixs1         651,953   4,140,973   3,489,020
STAT...no work - consistent re   1,242,934   4,779,892   3,536,958
STAT...consistent gets from ca   1,447,391   4,989,399   3,542,008
STAT...consistent gets           1,447,391   4,989,399   3,542,008
STAT...session logical reads     1,451,911   5,015,742   3,563,831
LATCH.cache buffers chains       2,698,761   9,865,284   7,166,523
STAT...physical write bytes     22,446,080           0 -22,446,080
STAT...physical write total by  22,446,080           0 -22,446,080<b>
STAT...redo size                   176,692  23,781,996  23,605,304</b>

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct<b>
3,570,177  12,234,835   8,664,658     29.18%
</b>
PL/SQL procedure successfully completed.

I use cpu hsecs now (replace dbms_utility.get_time with get_CPU_time in runstats)


but as you can see - it takes longer and uses more resources.

YOU CANNOT GET MORE "BULK LOAD" THAN A SINGLE SQL STATEMENT - INSERT INTO SELECT IS THE ULTIMATE BULK LOAD - YOU CANNOT WRITE BULKIER CODE YOURSELF, NO WAY NO HOW. 

so, if the consultant believes bulk loading is good (it is), then they went the WRONG DIRECTION didn't they?

1) explain what a little more?

insert /*+ APPEND */ works only with SELECT, not with VALUES. (except as mentioned in 11g Release 1 where there is a bug, in 11g Release 2 you can do it with values with a new hint, append_values - but append will NOT direct path with values)

2) forall is not a loop, that is all, it is not a loop.

it seemed that you thought it was and that the commit was in the loop. There is no loop. Your code:

    FETCH c BULK COLLECT INTO l_data LIMIT 10000;
    FORALL i IN 1..l_data.COUNT
        INSERT /*+ append */ INTO t2 VALUES l_data(i);
        commit;
    EXIT WHEN c%NOTFOUND;
    END LOOP;

Look at that lining up of the code - the commit is lined up with the insert, it just makes one THINK that you THINK the commit and insert are in a "loop", that is all I'm saying.



All I'll say is:


YOU CANNOT GET MORE "BULK LOAD" THAN A SINGLE SQL STATEMENT - INSERT INTO SELECT IS THE ULTIMATE
BULK LOAD - YOU CANNOT WRITE BULKIER CODE YOURSELF, NO WAY NO HOW.

doing this procedurally is "dumb"

I directly answered that:

...
the mantra:

o You should do it in a single SQL statement if at all possible.
o If you cannot do it in a single SQL Statement, then do it in PL/SQL.
o If you cannot do it in PL/SQL, try a Java Stored Procedure.
o If you cannot do it in Java, do it in a C external procedure.
o If you cannot do it in a C external routine, you might want to seriously
think about why it is you need to do it?

.....

for bulk processing you should always

a) make the array size a parameter, so you can adjust it in your code
b) start with 100 and go upto about 1000 but stick in that range.

You don't want to do all of step A, filling up the array (in memory, lots of resources) and then doing step B

You do want to do a little of A, a little of B, a little of A, a little of B and so on - spread the work out, no big bumps in the processing timeline.

sqlplus arraysize setting has *nothing* to do with plsql, it will not affect anything in plsql - forall processing is procedural, you control the arraysize yourself.

option 3 does NOT use a bulk insert.

It uses, correctly, a single SQL statement to do all of the work.


If you need to capture errors and deal with them later - you are looking for dml error logging.

http://asktom.oracle.com/Misc/how-cool-is-this.html
http://asktom.oracle.com/Misc/how-cool-is-this-part-ii.html

I know this can be easily done using merge. But considering number of records is merge really useful here?


ummm, you seem to be missing the basic point of SQL - processing sets of data is what it does best.

SQL performance beats slow by slow performance more and more as the volume of data increases.


You want to full scan stage, full scan target, hash outer join them in a merge.

YOU DO NOT want to do 30,000,000 index lookups which is probably what would happen with your slow by slow merge.

and even if it didn't use an index - how would you full scanning stage into memory and then using that collection in a merge be *better* than just doing it straight away???

you'll need to describe the machine. My laptop can do that in a few seconds.

ops$tkyte%ORA11GR2> create table t as select * from big_table.big_table where 1=0;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> insert into t
  2  select * from big_table.big_table union all
  3  select * from big_table.big_table union all
  4  select * from big_table.big_table ;

3000000 rows created.

Elapsed: 00:00:33.30

does your machine have the ability to do IO. describe machine.

how big it 3,000,000 rows in bytes (3,000,000 rows is rather meaningless as a metric when you think about it).

have you considered using nologging on the target table and a direct path load? alter table target_tbl nologging; insert /*+ append */ into target_tbl ...; alter table target_tbl logging; BACKUP now.

have you dug into an ash report to see what it was doing?

create table as select is a direct path load, yes.

....Is there any difference between using for vs forall during bulk collect?...

well, first - you do not use forall during a bulk collect. bulk collect is just a bulk collect - it doesn't use forall

forall is a way to bulk up other DML statements like insert, update, delete and merge.

For would do these statements one by one
Forall does them in bulk.


If you were to trace your stuff above, tkprof would show you doing an execute for every single insert in the for loop, it would show you doing an execute for every 2000 inserts in the second loop

ops$tkyte%ORA11GR2> create table t1 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from t1 where 1=0;

Table created.

ops$tkyte%ORA11GR2> create table t3 as select * from t1 where 1=0;

Table created.

ops$tkyte%ORA11GR2> create table t4 as select * from t1 where 1=0;

Table created.

ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> declare
  2          type array is table of t1%rowtype index by binary_integer;
  3          cursor test_curs is select * from t1;
  4          l_data array;
  5  begin
  6          open test_curs;
  7          loop
  8                  fetch test_curs bulk collect into l_data limit 2000;
  9                  for i in 1..l_data.COUNT
 10                  LOOP
 11                          insert into t2 values l_data(i);
 12                  END loop;
 13                  exit when test_curs%notfound;
 14          END LOOP;
 15          close test_curs;
 16  end;
 17  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          type array is table of t1%rowtype index by binary_integer;
  3          cursor test_curs is select * from t1;
  4          l_data array;
  5  begin
  6          open test_curs;
  7          loop
  8                  fetch test_curs bulk collect into l_data limit 2000;
  9                  forall i in 1..l_data.COUNT
 10                          insert into t3 values l_data(i);
 11                  exit when test_curs%notfound;
 12          END LOOP;
 13          close test_curs;
 14  end;
 15  /

PL/SQL procedure successfully completed.


ops$tkyte%ORA11GR2> insert into t4 select * from t1;

72839 rows created.

*****************************************************************************

INSERT INTO T2 VALUES
 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 , :B15 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  72839     30.51      35.24          0       1535      82646       72839
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    72840     30.51      35.24          0       1535      82646       72839
********************************************************************************
INSERT INTO T3 VALUES
 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 , :B15 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     37      0.24       0.33          0       2298      10018       72839
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       38      0.24       0.33          0       2298      10018       72839
********************************************************************************
insert into t4 select * from t1
  
  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.14       0.24        716       2980       9326       72839
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15       0.25        716       2982       9326       72839

pretty compelling - slow by slow is slow. bulk by bulk is faster. single sql statement = perfection.

we write directly to the blocks above the HWM - we know that in order to rollback - all we have to do is "unadvance" the HWM - we don't need to undo the block changes - we just need to undo the dictionary update.

hence we do not need to store undo for each and every block above the HWM - we just need undo to put the HWM back where it was.




as for the 8, to 128 and so on - that is the algorithm behind a locally managed tablespace with system allocated extent sizes - we allocated a set of 8 block extents, then 128, then bigger and bigger. the larger the table becomes, the larger the extent we'll allocate for it over time.

if you use uniform extent sizes - yes, they would be 'uniform' - they are always the same size in a uniform extent tablespace.

direct path inserts + unique constraint = no dml error logging. Just use a conventional path insert for such a teeny tiny bit of data


Or write the select statement in a fashion to get just the single "correct" record for each employee.

insert into t1_test_work select * from pc_work@prpctrg;



why would you even consider writing anything else? Why do people always seem to go for the most complex, resource intensive approach every single time? Is someone teaching this in school? at work? where does this come from?????