Routine Query Becomes Terribly Slow Over Time with High PX

Asset ID:	1-72-1379750.1
Update Date:	2012-08-22
Keywords:

Solution Type Problem Resolution Sure

Solution 1379750.1 : Routine Query Becomes Terribly Slow Over Time with High PX_MISMATCH.

Applies to:

Exadata Database Machine V2 - Version Not Applicable and later
Oracle Exadata Hardware - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Exadata Database Machine X2-2 Full Rack - Version Not Applicable and later
Exadata Database Machine X2-2 Half Rack - Version Not Applicable and later
Exadata Database Machine X2-2 Hardware - Version Not Applicable and later
Information in this document applies to any platform.

Symptoms

The following symptoms are seen:

Exadata X2-2 half rack system running 11.2.0.1
Two queries running (every 3 to 4 minutes) without problem for about 4 to 6 weeks.
However, recently the same queries, against the same databases, on the same Compute Nodes are now running with high CPU.
They normally take well under 30 seconds to run, but are now taking several minutes minimum.

Cause

After running following script, there was high PX_MISMATCH:

<NOTE 438755.1>, "Formatted V$SQL_SHARED_CURSOR Report by SQLID or Hash Value"

This showed that the SQL was not being cached, but command re-use was generating separate child cursors:

Addr: 00000001A75075F0 Hash_Value: 812190835 SQL_ID arx2rgss6k33m

Versions Summary

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

PX_MISMATCH :11119

Total Versions:11119



Addr: 00000001A75041A8 Hash_Value: 3753499135 SQL_ID dbp7qgbgvmqgz

Versions Summary

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

BIND_MISMATCH :1

LANGUAGE_MISMATCH :2

USER_BIND_PEEK_MISMATCH :1

TOP_LEVEL_RPI_CURSOR :2

PX_MISMATCH :12426

Total Versions:12428

This matches following bug internal:

Bug 11894017 - EM QUERY VERSION COUNT INCREASE ON REPEATED EXECUTION DUE TO PX_MISMATCH

Solution

1. This fix is included in 11.2.0.2

2. One-off-Backport patches on top of several Exadata Bundle Patch levels of 11.2.0.1 are already available.

For example, the Customer was able to apply My Oracle Support <patch 12315189> to correct the problem on his 11.2.0.1 BP9 compute nodes.

References

<BUG:11894017> - EM QUERY VERSION COUNT INCREASE ON REPEATED EXECUTION DUE TO PX_MISMATCH
<NOTE:438755.1> - High SQL Version Counts - Script to determine reason(s)

Attachments

This solution has no attachment