SQL Tuning for SAP

Basis Rule for Tuning (ST03N)

• Average CPU time should be < 50 % of the total response time, otherwise the server (ST06/OS07, OS07) or the running programs (SE30) should be analyzed more in detail

• Average wait time should be < 10 % of the total response time or < 50 ms, otherwise the server should be analyzed with transaction ST06 more in detail

• Average load time should be < 10 % of the total response time or < 50 ms, otherwise the R/3 buffers should be analyzed with transaction ST02 more in detail

• Average DB time should be < 40-50 % of the total response time, otherwise the database should be analyzed with transaction ST04, ST06 more in detail

• Average GUI time > 200ms may indicate poor GUI performance which could be caused by bad network connections or user menus downloading too much data

ST04

• database buffer should be > 94 %, If the  database buffer  quality is > 94 % you must check the value for „Reads / User calls“.
•  Shared Pool
•  DD-cache quality is greater than 80%
•  SQL Area pinratio is 95 or higher
•  reloads/pin is 0.04 or lower
•  User/recursive calls are 2 higher
• The ratio of "Busy wait time" to "CPU time"  should be 60:40

  • SELECT
      ROUND((STM1.VALUE - STM2.VALUE) / 1000000) "BUSY WAIT TIME (S)",
      ROUND(STM2.VALUE / 1000000) "CPU TIME (S)",
      ROUND((STM1.VALUE - STM2.VALUE) / STM1.VALUE * 100) || ' : ' ||
        ROUND(STM2.VALUE / STM1.VALUE * 100) RATIO
    FROM V$SYS_TIME_MODEL STM1, V$SYS_TIME_MODEL STM2
    WHERE STM1.STAT_NAME = 'DB time' AND STM2.STAT_NAME = 'DB CPU'

• Time Statistics Time/User call should be < 20
• Sort too high means full table scan 
• table “V$SYSTEM_EVENT“.
•  db file sequential read<=20
•  Buffer busy waits>=40
•  Log file Sync<=20
• • Write Complete Waits should be in top 10
• • Free Buffer Wait should not be in top10
• Overall Expensive SQL  report RSORADLD 

Reference

Note 712624 - High CPU consumption by Oracle

Note 618868 - FAQ: Oracle performance

Note 766349 - FAQ: Oracle SQL optimization

Note 1020260 - Delivery of Oracle statistics

Note 122718 - CBO: Tables with special treatment