Tuning Oracle Database - SGA Memory

[Former Member]

Good morning Gurus,

I have this environment in my company:

SAP ECC 6

Oracle 10g

Aix 6.1

Our hardware resources:

Memory: 16GB Ram

Processor: 4 x 3.2Ghz

IBM Blade JS12

Recently, we migrated SAP 4.6C (Solaris) to SAP ECC (Aix). In this new version, a lot of users feel that the performance had decreased.

I checked SGA Memory in Oracle, and i noticed that this configuration didn't used all hardware resources.

This parameters was like these:

sga_max_size=712M

pga_aggregate_target=292M

db_cache_size=292M

processes=80

sessions=96

I changed for:

pga_aggregate_target = 4096M

sga_max_size = 4096M

sga_target = 4096M

db_cache_size = 600M

I noticed, after this change the performance was perfect. We reduced the time db execution in 60%, but now it isn't occur.

We changed this parameters on July 4nd, now the performance decrease in 30%.

My question is, after this change, do we need to adjust the parameters in profile?

Thanks very much

Denis

Brazil

[Former Member]

Hi,

Well, the first step is to set all the parameters which are recommended by SAP.

Please follow this post.

Please let us know the discrepancies and how is the system performance after those changes.

Br,

Venky.

[Former Member]

Hi, thanks for your reply.

Ok, this values are like bellow. I compare in SE30 before and after change.

ME2L = Before:178.329ms after 07-04-11 : 58.572 07-19-11: 113.762

I chose this values too, because the parameter sga_max_size in Solaris was 2G.

THX,

Denis

Brazil

[Former Member]

You will need to get an idea of a few Oracle basics, just changing a bunch of parameters can help, but its a guessing game.

I changed for:
pga_aggregate_target = 4096M
sga_max_size = 4096M
sga_target = 4096M
db_cache_size = 600M

Obviously all of these could theoretically influence performance. As already mentioned to check the recommendations.

[830576 Parameter recommendations for Oracle 10g|https://service.sap.com/sap/support/notes/830576]

In your case db_cache_size is most probably the most important value If you have your system configured with dynamic SGA, then you have to check if either db_cache_size or the shared_pool_size are being resized when the performance decreases. My advice would be to set both values to the desired values and remove sga_max_size and sga_target.

Have a look at: [617416 - Oracle9i: Dynamic SGA|https://service.sap.com/sap/support/notes/617416] as far as i know this is still valid for 10g.

Cheers Michael

[lbreddemann]

>

I changed for:
> pga_aggregate_target = 4096M
> sga_max_size = 4096M
> sga_target = 4096M
> db_cache_size = 600M

>

Sorry, but 600 MB Cache for your productive instance?

I've got a 4 your old ipod shuffle that comes with 1GB...

Looks like you have a rather own view on Oracle memory management

regards,

Lars

[stefan_koehler]

Hello Lars,

you got a 4 year old ipod? That's even more impressive than the 1 GB storage )

The specified 600 MB is just the minimum amount of memory for the database cache - sga_target is set to 4 GB.

-> http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams050.htm#REFRN10033

Regards

Stefan

[Former Member]

In your case db_cache_size is most probably the most important value If you have your system configured with dynamic SGA, then you have to check if either db_cache_size or the shared_pool_size are being resized when the performance decreases. My advice would be to set both values to the desired values and remove sga_max_size and sga_target.

Hi,

Thanks for yours replies.

I checked the note 617416 - Oracle9i: Dynamic SGA. You sad me that i need to set both values to the desired values and remove sga_max_size and sga_target. This note says different for dynamic SGA.

Thanks

Denis

Brazil

[Former Member]

Yeah, you are right, but you said first the performance was much better and decreased again later. Thus the dynamic SGA can be responsible for that, so either disable dynamic SGA or try to set db_cache_size to a higher lower limit (like 2gb) for example.

But you want to verify my assumptions first, the note contains a few v$ views to monitor SGA resize operations.

Cheers Michael

[Former Member]

Hello Denis,

I checked the note 617416 - Oracle9i: Dynamic SGA. You sad me that i need to set both values to the desired values and remove sga_max_size and sga_target. This note says different for dynamic SGA.

Note 617416 is pretty old (2006), you should follow note 830576 for 10g and there is no sga_max_size mentioned.

Kind regards,

Gerd

[fidel_vales]

> Note 617416 is pretty old (2006), you should follow note 830576 for 10g and there is no sga_max_size mentioned.

Yes it is old, because there are no significant changes on automatic memory management between 9i and 10g (11g is another history), so it is "valid"

sga_max_size is not mentioned in note 830576 because the memory parameters depend on the available memory. look at the recommendations for the other "memory" parameters:

DB_CACHE_SIZE Size depends on the available memory (Notes 789011, 617416)

LOG_BUFFER 1048576

PGA_AGGREGATE_TARGET OLTP: 20 % of available memory

OLAP: 40 % of available memory

SHARED_POOL_SIZE 400 MB or greater, refer to Note 690241

As you see, there is only one "hard coded" recommendation and one minimum recommendation.

It is not possible to give a recommendation for sga_max_size.

If you do not set it, Oracle automatically calculates it.

If it is set to small, Oracle will adjust it.

What it is important is to set up the DB_CACHE_SIZE and SHARED_POOL_SIZE, they will be the MINUMUM sizes if AMM is

used

[Former Member]

Good morning gurus,

Thanks for your replies, i'm going to test this new values in a copy of Production and note after this changes.

I decided to disable the dynamic SGA.

I noticed that this parameters are calculated this forms, correct me if i'm wrong:

  PARALLEL_MAX_SERVERS=CPU * 10
  PROCESSES= ABAP WP * 2 + J2EE Server Processes (i don't know how to discovery this value, i believe that we don't use this resource) * <max connections> + Parallel_Max_Servers + 40
  SESSIONS = 2 * Processes
  PGA_AGGREGATE_TARGET= 20% of the memory
  DB_CACHE_SIZE= I increased the value 608M to 2GB (I didn't find the details for calculation this parameter)

I'm going to change this values in Production on Monday and on Wednesday I will post the result.

Thanks very much,

Have a good time.

Denis

Brazil

[Former Member]

DB_CACHE_SIZE= I increased the value 608M to 2GB (I didn't find the details for calculation this parameter)

Looks ok, there is no formula for db_cache_size. It is just the remainder of the available memory after all other parameters are set. Basically the free memory on the server can go the db_cache_size.

But one thing, pay attention to the shared_pool_size, this is the other important member of the SGA. Double check that it is specified, it looks like a size from 500mb to 1gb might be appropriate. But there is a formula for it, depending on the number of cpu's.

Cheers Michael

Edit: sigh, spelling errors corrected