| Pagesize and performance [message #359896] |
Tue, 18 November 2008 09:56  |
john100
Messages: 8
Registered: August 2008
|
Junior Member |
|
|
We have a sqlplus script which spools out a reasonable amount of data (3m rows or so) to a file.
One one client machine throughput for producing the whole file is 5 min and on another 17 min. We query the same data from the same database in each case. The oracle network parameter files - sqlnet.ora, tnsnames.ora (there is no protocol.ora) are the same on both client machines; the client version of Oracle is the same. We don't have the option of say running the sql locally and then ftp'ing the file. We strongly suspect a glitch in the setup below the Oracle layer but while we are looking for it we have also been searching for a work around.
We have found that changing pagesize and linesize settings very significantly improves performance. What is confusing us is that reducing pagesize from setting pagesize 0 to setting pagesize 600 improves performance. Since pagesize 0 never does a page break we're confused as to why introducing a break every 600 lines improves performance. Can some one enlighten us?
|
|
|
|
| Re: Pagesize and performance [message #359899 is a reply to message #359896] |
Tue, 18 November 2008 10:57  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
1) do a ping from each of the client machines to the DB machine to see if network latency is very different. High network latency impacts chatty client/server communication greatly. Increasing sqlplus arraysize from 15 (my sqlplus default) to say 100 would reduce chattiness.
2) examine your login.sql and glogin.sql to see what non-standard settings may be being changed.
3) Monitor cpu and I/O on the client machines during a spool to see if either is a bottleneck (especially on the slow machine).
4) See how long it takes when you use "autotrace traceonly". That will make the data come back to sqlplus, but not be spooled out.show arraysize
set arraysize 100
set autotrace traceonly
select ...
5) If it's an I/O bottleneck during spooling, try moving the output file to a faster disk. Also "set trimspool on" to trim trailing blanks from each record up to the full "linesize".
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Pagesize and performance [message #360586 is a reply to message #360538] |
Fri, 21 November 2008 08:43 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It's not a generic thing.
I created a two column table, stuck 1,000,000 rows of data into it, and spooled it to disk, once with pagesize 0 and once with pagesize 600.
Results:$ tail -n -3 pagesize*.txt
==> pagesize0.txt <==
1000000 rows selected.
Elapsed: 00:06:54.48
==> pagesize600.txt <==
1000000 rows selected.
Elapsed: 00:06:40.46
|
|
|
|
|
|
| Re: Pagesize and performance [message #360963 is a reply to message #360685] |
Mon, 24 November 2008 08:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Other than the first query doing a handful of phsical reads, there's no difference between them:
==> pagesize0.txt <==
1000000 rows selected.
Elapsed: 00:06:59.31
Execution Plan
----------------------------------------------------------
Plan hash value: 3381401216
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 942K| 26M| 666 (5)| 00:00:08 |
| 1 | TABLE ACCESS FULL| TEST_025 | 942K| 26M| 666 (5)| 00:00:08 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69341 consistent gets
263 physical reads
0 redo size
25535828 bytes sent via SQL*Net to client
733722 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
==> pagesize600.txt <==
1000000 rows selected.
Elapsed: 00:07:01.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3381401216
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 942K| 26M| 666 (5)| 00:00:08 |
| 1 | TABLE ACCESS FULL| TEST_025 | 942K| 26M| 666 (5)| 00:00:08 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69341 consistent gets
0 physical reads
0 redo size
25535828 bytes sent via SQL*Net to client
733722 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
@john100 - any chance of you posting the same data for your exampe, so we can see what's actually going on?
[Updated on: Mon, 24 November 2008 08:16] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
