Px Query caused Latch Free Waits

Px Query Caused Latch Free Waits [ID 1306688.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.5.0 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

=== ODM Issue Clarification ===
The System has been experiencing slow performance, and the consistent highest wait event from the AWR report is "Latch Free".

Example:

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~
wait    Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
latch free 4,686 74,706 15942 43.0
db file sequential read 416,377 43,357 104 25.0
CPU time 12,768 7.4
enq: CF - contention 3,900 8,775 2250 5.1
CSS operation: query 1,320 6,671 5054 3.8

Changes

The Database was upgraded to 10.2.0.5(After installing 10.2.0.5 patchset).

Cause

Several things were tested:
1) The Buffer Chains were tested for Hot Blocks. This Note helped to determine if such a Block existed.

How To Identify a Hot Block Within The Database Buffer Cache. (Doc ID 163424.1)

Some Hot Blocks were found for this database.

2) A statement running in parallel was found to be executing very often. There was a high "PX Deq Credit: send blkd" Event.

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level (Doc ID 738464.1)

To validate if this was a PX specific area for investigation the tests were performed in serial and the database experienced the same Latch issue. So Parallel execution could be ruled out.

3) It was found that one of the diskgroups was much more heavily used than others. In fact a specific group had 10 times the reads and 30 times the writes in comparison to other groups.

This Disk turned out to be a solid state device that  is supposed to take the brunt of temp updates due to its higher performance.

4) The CSS Audit Level was researched in another Service Request. (SR # 3-3215371961)

The reason here was that a lot of CSS logs were generated once the upgrade to 10.2.0.5 was complete. These logs were filled very quickly when SQL Statements were executed on the Database. The logs were determined by ASM support from the supplied RDA report. As the Error being logged is required by the HP-UX port, you can only reduce the detail level so the logging here is not impacting the Database.

ASM 10.2: Excessive Trace Messages In ocssd.log After Apply Patchset 10.2.0.5 (Doc ID 1238913.1)

Solution

It has been determined that the Latch Free is just a symptom for a HP-UX specific issue described in an Internal BUG.


It turned out the Audit Level needed to be reduced as a workaround until a Patch is available. The issue is specific to HP-UX and ASM usage.