SELECTING FROM EXTERNAL TABLE WITH CLOB perform very slow and High Wait On 'Px Deq Credit: Send Blkd ' (Doc ID 1300645.1)

Last updated on MARCH 27, 2014

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.
***Checked for relevance on 22-AUG-2012***

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms