How to trace out oracle performance issues

[former_member188883]

Many a times we come across situation where we need to troubleshoot performance problems related to Oracle database. This blog is meant to provide some basis guideline on tracing out performance problems with Oracle database.

1)  Use below SQL query command to confirm the status of Oracle patch set.

   select * from dba_registry_history order by action_time;

  Use the following statement to check whether both CATALOG and CATPROC in the DBA_REGISTRY have the current    patchset status, and are VALID:

  SELECT COMP_ID, VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_ID IN ('CATALOG', 'CATPROC')

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

3)  Perform database fine tuning as per SAP Notes # 1171650 - Automated Oracle DB parameter check  .Execute the script attached to this note to generate the recommendations.

4)  You may also update statistics using OSS Notes # 838725 - Oracle dictionary statistics and system statistics.

5)  In certain situations, the Cost Based Optimizer (CBO) may make unfavorable decisions, although there are no CBO errors

Due to several bugs you cannot use the Bind Value Peeking to optimize such situations either.

Alternatively, implement the script for the automatic postprocessing of critical statistic values using DBMS_STATS functions (Note 724545), which is attached to this note. In addition, it ensures that the changes remain in the system permanently and are not overwritten by BRCONNECT statistical runs.

Import the pre-configured statistics as explained in SAP Notes # 1020260 - Delivery of Oracle statistics (Oracle 10g, 11g)

6) You can use transaction ST03 or ST03N to determine the extent to which accesses to the Oracle database are involved in the overall response time. This portion is determined by using the "Database time" column and should not exceed 40% of the overall response time..

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

😎 Create AWR/ASH reports in HTML form covering the timeframe before/during/after the issue happens.

    Refer to SAP Notes # 1738665 - Creating ASH and AWR reports.

9) 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.

10) 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.

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

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

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

14) For overall performance and throughput reasons NEVER compress all transparent tables. Only compress the largest tables (less than 1000) to achieve good disk space reduction. You may check whether any table has compression enabled.

How to check for oltp compressed tables:

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME    COMPRESS COMPRESS_FOR

----------- ----------- -------------

COEP        ENABLED    OLTP

References:

SAP note 618868 - FAQ: Oracle performance