Re: Fetch calls and increasing arraysize
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 51198431
------- ------ -------- ---------- ---------- ---------- ----------
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 11198431
------- ------ -------- ---------- ---------- ---------- ----------
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