Home » SQL & PL/SQL » SQL & PL/SQL » Pagesize and performance (SQL*Plus: Release 9.2.0.8.0 on Solaris 10)
Pagesize and performance [message #359896] Tue, 18 November 2008 09:56 Go to next message
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 Go to previous messageGo to next message
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 #360017 is a reply to message #359899] Wed, 19 November 2008 04:24 Go to previous messageGo to next message
john100
Messages: 8
Registered: August 2008
Junior Member
3) Has potential. Haven't tried that one yet.

Still curious why pagesize, particularly reducing it from pagesize 0 to pagesize 600 would improve performance.
Re: Pagesize and performance [message #360024 is a reply to message #360017] Wed, 19 November 2008 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would not.

Regards
Michel
Re: Pagesize and performance [message #360538 is a reply to message #360024] Fri, 21 November 2008 05:29 Go to previous messageGo to next message
john100
Messages: 8
Registered: August 2008
Junior Member
That's exactly what I said.

Don't you just hate it when a developer prooves you wrong. With a test case. With repeatable results.
Re: Pagesize and performance [message #360548 is a reply to message #360538] Fri, 21 November 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post the test case.

Regards
Michel
Re: Pagesize and performance [message #360586 is a reply to message #360538] Fri, 21 November 2008 08:43 Go to previous messageGo to next message
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 #360685 is a reply to message #360586] Sat, 22 November 2008 03:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Repeat with AUTOTRACE on to see the stats on the query

Ross Leishman
Re: Pagesize and performance [message #360963 is a reply to message #360685] Mon, 24 November 2008 08:15 Go to previous message
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

Previous Topic: Need procedure to display all the rows
Next Topic: ORA-01722: invalid number in 10g
Goto Forum:
  


Current Time: Mon Dec 05 21:32:25 CST 2016

Total time taken to generate the page: 0.09345 seconds