05 February, 2009

Array Processing, SQL*Net RoundTrips and consistent gets

When fetching large datasets (even if the SQLPlus Client is on the *same* server as the Database), it is important to use the right ARRAYSIZE.

Here I demonstrate the impact of ARRAYSIZE on
a. Total volume of bytes transferred from the Server to the Client
b. Number of SQL*Net round-trips
c. Number of 'consistent gets' (and, even, a slightly higher CPU usage for the higher logical reads, but which isn't presented by SET AUTOTRACE )

I shall be querying a table with 1.6million rows. (This has the same structure and multiple copies of the contents of DBA_OBJECTS). I avoid using the index -- I am fetching ALL rows and ALL columns.


ora10204>sqlplus hemant/hemant

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 23:26:25 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> REM Start with the default arrasize
SQL> show arraysize
arraysize 15
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
128823 consistent gets
11211 physical reads
116 redo size
83292501 bytes sent via SQL*Net to client
1188866 bytes received via SQL*Net from client
108036 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed

SQL> set timing on
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.

Elapsed: 00:00:42.22

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
128822 consistent gets
11436 physical reads
0 redo size
83292501 bytes sent via SQL*Net to client
1188866 bytes received via SQL*Net from client
108036 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed

SQL>


This was 83.292million bytes sent by the server to my SQLPlus client via SQLNet in 108,036 round-trips. The query did 128,822 'consistent gets'.

Now, I switch to an ARRAYSIZE of 100. Thus, the SQLPlus client will "ask" Oracle for 100 rows at each fetch.


SQL> REM Now I set a larger arraysize
SQL> SET ARRAYSIZE 100
SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.73

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38238 consistent gets
11434 physical reads
0 redo size
66487794 bytes sent via SQL*Net to client
178747 bytes received via SQL*Net from client
16207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.68

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38238 consistent gets
11414 physical reads
0 redo size
66487794 bytes sent via SQL*Net to client
178747 bytes received via SQL*Net from client
16207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed

SQL>
Now, the server has to send 66.488million bytes to me in 16,207 round-trips. Since my ARRAYSIZE has increased 7 fold, the number of round-trips has dropped to almost 1/7th. More rows are being sent by the DB server in each SQL*Net packet. Even 'consistent gets' has dropped from 128,822 to 38,238. This is because fewer Fetch calls (which we would have seen in a tkprof of trace file with SQL_TRACE enabled) have had to be executed, and the number of blocks being re-read has declined. Execution time has also improved.

What about the other extreme ? An ARRAYSIZE of 2 ?


SQL> REM Now I set a very small arraysize
SQL> SET ARRAYSIZE 2
SQL> select /*+ FULL (st) */ * from source_table st
2 /

1620512 rows selected.

Elapsed: 00:03:10.08

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
821428 consistent gets
11721 physical reads
0 redo size
211798944 bytes sent via SQL*Net to client
8913297 bytes received via SQL*Net from client
810257 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:03:46.49

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
821428 consistent gets
11466 physical reads
0 redo size
211798944 bytes sent via SQL*Net to client
8913297 bytes received via SQL*Net from client
810257 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1620512 rows processed


SQL>
With a very small ARRAYSIZE, the server sent 211.799MB in 810,257 round-trips ! The number of 'consistent gets' has jumped to 821,428 !

So we have :

ARRAYSIZE MB sent RoundTrips consistent query
gets time
2 211.799 810,257 821,428 190sec
15 83.292 108,036 128,822 42sec
100 66.488 16,207 38,238 17sec


For some other tests with PAGESIZE and LINESIZE, see my previous post "Tuning Very Large SELECTs in SQLPlus"

3 comments:

Gyanjit-Oracle said...

GyanjitKanungo- Good one, so FTS can be optimized by the following all at one time.
1.increase the array size.
2.put the optimum vallue for DB_FILE_MULTIPLE_READ_COUNT .
3. prallel degree.

Amit Kumar said...

Hi Hemant,

When I ran "show arraysize" command on SQL* PLUS , I didnt get any output. Just blank SQL prompt came.

Can u please tell me what should I do to see the arraysize?

Regards,
Amit

Hemant K Chitale said...

Amit,

You have to issue a "SHOW ALL" in SQLPlus.

Hemant