Performence on Oracle instance running on Sun Solaris/Sparc

From: Tommy Svensson <tkman23_at_hotmail.com>
Date: 22 May 2001 14:23:35 -0700
Message-ID: <ebf7c6db.0105221323.3860f883@posting.google.com>

Hi!

We have huge and strange performance problem on one of our oracle instance
running our ERP software on Sun Solaris Servers and OS. We have done a lot of tuning and testing to found the performance problem but
without any luck :-(

We have two server running our ERP software one in Canada and one in Norway both
useing Oracle 7.3.4 on Solaris 2.6 Sparc platform. The hardware and OS setup is
identical between Canada and Norway unless the memory which is 2Gb in Canada and
only 1Gb in Norway. The database size is 12Gb in Canada and only 4Gb.

The performence in the Canada instance is good, but the performence in Norway
is terriable. A report this is take 1 minutes in Canada can take up to 2-3 hours on the Norway setup.

Please note the Hardware isn't the high-end, and can be more tuned by adding
additional CPU, but the main "problem" is the different in performance between
Canada and Norway. The setup can be better with on extra diskset for the second
redolog member of the groups, more CPU, even more memory. But again the
problem is the different in performance between Canada and Norway not to make
the ultimate setup.

Canada setup

Sun Ultra Enterprise 450 Server with 1 248Mhz CPU, 2Gb Memory
9Gb disk for OS+Oracle binary, redo logs
RAID10 Setup 2*6 4Gb disk = 24Gb data for databases datafiles
RAID5 setup, 3 18GB disk = 36Gb data for archive logs, redo logs
Database size 12Gb
Orginal SGA Size: 200Mb

Norway Setup

Sun Ultra Enterprise 450 Server with 1 248Mhz CPU, 1Gb Memory
9Gb Disk for OS+Oracle binary, redo logs
RAID10 Setup 2*6 4Gb disk = 24 Gb data for databases datafiles
RAID5 setup, 3 18GB disk = 36 Gb data for archive logs, redo logs
Database size 4Gb
Orginal SGA Size: 200Mb
1. Our first hint was that the different in memory was the reason to the bad performance on our Norway server. But we run a lot of huge reports on the server and running iostat and vmstat but they didn't show any sign on paging/swapping problem.
  - vmstat in Norway during huge report --

1 0 0 503488 121416 0 204 0 0 0 0 0 0 0 0 0 121 663 75 99 1 0
1 0 0 503360 121416 0 210 0 0 0 0 0 0 2 0 2 147 2614 87 97 3 0
0 0 0 503488 121416 0 163 0 0 0 0 0 0 1 0 1 128 1700 74 99 1 0
1 0 0 503488 121400 0 204 0 0 0 0 0 0 3 0 3 166 4378 97 98 2 0
1 0 0 503488 121384 0 204 0 0 0 0 0 0 1 0 1 157 1522 85 98 2 0
1 0 0 503360 121368 0 210 1 0 0 0 0 0 2 0 2 154 4014 92 97 3 0
0 0 0 503488 121368 0 163 0 0 0 0 0 0 1 0 1 129 1605 76 98 2 0
1 0 0 503488 121368 0 197 0 0 0 0 0 0 3 0 3 166 5599 96 98 2 0
1 0 0 503488 121360 0 204 0 0 0 0 0 0 0 0 0 115 658 76 99 1 0
1 0 0 503360 121352 0 210 0 0 0 0 0 0 3 0 3 166 6180 96 98 2 0
0 0 0 503488 121344 0 163 0 0 0 0 0 0 1 0 1 136 2784 74 99 1 0
1 0 0 503488 121344 0 197 0 0 0 0 0 0 3 0 3 177 2388 88 98 2 0
1 0 0 503488 121344 0 204 0 0 0 0 0 0 0 0 0 118 653 78 100 0 0
1 0 0 503360 121344 0 210 0 0 0 0 0 0 2 0 2 149 3710 88 97 3 0
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr f0 m0 m3 m1 in sy cs us sy id
1 0 0 503488 121344 0 163 0 0 0 0 0 0 1 0 1 129 2648 74 99 1 0
1 0 0 503488 121344 0 204 0 0 0 0 0 0 2 0 2 148 5683 87 97 3 0
1 0 0 503488 121344 0 210 0 0 0 0 0 0 1 0 1 138 3462 88 99 1 0
1 0 0 503360 121328 0 204 4 0 0 0 0 0 2 0 2 143 5340 92 98 2 0
0 0 0 503488 121320 0 163 0 0 0 0 0 0 2 0 2 167 2354 78 99 1 0
1 0 0 503488 121320 0 204 0 0 0 0 0 0 1 0 1 135 2325 81 99 1 0
1 0 0 503488 121320 0 210 0 0 0 0 0 0 0 0 0 121 681 81 100 0 0
1 0 0 503360 121320 0 204 0 0 0 0 0 0 1 0 1 145 4440 88 99 1 0
0 0 0 503488 121312 0 163 0 0 0 0 0 0 1 0 1 146 4596 78 98 2 0
1 0 0 503488 121304 0 204 0 0 0 0 0 0 1 0 1 132 2640 83 98 2 0
1 0 0 503488 121304 0 210 0 0 0 0 0 0 1 0 1 144 2682 89 99 1 0
1 0 0 503360 121304 0 204 0 0 0 0 0 0 3 0 3 179 4019 87 97 3 0
0 0 0 503488 121296 0 183 0 0 0 0 0 0 1 0 1 137 1455 76 99 1 0
1 0 0 503488 121296 0 232 0 6 6 0 0 0 1 0 1 138 2918 86 98 2 0
0 0 0 503624 121296 0 170 0 0 0 0 0 0 0 0 0 123 984 72 99 1 0
2 0 0 503360 121288 0 197 3 0 0 0 0 0 2 0 2 147 6037 87 96 4 0
1 0 0 503488 121280 0 176 0 0 0 0 0 0 1 0 1 133 1336 73 99 1 0
0 0 0 503312 121040 0 214 0 0 0 0 0 0 0 0 0 123 2206 95 99 1 0
1 0 0 502840 120872 0 197 0 0 0 0 0 0 4 0 4 222 4298 89 98 2 0
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr f0 m0 m3 m1 in sy cs us sy id
0 0 0 502968 120872 0 176 0 0 0 0 0 0 2 0 2 159 5390 86 99 1 0
1 0 0 502968 120872 0 204 0 0 0 0 0 0 1 0 1 130 2181 75 99 1 0
1 0 0 502840 120872 0 224 0 0 0 0 0 0 1 0 1 131 3176 83 99 1 0
0 0 0 502968 120872 0 176 0 0 0 0 0 0 0 0 0 121 579 75 99 1 0
1 0 0 502968 120872 0 204 0 0 0 0 0 0 1 0 1 148 3561 85 97 3 0
1 0 0 502968 120864 0 210 0 0 0 0 0 0 0 0 0 124 1320 80 100 0 0
0 0 0 502384 120320 0 271 0 0 0 0 0 0 5 0 5 237 7261 106 94 6 0
1 0 0 502232 120176 0 204 3 0 0 0 0 0 1 0 1 128 1883 82 99 1 0
1 0 0 502104 120168 0 190 0 0 0 0 0 0 2 0 2 151 4678 90 99 1 0
0 0 0 502232 120168 0 183 0 0 0 0 0 0 0 0 0 118 605 76 99 1 0

end vmstat norway --
iostat --

tty fd0 md0 md3 md10 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id

0 8 0 0 0 10 1 9 0 0 0 11 1 13 99 1 0 0

0 24 0 0 0 6 1 10 0 0 0 6 1 17 99 1 0 0

0 8 0 0 0 11 1 13 0 0 0 11 1 19 96 4 0 0

0 8 0 0 0 13 2 13 0 0 0 13 2 19 98 2 0 0

0 8 0 0 0 10 1 10 0 0 0 10 1 15 98 2 0 0

0 8 0 0 0 14 2 12 0 0 0 14 2 17 99 1 0 0

0 8 0 0 0 8 1 11 0 0 0 8 1 12 99 1 0 0

0 8 0 0 0 13 2 11 0 0 0 13 2 19 98 2 0 0

0 8 0 0 0 14 2 9 0 0 0 14 2 13 98 2 0 0

0 8 0 0 0 9 1 12 0 0 0 9 1 16 99 1 0 0

0 8 0 0 0 7 1 11 0 0 0 7 1 15 99 1 0 0

0 8 0 0 0 18 2 10 0 0 0 18 2 15 98 2 0 0

0 8 0 0 0 10 1 8 0 0 0 10 1 11 99 1 0 0

0 8 0 0 0 7 1 11 0 0 0 7 1 15 99 1 0 0

0 8 0 0 0 14 2 11 0 0 0 14 2 16 97 3 0 0

0 8 0 0 0 15 2 10 0 0 0 15 2 15 98 2 0 0

0 8 0 0 0 6 1 10 0 0 0 6 1 15 99 1 0 0

0 8 0 0 0 10 1 10 0 0 0 11 1 16 99 1 0 0

0 8 0 0 0 22 3 10 0 0 0 22 3 13 98 2 0 0

tty fd0 md0 md3 md10 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id

0 8 0 0 0 5 1 11 0 0 0 5 1 15 99 1 0 0

0 24 0 0 0 9 1 13 0 0 0 9 1 20 98 2 0 0

0 8 0 0 0 13 2 11 0 0 0 13 2 17 97 3 0 0

0 8 0 0 0 14 2 10 0 0 0 14 2 13 98 2 0 0

0 8 0 0 0 6 1 12 0 0 0 6 1 18 98 2 0 0

0 8 0 0 0 8 1 11 0 0 0 8 1 21 99 1 0 0

0 8 0 0 0 15 2 10 0 0 0 15 2 17 98 2 0 0

0 8 0 0 0 12 2 12 0 0 0 12 2 15 98 2 0 0

0 8 0 0 0 5 1 9 0 0 0 6 1 11 98 2 0 0

0 8 0 0 0 14 2 11 0 0 0 14 2 22 98 2 0 0

0 8 0 0 0 13 2 11 0 0 0 14 2 14 98 2 0 0

0 8 0 0 0 10 1 11 0 0 0 10 1 15 98 2 0 0

0 8 0 0 0 6 1 10 0 0 0 6 1 14 99 1 0 0

0 8 0 0 0 16 2 10 0 0 0 16 2 16 96 4 0 0

0 8 0 0 0 11 1 11 0 0 0 11 1 14 99 1 0 0

0 8 0 0 0 7 1 11 0 0 0 7 1 15 99 1 0 0

0 8 0 0 0 13 2 10 0 0 0 13 2 15 98 2 0 0

0 8 0 0 0 14 2 10 0 0 0 14 2 17 98 2 0 0

end iostat --

As far as I can say even if the Norway server is using 700Mb of the swap space
the memory should not be a performance problem. It don't do a lot of paging
according to the vmstat (I have learn to watch the sr field not the pi/po field).

Iostat and vmstat also show that the disk isn't the problem, io-wait is near
0% so there is not disk or i/o problem causing the bad performance in Norway.

We also note from vmstat,iostat,top that the Oracle process near take 99% of
the CPU during the reports.

2) Next step was do found out if the oracle instance run other huge reports during the time we did the performance test but we didn't find any
other job unless our test jobs in the oracle server.

3) Next step was do tune the setup of buffers and SGA pool size on the oracle
instance in Norway, we have try different values both go upp to 500Mb and even
go down the half the size but we didn't "won much" just on 1-5% win/lose.

4) Next step was do some tracing on the SQL-level we try to compare the setup
of views,index between the Norway and Canada setup even if our ERP supplier
says that is should be the same.
We found some minor different but it should cause the performance problem.
We also try to create the views,index used in Norway in the Canada database to
se if we did hit the same performance as in Norway but the performance was the
same as we used Canada original views and sql-questions.

5) Next step we did was to do explain on the sql_questions to see if we found any
different between the Norway and Canada setup. But again the explain was the
same unless one case but that was on of the few sql-questions which run with the same
speed in Norway and Canada.

6) Next step was to turn on full sql_trace and use tkprof to compare the same
sql questions between the Norway and Canada. Here we found something interesting.

We below attach some of the output, we only show the part of trace file which
take longest time and is showed the greatest different between the Canada setup
and Norway setup

Canada setup ---

select *
from
payment_from_auth_customer

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.11       0.12          1          0          1  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch     1149     11.35      15.70       3979     289910          0  
     1148

------- ------ -------- ---------- ---------- ---------- ----------

total     1151     11.46      15.82       3980     289910          1  
     1148

end canada ---

Look good, great performance at least compared to the Norway performance.

Norway setup --- select * from payment_from_auth_customer

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.03          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch      151  12483.17   12819.54          2  397742108          0  
      150

------- ------ -------- ---------- ---------- ---------- ----------

total      153  12483.20   12819.57          2  397742108          0  
      150

end norway ---

Strange, look like a joke? Look at the query field 397 742 108 blocks = 397 742 108*8192 = 3258 303 348 736 = 3200 Gb, almost 1000 times our Norway
databases size?
Is this right? We don't think so but we can't understand why?

Other example

canada --- SELECT SUM(CURR_AMOUNT) PLAN_CURR_AMOUNT FROM INVOICE_UTILITY_PAY_PLAN_PUB WHERE COMPANY = :b1 AND IDENTITY = :b2 AND PARTY_TYPE_DB = 'SUPPLIER' AND INVOICE_ID = :b3 AND AUTHORIZED = 'FALSE'

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.02       0.02          0          0          0  
        0
Execute   3148      1.20       2.60          0          0          0  
        0
Fetch     3148      0.77       2.78        147      15749          0  
     3148

------- ------ -------- ---------- ---------- ---------- ----------

total     6297      1.99       5.40        147      15749          0  
     3148

end canada ---
norway ---

SELECT SUM(CURR_AMOUNT) PLAN_CURR_AMOUNT FROM
INVOICE_UTILITY_PAY_PLAN_PUB WHERE COMPANY = :b1 AND IDENTITY = :b2 AND
PARTY_TYPE_DB = 'SUPPLIER' AND INVOICE_ID = :b3 AND AUTHORIZED = 'FALSE'

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.04       0.04          0          0          0  
        0
Execute   1477      0.73       0.72          0          0          0  
        0
Fetch     1477    738.65     760.88        269   29113399          0  
     1477

------- ------ -------- ---------- ---------- ---------- ----------

total     2955    739.42     761.64        269   29113399          0  
     1477

en or index have to much extents in norway compared to Canada but we didn't found anythings suspicious.

8)We have already defrag the databases both in Canada and Norway.

9)We have also look in the log files run prtdiag to found any possible hardware problem but everything works okey.

Any idea? What we can test or any hint to find the real problem? WE don't think
the problem is 2Gb in Canada vs 1Gb in Norway and because of the bad financl
status of our company we don't like to buy in 1Gb Sun memory for $6000 to test
with. We don't think it will solve the performence problem, and if we spend
$6000 on it and it didn't we will be the first of 1000 to be fired.

Other problem on the Norway instance is that the database file in our main
tablespace is soon full, we only have 1-2Mb free on it. Perhaps which is what
cause the performance problem we have in Norway? But should it be so huge
performance problem if one tablespace is soon full?

Perhaps the best and first option is to recreate the Norway instance, but the
problem has been in Norway even before the last defrag (done in less then
1 month) and we are pretty sure the tablespace had more space before the
defrag.

We have also read the alert.log file but don't find anything there.

Please help us!

Kind regards

//Tommy

North Sea Fish and Meat Company Received on Tue May 22 2001 - 16:23:35 CDT