Forum Post: RE: Buffer the whole database

  • Thread starter Thread starter Rob Fitzpatrick
  • Start date Start date
Status
Not open for further replies.
R

Rob Fitzpatrick

Guest
I wouldn't worry overly about warming up the cache. Database startup isn't something that happens terribly frequently; at least, it shouldn't. If you really want to, as Tom said you can run a DB analysis which reads the whole DB through the buffer pool. It can also be run on a per-area basis. The tuning challenge is not necessarily to get the entire DB memory-resident, however. Many applications have large transaction history tables where the data is not read much if at all once it reaches a given age. Caching that data doesn't help you much. What you want is to have the data that is frequently accessed by the application (its "working set", if you will) in the buffer pool, to reduce your physical I/O. Use of the Alternate Buffer Pool can also help with that, allowing you to relieve memory pressure on frequently-accessed data. Also, you don't have to move to 11.x. I'm not discouraging it at all but you can get a 64-bit RDBMS license in 10.2B. I agree with your inclination not to mask application problems by throwing hardware at them. A good place to start with application perf issues is looking at your logical I/O. Configure your -tablerangesize and -indexrangesize database startup parameters as appropriate for your schema (higher than your highest application table and index numbers respectively) and then query the _TableStat and _IndexStat VSTs to see if you have any excessive read totals. If so then you can drill down by user, looking at the _UserTableStat and _UserIndexStat data to determine which users are responsible for the reads. From there, you have a variety of approaches available to find the offending code. Good luck. BTW ProTop is a very good tool for seeing a real-time view of the VST data during application use. P.S. I second the suggestion about hiring a consultant. It's easy to burn a lot of time (and money) trying in vain to solve a problem whose root cause might be much more obvious to someone who does tuning and troubleshooting for a living.

Continue reading...
 
Status
Not open for further replies.
Back
Top