SAP ORACLE PERFORMANCE ISSUE TROUBLESHOOTING TIPS

1) Validate current SBP/Patch of the database . Use below SQL query command to check:
   select * from dba_registry_history order by action_time;

2)  Install the Latest SBP or Patches if available for the database release. Newer patches/SBPs contains bug fixes from previous releases.

3)  Run the 'Automated Parameter Check' script from OSS Notes # 1171650 - Automated Oracle DB parameter check  and perform the necessary changes if required.

4)  Verify that the Oracle Dictionary and System statistics are Updated properly.
    Refer OSS Notes # 838725 - Oracle dictionary statistics and system statistics.

5) Import the pre-configured statistics if required.
   Refer to OSS Notes # 1020260 - Delivery of Oracle statistics (Oracle 10g, 11g)

6) If required perform the necessary steps as per OSS Note # 176754 - Problems with CBO and RBO to solve it.

7) Check 'Avg.Proc:Time','Avg CPU Time','Avg DB Time' values in ST03N transaction where the most time was spent.

8) If everything is slow, use ST01 to trace e.g. DB Access (SQL Trace), Table Buffer Trace, etc.

9) Create AWR/ASH reports in HTML form covering the timeframe before/during/after the issue happens.
    Refer to OSS Notes # 1738665 - Creating ASH and AWR reports.

10) Use Transaction ST04 -> Detail Analysis Menu -> File System Requests to check whether the average read time ("Avg(ms)" for "Blk Reads") for individual data files or in total (Total under the column) is too high (>> 10 ms). If so, check whether the problem can be solved by improving the data distribution (for example, reorganization) or whether it is an I/O problem caused by hardware.

11) Use transaction ST06 or OS07 to check whether there are sufficient CPU and memory resources on the database server.
    The CPU IDLE time should usually appear on the hour average at 30% or more. As of Oracle 10g, you can also read important operating system information (CPU number, CPU load, physical memory, paging) from V$OSSTAT. You can take details about the CPU (CPUs, CPU cores, CPU sockets) from DBA_CPU_USAGE_STATISTICS.


12) As described in Note 766349, check which SQL statements are responsible for most Buffer Gets or Disk Reads and whether they can be optimized.

13)  Check regularly whether fragmented indexes exist and reconstruct these indexes. Refer SAP Note 771929.

14)  Refer SAP note 354080 for performance problems due to Oracle bugs and configuration problems.

Reference SAP note 618868 - FAQ: Oracle performance


Original Post By Deepak Kori