Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 4, 2007

Logical I/O(consistent get) and Arraysize relation with SQL*PLUS

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 7:18 pm

“Set Arraysize” sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. Here is a small test case;

conn hr/hr

drop table test_arraysize purge ;
create table test_arraysize nologging as select * from all_source ;
set serveroutput on
exec show_space(upper(‘test_arraysize’));

Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 9,976
Total Blocks………………………. 10,240
Total Bytes……………………….. 83,886,080
Total MBytes………………………. 80
Unused Blocks……………………… 121
Unused Bytes………………………. 991,232
Last Used Ext FileId……………….. 4
Last Used Ext BlockId………………. 11,529
Last Used Block……………………. 903

PL/SQL procedure successfully completed.

set timing on
set autotrace traceonly statistics
show arraysize

arraysize 15 <– default value

select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 5
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 50
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 100
select text from test_arraysize where owner = ‘SYS’ ;

Here are the information given;

1- “select text from test_arraysize where owner = ‘SYS'” query returns 93963 rows for my case,
2- test_arraysize table Full Blocks 9976 and Unused Blocks 121, returned from show_space procedure
3- Formula is : Consistent Gets ~= (number of rows / arraysize) + (Full Blocks – Unused Blocks)

Lets test the formula from these information and above test scripts results;

** arraysize 15(default)
16155 consistent gets
Elapsed: 00:00:04.51

93963 rows / 15 arraysize = 6264,2
9976 Full Blocks – 121 Unused Blocks = 9855
6264,2 + 9855 = 16119,2

** arraysize 5
28233 consistent gets
Elapsed: 00:00:05.34

93963 rows / 5 arraysize = 18792,6
9976 Full Blocks – 121 Unused Blocks = 9855
18792,6 + 9855 = 28647,6

** arraysize 50
11819 consistent gets
Elapsed: 00:00:03.71

93963 rows / 50 arraysize = 1879,26
9976 Full Blocks – 121 Unused Blocks = 9855
1879,26 + 9855 = 11734,26

** arraysize 100
10904 consistent gets
Elapsed: 00:00:03.84

93963 rows / 100 arraysize = 939,63
9976 Full Blocks – 121 Unused Blocks = 9855
939,63 + 9855 = 10794,63

Responce time changes with the arraysize since logical I/O changes. Ok what about increasing arraysize to 500, what will be the impact;

set arraysize 15
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.82

set arraysize 500
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.96

Responce time got worser, overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance. So optimum arraysize for your query is a test subject, no silver bullet again :)

Also in recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.2.0

Refences Used : “consistent gets — Very puzzling” askTom thread
show_space procedure on asktom

About these ads

5 Comments »

  1. This demo and formula is only valid for full table scans and index fast full scans.

    Comment by H.Tonguç Yılmaz — June 7, 2007 @ 4:18 pm | Reply

    • It is a bit late to comment but I think this formula gives the exact result when you use manual segment space management. If you use the automatic segment space management (which is probably what you used) the equation is ~= instead of = .

      By the way there is also good explanation on this link with the help of block distribution

      http://viveklsharma.wordpress.com/2010/03/04/consistent-gets-myth/

      Comment by coskan — March 24, 2010 @ 12:39 am | Reply

  2. Coskan thanks for the comment, it is never to late, any input can be and should be enriched in time.

    Comment by H.Tonguç Yılmaz — March 25, 2010 @ 9:13 am | Reply

  3. [...] do the same to see similar results to me. {I keep meaning to do a dedicated post on arraysize but H.Tonguç YIlmaz has a nice post already on [...]

    Pingback by IOT part 3 – Significantly Reducing IO « Martin Widlake's Yet Another Oracle Blog — August 2, 2011 @ 11:18 am | Reply

  4. [...] do the same to see similar results to me. {I keep meaning to do a dedicated post on arraysize but H.Tonguç YIlmaz has a nice post already on [...]

    Pingback by IOT part 3 – Significantly Reducing IO « Ukrainian Oracle User Group — August 3, 2011 @ 9:45 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: