| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How long should statspack.snap take to run?
Mark D Powell wrote:
> Statspack can be a very useful took IF you work with it enough to
> understand what it shows you. While the default report has limited
> usefulness for finding specific causes of performance problems the
> report can identify if the database as a whole had a performance
> problem. Higher level settings can be used to help locate resource
> intensive SQL, which generally sits at the bottom of all performance
> problems.
>
> Once you know you have a problem then you can use other tools to help
> you locate the true cause and solve the problem.
>
> IMHO -- Mark D Powell --
Excellent points. It is important to use the tools correctly.
Example #1:
The DBA who left here four years ago used Statspack and Toad to monitor
performance. As the lead programmer at the time, I told him that the
database seemed to be performing slower than expected. He showed me
Statspack reports, fancy graphical displays in Toad, the buffer cache
hit ratio over 99% - everything looked normal. When he left, I assumed
DBA responsibilities, dumped Statspack and Toad, and started doing
things the hard way. The end result was significantly faster database
performance - a job scheduling process that was taking 2.5 hours to run
now completed in 25 minutes, and with a faster client it completed in
18 minutes. That's roughly a five fold application performance
increase without making any hardware changes, and without rebuilding
the database. It is easy to be lured into thinking that something is
normal.
Example #2:
I had the opportunity a couple months ago to replace our old dual
2.2GHz Oracle server with a dual 3.66GHz Oracle server. At best I
expected a 50% performance inprovement over the older server.
According to a stop watch, data retrieval in Excel and in programs that
I wrote complete 20 times faster than the same activity on the old
server. According to a stop watch, our ERP software completes data
retrieval 5% to 20% faster than when executed against the old database
server. Why the descrepancy? This is probably the point where the DBA
would say that everything in the database is working correctly, and the
finger would be pointed at the network administrator. The network
administrator would see that everything in the gigabit network is
working correctly and the network cards are running fine, and would
point the finger at the server administrator. The server administrator
would say that the server is only showing 3% CPU utilization, the hard
drives are not overworked, network utilization is at most 6% - must be
a client computer problem and the finger is pointed at the computer
technician. The client CPU is barely engaged, the hard drive isn't
doing much of anything, and the finger is pointed at the application
developer. Was anything accomplished in this exercise, other than the
conclusion that the system is running normally? This is the reason
that I developed my own Oracle performance monitoring tool.
Example case - a report in the ERP application required 60 seconds to complete when run against the new database server and a couple seconds longer when run against the old database server. This was unacceptable performance, so I started digging. An Oracle extended SQL trace showed this for total time, which the database server could be held accountable during the 60 second run time of the report: 0.021 seconds - latch: library cache
Where was the rest of the 59.079 seconds spent?
0.070 seconds - SQL*Net message to client 0.002 seconds - SQL*Net more data to client ----------------------------------------------------------------0.072 seconds sending responses to the client - not too bad for 61,153 round trips to the client.
Where was the rest of the 59 seconds spent? An Ethereal packet capture shows 8,000 times in 60 seconds that the client asked the Linux file server that hosts the ERP .exe files, do you have a SQL.INI file that I can access? The Linux server responds - no such file exists, and at least 0.004 seconds is wasted each time sending the request, and being rejected. 32.000 seconds of the 59 remaining seconds are now accounted for by this unnecessary traffic.
Where was the rest of the 27 seconds spent? Likely searching the path specified on the computer for the SQL.INI file and the various other configuration files. My guess is that the report was looking for the same substitution entries in the SQL.INI file (DB platform independent ERP package), each of the 8,000 times it tried accessing that file.
Ever wonder how long this report would take over a WAN connection with
300ms ping times?
(61,153 * 0.300) + (8,000 + 0.300) = 20745.9 seconds = 5.76 hours.
The point is to use the tools correctly, as indicated by Mark. Do not rely only on one tool that has a fancy report, or fancy colored dials that states everything is normal. Maybe your perspective of what is normal needs to be reconsidered. Statspack can be a starting point, but should not be both a starting point and an ending point.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Sat Jul 15 2006 - 07:30:32 CDT
 |
 |