Re: Performence on Oracle instance running on Sun Solaris/Sparc

Good detail.

Question: Is one instance accessed over a LAN and the other accessed over a WAN. The network latency (not necessarily bandwidth) could be the issue. SQL*Net does quite a bit of talking back and forth.

Regards,
Dave

Tommy Svensson <tkman23_at_hotmail.com> wrote in message news:ebf7c6db.0105221323.3860f883_at_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
>
> file://Tommy
>
> North Sea Fish and Meat Company
Received on Tue May 22 2001 - 19:45:06 CDT