-1

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.

Community
  • 1
  • 1
user3715001
  • 131
  • 3
  • 18

3 Answers3

1

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:

  • Are there 1 000 record lookups or 1 000 000 000?
  • "Primary Buffer hits" says nothing about single tables. Perhaps all "buffer misses" comes from a single table (or very few).

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:

ProTop

Jensd
  • 7,886
  • 2
  • 28
  • 37
0

This question is very vague. You would be much better off asking it in a forum where some "back and forth" can take place and where you can be guided to a more complete answer.

You might try:

These forums all have dedicated DBA focused areas where many people routinely chip in to help.

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
0

We have found that adding (on linux servers) -T /dev/shm made a big performance improvement

/oe116> cat startup.pf -T /dev/shm

You can also add this to your common.pf files

You can see the before and after of this by doing a ( with the database running) lsof |grep delete

and you should see a lot of locations on your hard disk then after you add it and restart your database` it will be using shared memory

Alex-TBG
  • 1