354080 - Note collection for Oracle performance problems

Symptom

The Oracle database performance is poor.

Other Terms

Performance, runtime, TIME_OUT, timeout

Reason and Prerequisites

There are several possible causes of poor database performance. Numerous notes are available for troubleshooting Oracle performance and they are described below:

• This note

           This note lists performance problems that you can solve by changing the database configuration or by importing a patch.

• Note 618868: FAQ Oracle Performance

           This note describes general checks that you can perform in the event of poor performance and refers to other key notes.

• Note 521264: Hang situations

           This note deals with situations in which the database hangs and with performance problems that occur during the connect and when you start and stop Oracle.

• Note 184905: Collective note Performance BW 2.0
  Note 567745: Composite note BW 3.x performance: DB-specific settings

           These notes deal with BW-specific problems with the database.

Below you will find an overview of performance problems that you can solve by changing the Oracle configuration or by importing a patch.

Solution

1. Release-independent performance problems:

a) OLTP: Note 164925: Storage parameter of tablespace PSAPTEMP
BW: Note 359835: Design of the temporary tablespace in the BW System

                       Symptoms: Long runtime during index creation, many space transaction enqueues, ORA-01575
                       Explanation: When you use a dictionary-administered temporary tablespace with extent sizes that are too low, or with a TEMPORARY type, the SMON process cannot clean the extents quickly enough. You must therefore define the extents with a sufficient size, and set the type to PERMANENT. Alternatively, use a locally administered temporary tablespace.

b) Note 128221: Increased memory consumption with Oracle >8.0.X

                       Symptoms: Oracle has high memory consumption, increased swapping, poor database performance, ORA-04030
                       Explanation: When using the CBO, the shadow processes sometimes need a lot of local memory that is no longer released. To avoid this, set Event 10191.

c) Note 198752: TCP delay problem under Oracle 8.1.x

                       Symptoms: TCP-based database accesses take unnecessarily long.
                       Explanation: By default, data packets are only sent by TCP once they have reached a certain size, or after a certain period of time has passed. To ensure immediate transfer, you must set the parameter TCP_NODELAY appropriately.

d) Oracle >= 8.1: Note 871455: DBA_SEGMENTS performance
BW, Note 519448: Accessing USER_INDEXES, USER_IND_COLUMNS
BW, Note 519407: Accessing USER_IND_PARTITIONS
Note 558197: Accessing USER_IND_COLUMNS during upgrade

                       Symptoms: Long-running accesses to Oracle DDIC objects and external tools like SAPDBA or BR*Tools hang for a long time at certain points, and DB02 refreshes take unusually long to complete. Deactivating BW aggregates takes too long.
                       Explanation: These problems are caused by Oracle bugs or inefficient accesses to Oracle DDIC objects. Apply the relevant bug fixes and scripts from the notes to ensure better access.

e) Note 563359: Poor performance on LOBs

                       Symptoms: Access to LOB information takes longer than access to comparable RAW information.
                       Explanation: This poor performance is caused by the fact that LOB data is not cached by default. The script from the note also activates caching for LOB data.

f) Note 505246: ORA-04031 and ORA-00600 [12333]
Note 556764: Upgrade hangs in phase ACT_*

                       Symptoms: Database error, poor performance or database hangs because of RBO accesses.
                       Explanation: Set the Oracle parameter shared_pool_size to at least 400MB. If you do not, fragmentation effects may cause the problems described above.

g) BW: Note 558746: Better Oracle Data Dictionary BW Performance

                       Symptoms: If there are no statistics: Poor BW performance; if statistics were created: Poor performance is possible during upgrades or monitoring statements.
                       Explanation: In the BW environment, statistics should be created in the Oracle DDIC to improve BW performance. If performance is poor when accessing the Oracle DDIC in another area with these statistics present, the DDIC statistics can be deleted as a test.

h) Note 596420: System standstill during deadlock (ORA-60)

                       Symptoms: Database hangs during a deadlock, "latch free waits" on "enqueue hash chains".
                       Explanation: Oracle locks central database resources when the deadlock trace file is written. This causes a serialization of all other processes using those resources. You can solve the problem by using a patch, an event, or by reducing the trace file size.

2. Release-dependent performance problems:

a) Oracle 8.1.7 (< patch set 3): Note 449136: Cache buffer chains latches
Oracle 8.1.7 (< patch set 4): Note 488583: Cache buffer chains latches

                       Symptoms: The system sometimes performs poorly and hangs, large-scale occurrence of the "latch free" wait event on the "cache buffer chains" latch.
                       Explanation: These are Oracle bugs that you can fix by implementing patches.

b) BW, Oracle 8.1.7.4: Note 598552: BW Queries

                       Symptoms: Execution plan shows sort-merge-joins instead of hash-joins.
                       Explanation: A patch with errors was delivered. To fix the problem, implement the corrected version of the patch.

c) Oracle 8.1.7, 9.2.0 (< patch set 2): Note 520568: CBO: Long runtimes for large IN lists

                       Symptoms: SQL statements with large IN lists perform poorly.
                       Explanation: This is an Oracle bug that you can fix by implementing a patch.

d) Oracle 9i: Note 626615: Error because of _B_TREE_BITMAP_PLANS

                       Symptoms: Poor performance of certain SQL statements, unusual execution plans, ORA-00600 [20022]
                       Explanation: Set the underscore parameter _B_TREE_BITMAP_PLANS to FALSE to avoid these problems

e) Oracle 9.2.0.2/9.2.0.3: Note 649876: Library Cache Pins

                       Symptoms: Wait event "library cache pin" causes wait situations
                       Explanation: This is an Oracle bug that you can remove by implementing the fix from Note 649876.

f) Oracle 9.2: Note 626172: Performance problems with outer joins

                       Symptoms: Performance problems with SQL statements containing outer joins (for example, "(+)" in the SQL text), "null event" as accompanying wait event, high CPU usage by the session
                       Explanation: Set the underscore parameter _PUSH_JOIN_PREDICATE to FALSE to avoid this problem.

g) Oracle 9.2.0: Notes 632336, 684545: DBMS_STATS

                       Symptoms: Statistic creation performs poorly with DBMS_STATS
                       Explanation: These are Oracle bugs that you can fix by implementing patches or bug fixes.

h) Oracle 8.1.7: Note 601668: Merge fix for Oracle 8.1.7.4
Oracle <= 9.2.0.3: Note 610445: Merge fix for 9.2.0.3
Oracle 9.2.0.4: Note 695080: Merge fix for 9.2.0.4
Oracle 9.2.0.5: Note 755629: Merge fix for 9.2.0.5
Oracle 9.2.0.6: Note 834100: Merge fix for 9.2.0.6
Oracle 9.2.0.7: Note 896903: Merge fix for 9.2.0.7
Oracle 9.2.0.8: Note 992261: Merge fix for 9.2.0.8
Oracle 10.2.0.2: Note 981875: Merge fix for 10.2.0.2
Note 1002062: Additional merge fix for 10.2.0.2

                       Symptoms: Wrong resulting quantities, Oracle error messages, poor performance
                       Explanation: The merge fixes correct a large number of Oracle bugs, which sometimes also manifest themselves as performance problems.

i) Oracle 9i: Note 875477: BEGIN BACKUP runtimes

                       Symptoms: Poor performance during TRUNCATE TABLE with large buffer pool

j) Oracle 9i: Note 758989: Poor performance with TRUNCATEs

                       Symptoms: Poor performance of TRUNCATE operations

k) Oracle >= 9i: Note 755342: Time-consuming accesses with Bind Value Peeking

                       Symptoms: Time-consuming SQL statements and incomprehensible CBO decisions

l) Oracle 10g: Time-consuming DDIC and X$ accesses

                       Symptoms: Monitoring transactions (such as access to Shared Cursor Cache) take a long time, INSERT operations perform poorly
                       Explanation: To ensure optimum access to the Oracle DDIC, you must implement the DDIC and fixed object statistics from Note 838725.

3. Performance problems in Oracle releases that are no longer supported:

a) Oracle 8.0: Note 170989: Poor view performance

                       Symptoms: Long runtimes when accessing Views, ORA-01652, Full Table Scans.
                       Explanation: The CBO makes incorrect decisions because of Oracle bugs or invalid parameterization. Set the parameters correctly and implement the relevant patches to correct the problem.

b) Oracle 8.1.6, BW: Note 336402: Long parsing times with 8.1.6

                       Symptoms: Performance problems
                       Explanation: This is an Oracle bug. You can avoid it by setting optimizer_max_permutations to a value less than 80000.