Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
> Consistent gets and arraysize
Saptarshi B
post Jul 23 2009, 09:17 AM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 22-July 09
From: India
Member No.: 41,310



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


--------------------
Saptarshi Basu
Go to the top of the page
 
+Quote Post
 
Start new topic
Replies
burleson
post Jul 23 2009, 02:43 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,601
Joined: 26-January 04
Member No.: 13



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

QUOTE
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 . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Posts in this topic


Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th October 2014 - 03:02 AM