Help: Tuning Oracle parameters

[Former Member]

Hi SAP gurus,

I am reviewing Oracle parameters in our APO production system and would like to tune them up to optimize system performance. I'm a newbie when it comes to tuning Oracle parameters so I would like to get your feedback and suggestions about this.

Currently, these Oracle parameters are set in the APO system:

sga_max_size = 2367656520

db_cache_size = 1140850688

pga_aggregate_target = 1509949440

shared_pool_size = 1140850688

shared_pool_reserved_size = 113246208

sessions = 163

processes = 144

db_writer_processes = 4

large_pool_size = 0

System hardware information as follows:

System Configuration: Sun Microsystems sun4v Sun Fire T200

System clock frequency: 200 MHz

Memory size: 16376 Megabytes

Total of 32 CPU's at 1000 MHz SUNW,UltraSPARC-T1

SAP system information:

SCM 4.10

Operating system = SunOS

Machine type = sun4v

Database system = Oracle9i Enterprise Edition Release 9.2.0.7.0

Me and my colleagues feel that the current parameters are somewhat low for the system's capacity. Thus, we experience performance issues in the system, e.g. high Load average in OS06 (>5).

Having said all of these, please tell me which steps I should take in order to tune-up these parameters for better performance. Any input will be highly appreciated.

I hope you guys can help me out. Thanks in advance.

Cheers,

Kris Caldoza

[stefan_koehler · Accepted solution]

Hello Kris,

this information is not enough.

1) What is your goal ?

- Average response time in dialog

- Tune some specific transactions

2) We have no size of the database and the i/o behavior is also not known.. so we can not say if the paramteres are ok or not.

Please attach 4 screenshots here for a quick overview:

- ST04 Screenshot

- ST04 => Detailed analysis menu => Buffer busy waits

- ST04 => Detailed analysis menu => Wait Events

- ST04 => Detailed analysis menu => File System Requests

Regards

Stefan

[Former Member · Accepted solution]

Thank you for your reply Stefan.

To answer your questions:

1) What is your goal ?

First, I want to find out why our previous Basis admin setup those values in our APO system. My colleagues and I think that they are not optimal values for our system's resources.

Secondly, lately we have been experiencing high "Load Average" (> 6) in OS06. We feel that this has something to do with memory allocations in Oracle and SAP.

2) We have no size of the database and the i/o behavior is also not known.. so we can not say if the paramteres are ok or not.

I cannot provide the "screenshot" here so I just copy-pasted the thing here:

Data buffer

Size kb 1,114,112

Quality % 93.5

Reads 1,343,322,395

Physical reads 87,560,865

writes 1,327,700

Buffer busy waits 75,374,900

Buffer wait time s 6,996

Shared Pool

Size kb 1,114,112

DD-Cache quality % 94.4

SQL Area getratio % 93.2

pinratio % 99.8

reloads/pins % 0.0111

Log buffer

Size kb 1,304

Entries 11,963,073

Allocation retries 400

Alloc fault rate % 0.0

Redo log wait s 4

Log files (in use) 8( 😎

Calls

User calls 47,994,117

commits 306,362

rollbacks 286

Recursive calls 14,980,496

Parses 843,431

User/Recursive calls 3.2

Reads / User calls 28.0

Time statistics

Busy wait time s 220,341

CPU time s 73,430

Time/User call ms 6

Sessions busy % 23.39

CPU usage % 4.23

CPU count 32

Redo logging

Writes 430,305

OS-Blocks written 15,317,067

Latching time s 1

Write time s 704

Mb written 7,152

Table scans & fetches

Short table scans 140,111

Long table scans 8,615

Fetch by rowid 6,067,482,007

by continued row 233,680

Sorts

Memory 416,482

Disk 10

Rows sorted 437,908,652

Statistics of buffer busy waits

Name of operation Class Range (m Number Time (ms) Avg. (ms)

waiting for data block N/A 75,360,858 6,986,260 0

waiting for segment header N/A 1,355 1,800 1

waiting for 1st level bmb N/A 1,645 80 0

waiting for 2nd level bmb N/A 42 50 1

waiting for file header block N/A 3,141 7,390 2

waiting for undo header N/A 1,463 180 0

waiting for undo block N/A 6,413 660 0

Totals 75,374,917 6,996,420 0

Unfortunately, I can't fit the other stats here. I hope these will do for now.

Thank you.

[stefan_koehler · Accepted solution]

Hello Kris,

ok lets start and make some suggestions.

> First, I want to find out why our previous Basis admin setup those values in our APO system

The hardest job in the world

> My colleagues and I think that they are not optimal values for our system's resources.

I think so, too... but to this topic later...

> Secondly, lately we have been experiencing high "Load Average" (> 6) in OS06. We feel that this has something to do with memory allocations in Oracle and SAP.

What do you mean with high load average? CPU / IO?

Normally you can not see the cause of the high load average in your system with OS06 (or ST06). Call ST03n and compare the different weeks and task types, then you will get a detailed information where you need more ressources.

But now to your database values - the problem is that i have no idea how long your database is running (information stands in the header of ST04) .. so i have a problem to interpret the values of buffer wait time, etc.. so some statements are guesses:

- Quality % 93.5 => 93% buffer hit ratio is too bad .. you need more db_cache_size

- Buffer busy waits 75,374,900 => too many buffer busy waits .. (http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref2987)

- DD-Cache quality % 94.4 => not a good ratio... you need to increase shared_pool_size

- Reads / User calls 28.0 => the ratio of reads and user calls is very good.. so it don't look like you have many exepensive sql statements

The other values are hard to interpret, because of the formatting and the missing information... this is just a suggestion which is based on the given information.. to get in detail.. we need much more infos.. but this would be hard to do in a sap forum...

So i would suggest to set the following values:

sga_max_size = 4294967296

db_cache_size = 2469606195

shared_pool_size = 1610612736

Regards

Stefan

[Former Member · Accepted solution]

Thank you so much Stefan for all your inputs.

I really appreciate them.

To answer: "What do you mean with high load average? CPU / IO?"

Everyday in ST06, I notice that "Load Average" values are always high:

Load average 1 min 5.46

5 min 5.80

15 min 6.50

According to OSS notes I've read, these values should not be even higher than 3! And sometimes I would see these values peaking at 9 or 10 even!

Moreover, SAP suggests adding more work processes to prevent this situation and later on experience performance issues.

[Former Member · Accepted solution]

Thank you so much Stefan for all your inputs.

I really appreciate them.

To answer: "What do you mean with high load average? CPU / IO?"

I believe it's for CPU. Everyday in ST06, I notice that "Load Average" values are always high:

Load average 1 min 5.46

5 min 5.80

15 min 6.50

According to OSS notes I've read, these values should not be even higher than 3! And sometimes I would see these values peaking at 9 or 10 even!

Moreover, SAP suggests adding more work processes to prevent this situation and later on experience performance issues.

In ST03N, these are what I found:

(From week of 03/10/2008 - 03/16/2008)

Dialog

ave. resp time = 875.4

ave. cpu time = 474.4

ave. db time = 114.8

Background

ave. resp. time = 5,486.1

ave. cpu time = 2,318.3

ave. db time = 1,265.7

Can any improvement be done in background processing?

Here's another "screenshot" of ST04, which now includes header information:

Database XXX Database summary

DB Server XXXXXapo03

Release 9.2.0.7.0

Day, Time 03/19/2008 07:25:09

Start up at 03/17/2008 18:57:54

Elapsed since start (s) 131,235

Data buffer

Size kb 1,114,112

Quality % 95.4

Reads 1,949,603,993

Physical reads 89,433,298

writes 2,314,207

Buffer busy waits 75,412,393

Buffer wait time s 7,026

Shared Pool

Size kb 1,114,112

DD-Cache quality % 95.5

SQL Area getratio % 94.0

pinratio % 99.8

reloads/pins % 0.0343

Log buffer

Size kb 1,304

Entries 22,965,035

Allocation retries 783

Alloc fault rate % 0.0

Redo log wait s 8

Log files (in use) 8( 😎

Calls

User calls 80,985,452

commits 665,110

rollbacks 459

Recursive calls 25,208,073

Parses 1,482,535

User/Recursive calls 3.2

Reads / User calls 24.1

Time statistics

Busy wait time s 402,658

CPU time s 102,727

Time/User call ms 6

Sessions busy % 17.66

CPU usage % 2.45

CPU count 32

Redo logging

Writes 893,223

OS-Blocks written 29,459,079

Latching time s 1

Write time s 1,430

Mb written 13,743

Table scans & fetches

Short table scans 288,574

Long table scans 16,129

Fetch by rowid 7,096,713,324

by continued row 417,150

Sorts

Memory 767,900

Disk 14

Rows sorted 926,391,825

Thanks again in advance for all your help.

Best regards,

Kris Caldoza

[stefan_koehler · Accepted solution]

Hello Kris,

thanks for the ST03n input.

In this context (Av. response time to DB-time) - you can tune your database ... but you will not benefit as much as i thought before (with only the db performance values).

I will only take a look at the dialog now.. if you wish to tune the background you have to look at these values.

> (From week of 03/10/2008 - 03/16/2008)

>Dialog

> ave. resp time = 875.4

> ave. cpu time = 474.4

> ave. db time = 114.8

You will see that the database time is only round about 13 percent of the complete response time.

The cpu time is the biggest part of the response time with 54 percent.. so you have to tune this "component".

Unfortunately you didn't post the avg. processing time.. it would be intersting to see it in the context to the cpu time... maybe you can post it too. the processing time is calculated out of all the other time values and you can see some trends .. if you have enough cpu power.

Please check if you have enough work processes to handle the requests:

SM51 => select one application server => Goto => Servername => Information => Queue Information

Also you can check which process is taking up the most cpu time (it can also be some shadow processes of oracle, if you run a central instance or maybe some non-SAP processes):

ST06 => Detail analysis menu => Top CPU

To get a snapshot .. the easiest way is to look on OS level.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thank you so much for your recommendations

Thanks really for taking time to look into this, I really appreciate it.

Here are other information for Dialog response time:

Dialog Steps = 35,517

ave. resp. time = 875.4

ave. cpu time = 474.4

ave. db time = 114.8

ave. db procedure call time = 33.8

ave. wait time = 4.8

ave. roll in time = 5.6

ave. roll wait time = 219.8

ave. load and generation time = 26.0

ave. lock time = 0.2

ave. rfc interface time = 6.0

ave. frontend nework time = 98.0

ave. gui time = 237.8

number of roundtrips = 105,822

requested data = 10,254,594

I'm not sure how to calculate average processing time based on the times above. Can you teach me how?

You mentioned that I need to "tune" the cpu component. Does that mean I need to add more cpu's to the system? From SM51, here's the Queue information:

Request type Waiting requ Maximum requ Max.req Request writ Request read

NOWP 0 28 2,000 1,692,542 1,692,542

DIA 0 6 2,000 671,852 671,852

UPD 0 9 2,000 193,155 193,155

ENQ 0 0 2,000 0 0

BTC 0 4 2,000 1,953 1,953

SPO 0 4 2,000 3,375 3,375

UP2 0 1 2,000 64 64

From the values above, I think we have enough work processes our system.

Looking at ST06 - Top CPU, i dont see any non-SAP processes consuming resources at the moment but I will continue to monitor them.

Hope to hear from you again.

Best regards,

Kris Caldoza