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

上一篇:日志级别的判定


下一篇:STA 108 Fall 2019