'Px Deq Credit: Send Blkd

 

SELECTING FROM EXTERNAL TABLE WITH CLOB perform very slow and High Wait On 'Px Deq Credit: Send Blkd ' [ID 1300645.1]
		修改时间 03-MAR-2011     类型 PROBLEM     状态 MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

-- Problem Statement:
On 10.2.0.4.0 in Production:
When attempting to create a table as CTAS from an external table,
the progress is very slow and there are high waits on 'PX DEQ CREDIT: SEND BLKD ':

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
1. Create a table with data.

2. Create an external table like this:

CREATE TABLE test_terminals
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test
LOCATION ('proba_terms01.dmp', 'proba_terms02.dmp', 'proba_terms03.dmp', 'proba_terms04.dmp',
'proba_terms05.dmp', 'proba_terms06.dmp', 'proba_terms07.dmp', 'proba_terms08.dmp',
'proba_terms09.dmp', 'proba_terms10.dmp', 'proba_terms11.dmp', 'proba_terms12.dmp',
'proba_terms13.dmp', 'proba_terms14.dmp', 'proba_terms15.dmp', 'proba_terms16.dmp',
'proba_terms17.dmp', 'proba_terms18.dmp', 'proba_terms19.dmp', 'proba_terms20.dmp')
)
PARALLEL 20
AS SELECT i_to_id(i) as terminal_id,
msisdn_to_id(msisdn) as subscriber_id,
phonetype,
impstat,
settings,
firstconfig,
usedconfig,
lastmodif
FROM terminals t1
WHERE NOT EXISTS
(SELECT imei
FROM terminals t2
WHERE t2.imei = t1.imei
AND t1.lastmodif < t2.lastmodif);

It will take a long time. A couple of hours.

3. Try to create an internal table from the external table, like this:

CREATE TABLE terminals_tmp
PARTITION BY hash(terminal_id)
partitions 128 nocache
PARALLEL 20
AS SELECT
i_id, subscriber_id, phonetype, impstat,
settings, firstconfig, usedconfig, lastmodif
from test_terminals;

This will never finish.

Cause

INSERT APPEND VERY SLOW WHEN SELECTING FROM EXTERNAL TABLE WITH CLOB in accord with the bug 9399832.

Solution

 
Download the patch 9399832 or the merge where the patch was included and install it as its Readme file suggests.
1) Click on `Patches and Updates'tab
2) Click on 'Patch Search' tab
3) Click on `Search` link.
4) Enter patch number in the `Patch Number` field.---------> 9399832
5) Select your platform.
6) Click on 'Search.'
7) Select the patch for your version of the database.
8) Click on `Download` to begin the download process. Note: Please review the Readme file for instructions on how to install the patch

The bug 9399832 is fixed in 11.2.0.2

References

BUG:8976188 - CREATE TABLE PARTITION BY HASH CTAS WAIT ON 'PX DEQ CREDIT: SEND BLKD '
BUG:9399832 - INSERT APPEND VERY SLOW WHEN SELECTING FROM EXTERNAL TABLE WITH CLOB
NOTE:9399832.8 - Bug 9399832 - Temporary LOB leak in parallel query / DML