Re: Fetch calls and increasing arraysize
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "oracledba71@xxxxxxxxx" <oracledba71@xxxxxxxxx>, Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 1 Oct 2013 10:12:20 -0700 (PDT)
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@xxxxxxxxx>
To: Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
