Consistent gets and arraysize Options

[Saptarshi B]

Hi,

I have been doing some testing with arraysize in 10gR2 and got some unexpected results. Please help
me to understand the behaviour. Thanks!
I have tables t1 and t2, each having 15000 rows. As I execute "select * from t1" and tkprof the
trace file, I see a significant reduction in consistent gets with the increase in arraysize from 10
to 1000 (as expected). However, when I execute "select * from t1,t2 where n1=n2" (doing hash join),
I can see a reduction in no. of fetches but the number of consistent reads remain same.
1. Does it imply, for joins arraysize does not have any impact on performance?
2. If 1 is true, using BULK COLLECT - FORALL constructs in PL/SQL whenever there are joins won't
give any performance benefit. Is that correct?

Details are given below:
create table t1 (row_num number, n1 number, txt varchar2(400));

create table t2 (row_num number, n2 number, txt varchar2(400));

insert into t1 (select rownum, trunc(rownum/15)+1, rpad(object_name,400,'-') from dba_objects where
rownum<=15000)

insert into t2 (select rownum, mod(rownum,15), rpad(object_name,400,'-') from dba_objects where
rownum<=15000)

select * from t1,t2 where n1=n2
arraysize 15
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13935 3.26 5.75 2577 1865 0 209000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13937 3.28 5.81 2577 1865 0 209000

arraysize 1000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 210 2.54 8.90 2577 1865 0 209000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 212 2.59 8.95 2577 1865 0 209000

[burleson]

Hi,

>> I see a significant reduction in consistent gets with the increase in arraysize from 10 to 1000 (as expected).

This is an interesting issue! However, it's an illusion, since arraysize does not determine "real" I/O, which remains constant.

The "set arraysize" in SQL*Plus governs the number of rows Oracle will fetch before shipping them back to the client.
Here is a test of the SQL*PLus arraysize:

http://www.dba-oracle.com/oracle_tips_sql_plus_arraysize.htm


See here:

http://viveklsharma.blogspot.com/2007/11/c...-gets-myth.html

These clearly shows the way Oracle Optimizer measure Consistent Gets and the effect of Arraysize on this. Also, increasing the value of arraysize will have an impact on the performance but, beaware, do not increase this to a higher value.

Consistent Gets are not a measure of number of blocks that are read into the cache but number of times, a block was read into the PGA. A block is read only once in the cache and touched multiple times. Our example above showed that with arraysize of 15, an 8k block was read once but was touched 3 times and hence had a 3 consistent gets for 1 block. This does not mean 3*8192 worth of data. The data was read only once.

**********************************************************
>> for joins arraysize does not have any impact on performance?

The arraysize WILL have an impact for joins that perform full-table scans (hash joins, merge joins). . . .

Why is this important? In production you cannot use SQL*Plus anyway . . .