We have recently upgraded to OE 11.3 version.The application and database appears to be slow in one particular location.But we didn't face any performance issues in the application or in the databases.i have checked few parameters in promon like buffer hits,Number of databse buffers,-spin parameter.
Buffer hits -97%
number of databse buffers - 50000
-spin berfore timeout- 2000 which looks very low.
is there any way we can find the issue why the database and application is very slow in only that location?
we are not facing any performance issue form other locations.
does increasing the -spin value would increase the performance in that location?
Location refers to geographical location.
You are not providing very much information:
A) About your intentions. Do you just want everything to be "faster"? Or are there other needs - like servers are out of memory/under heavy load etc.
B) About your system. How many users, databases, tables, indices etc etc.
C) When you say location - what do you really mean? Is it a specific program, a specific query/search or a specific (geographical) location?
Buffer hits
97% buffer doesn't say that much on its own:
A simple explanation of buffer hits:
A record read in the buffer (memory) is a "hit" a record read from the disk is not.
1 000 record lookups with 97% buffer hits means:
970 records are read from buffer (memory). (0.97 x 1 000)
30 records are read from disk. (0.03 x 1 000)
Increasing to 99% buffer hits means you will remove:
20 disc reads. (0.02 x 1 000)
1 000 000 000 record lookups with 97% buffer hits means:
970 000 000 records are read from buffer (memory).
30 000 000 are read from disk.
Increasing to 99% buffer hits means you will remove:
20 000 000 disc reads.
In the first case you won't notice anything at all most likely when going from 97 to 99%. In the second case load on discs will decrease a lot.
Conclusion
Increasing -B might affect your performance as well as buffer hits. Changing -spin might also affect your performance by utilizing more of your CPU. It all depends on how your system works. The best way really is to try (with a test setup).
The first thing you really should do is to look at your application and the most run queries - do the utilize optimal indices? If not you can most likely tune very much without getting big differences. Read up on index usage, XREF-compiling and Different VST-tables you can use to check index performance etcetera.
This is a good place to start:
Top 10 (really more) Performance Tuning Tips For The Progress Database
Also, you can try the excellent free ProTop software for and get some guesstimations for -B: