Skip to Content
avatar image
Former Member

Help: Tuning Oracle parameters

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

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.


Kris Caldoza

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 18, 2008 at 07:52 AM

    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



    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Stefan Koehler

      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