db_block_buffers - can you have too many ?

Hi,
About the performance problem you are talking about ... I would suggest something like this
1. Check what is the size of your database (#of tables dont make it large) in terms of size of your data files and how much of them are really used. You can check this with Oracle Enterprise manager if you have or by querying the data dictionary views. And also find out the #of rows in each of these tables. Especially the large transaction tables which keep growing rather than master tables which are fairly static.   Also check your db_block_size (default is 2k if db is created thru default installation.) If possible create database with 8K db_block_size and import data into new database.

2. Check How these files are distributed across the disks available to you (for Oracle) on your server.

3. How many applications are using the database and how many users of these applications access the
database concurrently.

4. Check if you have created proper indexes for all these tables. and take care of storing Indexes on different disk than your data tables.

5. Run explain plan for the queries that are taking longer and try to optimise these qeries. This could take a few iterations for you to get good results. Most of the time dirty SQL code is the main cause of poor performance.

6. You could use hints (set your optimiser to be cost based) 7. And if this take you any closer to the expected performance go ahead and analyze the tables to take best advantage of cost based optimizer of oracle.

8. And at every stage of changes you do, give some time for observations and then do the next iteration.

9. All thru this exersise use oracles performance monitoring tools or scripts to note the effect of
changes you make.

Wish You all The Luck

Anand

   -**** Posted from Supernews, Discussions Start Here(tm) ****- http://www.supernews.com/ - Host to the the World's Discussions & Usenet Received on Tue Nov 10 1998 - 13:21:21 CST