Thanks for the question, Reader.

Asked: March 08, 2017 - 7:11 am UTC

Last updated: March 09, 2017 - 2:05 am UTC

Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Dear Team,
If i execute a delete statement within a procedure what will be array size it will work with?
create or replace procedure del_test as
begin
delete from big_table;
commit;
end;

If i execute this procedure from a job is the array size going to be different?

If we use dblink in the above procedure to delete data at target data base what will be the array size? Is there any way to define array size for db link?

Thanks & Regards.

and Chris said...

The array size is only relevant for fetching results back to the client. Deletes don't send anything back. So it doesn't apply!

For example, here's a 1,000 row table. As I increase the arraysize the work (consistent gets) to fetch all the data decreases:

SQL> create table t as
  2  with rws as (
  3    select level x, 'xxxxxxxxxxx' y
  4    from dual connect by level <= 1000
  5  )
  6    select rownum x from rws;

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
      1000

SQL>
SQL> set autotrace trace stat
SQL> set array 10
SQL> select * from t;

1000 rows selected.


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        111  consistent gets
          0  physical reads
          0  redo size
      19752  bytes sent via SQL*Net to client
       1588  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> set array 100
SQL> select * from t;

1000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
      10235  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> set array 1000
SQL> select * from t;

1000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       9283  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

This is because the database has to do fewer roundtrips to get all the rows. You can see this in the "SQL*Net roundtrips to/from client" stat.

Repeat the test, but this time with delete and the work and roundtrips stay the same:

SQL> rollback;

Rollback complete.

SQL> delete t;

1000 rows deleted.


Statistics
----------------------------------------------------------
         10  recursive calls
       1050  db block gets
         18  consistent gets
          0  physical reads
     261408  redo size
        826  bytes sent via SQL*Net to client
       1215  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> rollback;

Rollback complete.

SQL> set array 10
SQL> delete t;

1000 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
       1038  db block gets
          4  consistent gets
          0  physical reads
     260896  redo size
        826  bytes sent via SQL*Net to client
       1215  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> rollback;

Rollback complete.

SQL> set array 100
SQL> delete t;

1000 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
       1040  db block gets
          4  consistent gets
          0  physical reads
     260964  redo size
        825  bytes sent via SQL*Net to client
       1215  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> rollback;

Rollback complete.

SQL> set array 10
SQL> delete t;

1000 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
       1038  db block gets
          4  consistent gets
          0  physical reads
     260892  redo size
        826  bytes sent via SQL*Net to client
       1215  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

and what is default array size?

A reader, March 08, 2017 - 1:24 pm UTC

March 08, 2017 - 3:00 pm UTC

It depends on the client. Which are you using?

Arraysize Default

SeanMacGC, March 08, 2017 - 2:10 pm UTC

SQL> show array
arraysize 15
SQL>

March 08, 2017 - 3:01 pm UTC

If you're using SQL*Plus ;)

Same 15 on sql developer v 11 2 0 3

A reader, March 08, 2017 - 7:51 pm UTC

March 09, 2017 - 2:05 am UTC

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating