Re: Fetch calls and increasing arraysize

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 1 Oct 2013 10:12:20 -0700 (PDT)
Message-ID: <1380647540.90194.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>



You're not looking at the whole picture -- increasing arraysize also increases memory usage and if that increase involves paging/swapping then you do get increased response time.  There is a point of diminishing returns for the arraysize setting that is dependent on the memory resources you have on the server running the database, the row length and the number of concurrent processes running while your query executes, among other things.  I have an example of changing the arraysize parameter and I see no appreciable increase in the query time and I'm querying DBA_OBJECTS (I have grepped the log file generated by my example for pertinent information):  
SQL> -- arraysize 15
Elapsed: 00:00:29.48
       4549  SQL*Net roundtrips to/from client SQL> -- arraysize 100
SQL> set arraysize 100
Elapsed: 00:00:25.35
        684  SQL*Net roundtrips to/from client SQL> -- arraysize 1000
SQL> set arraysize 1000
Elapsed: 00:00:26.04
         70  SQL*Net roundtrips to/from client SQL> -- arraysize 5000
SQL> set arraysize 5000
Elapsed: 00:00:26.17
         15  SQL*Net roundtrips to/from client So the roundtrips decreased significantly ( I didn't run a 10046 trace ) yet the elapsed time actually decreased from the setting of 15  to the setting of 100; it increased slightly moving to a setting of 1000  and was just about the same for the setting of 5000.  The query returned 68213 rows.  
There are a number of variables that affect the elapsed time besides the arraysize setting.  You cannot attribute the increase solely on that setting as there are other areas which also affect that time.

David Fitzjarrell  



 From: oracledba <oracledba71_at_gmail.com> To: Oracle-L Freelists <Oracle-L_at_freelists.org> Sent: Tuesday, October 1, 2013 8:22 AM
Subject: Fetch calls and increasing arraysize   

Folks,
Here is what documented in Oracle11gR2 SQL*PLUS User's guide and reference.

SET ARRAY[SIZE] {15 | n}

Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. *A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. *Values
over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

Here is the tkprof output of a query with the arraysize 15.

call    count      cpu    elapsed      disk      query    current rows
------- ------  -------- ---------- ---------- ---------- ----------


Parse        1      0.97       1.01          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch    79897    692.59    *1152.05*    3706124    2769429          5
1198431
------- ------  -------- ---------- ---------- ---------- ----------


total    79899    693.57    1153.07    3706124    2769429          5 1198431

tkprof output of the same query with the arraysize 5000.

call    count      cpu    elapsed      disk      query    current rows
------- ------  -------- ---------- ---------- ---------- ----------


Parse        1      0.97       0.99          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch      241    678.86    *1197.28*    3697261    2768993          1
1198431
------- ------  -------- ---------- ---------- ---------- ----------


total      243    679.83    1198.28    3697261    2768993          1 1198431

Well.The number of fetch calls were reduced from 79897 to 241. wow! But then it spent  ~45 seconds more than the previous one.

what do I infer from the documentation? I don't understand what do they mean by "increasing efficiency of queries"? Is it just reducing the number of fetch calls? But then it doesn't help me reducing the total elapsed time?

Thanks

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 01 2013 - 19:12:20 CEST

Original text of this message