Increasing the data buffer size

[Former Member]

Hi Gurus ,

Now a days our SAP 4.7 server runnig very slow some times , As per the log i need to increase the data buffer size , So kindly tell me the steps and commands step by step for do this , Because this is our production server , Kindly advice me . and also tell me how to run catspace_sap.sql for update statistics .

Regards

Selvan

[Former Member · Accepted solution]

Hi,

You can increase your oracle memory by this following method,

Rules for Adjusting the Oracle Data Buffer Size

The following STATSPACK report alerts the DBA when the data buffer hit ratio falls below the preset threshold. It is very useful for pinpointing those times when decision support-type queries are being run, since a large number of large-table, full-table scans may make the data buffer hit ratio drop. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools.

Remember, the KEEP pool should always have enough data blocks to cache all table rows, while the RECYCLE pool should get a very low buffer hit ratio, since it seldom re-reads data blocks. If the data buffer hit ratio is less than 90 percent, you may want to increase db_cache_size (db_block_buffers in Oracle8i and earlier).

***********************************************************

When the data buffer hit ratio falls below 90%, you

should consider adding to the db_cache_size parameter

***********************************************************

yr. mo dy Hr. Name bhr

-

---

-

---

-

---

2001-01-27 09 DEFAULT 45

2001-01-28 09 RECYCLE 41

2001-01-29 10 DEFAULT 36

2001-01-30 09 DEFAULT 28

2001-02-02 10 DEFAULT 83

2001-02-02 09 RECYCLE 81

2001-02-03 10 DEFAULT 69

2001-02-03 09 DEFAULT 69

Here, you will note those times when you might want to dynamically increase the value of the db_cache_size parameter. In the case of the preceding output, you could increase the db_cache_size each day between 8:00 A.M. and 10:00 A.M., stealing RAM memory from pga_aggregate_target.

The single most important new feature of Oracle9i is the ability to dynamically modify almost all of the Oracle parameters. This gives the Oracle professional the ability to dynamically reconfigure the Oracle instance while it is running, whether in reaction to a current performance problem or in anticipation of an impending performance problem.

Because everything within the SGA can now be modified dynamically, it is critical to the Oracle professional to understand how to monitor the Oracle database to learn to recognize trends and patterns with the system and proactively reconfigure the database in anticipation of regularly scheduled resource needs.

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress. It is only $9.95 and all scripts in this tips can be immediately downloaded.

Thanks,

Sudip...

[Former Member · Accepted solution]

Hi,

If you don't want to read any long text for increasing db_block_buffer then try this

Just open the initSID.ora file from $ORACLE_HOME/database folder and do the following changes-

Increase the parameter db_cache_size to a higher value and restart the Oracle.

before doing this just check your SGA size, eg, SQL> Show SGA

and after doing the changes again check your SGA.

thanks,

Sudip...

[markus_doehr2]

> Now a days our SAP 4.7 server runnig very slow some times , As per the log i need to increase the data buffer size ,

Are you talking about Oracle paramters?

> So kindly tell me the steps and commands step by step for do this , Because this is our production server , Kindly advice me . and also tell me how to run catspace_sap.sql for update statistics .

- use "brtools" to change parameters

- read --> Performance (bottom part of the page)

Markus