dbTalk Databases Forums  

Insert slow in DPF environment.

Go Back dbTalk Databases Forums Databases comp.databases.ibm-db2 Insert slow in DPF environment.

comp.databases.ibm-db2 comp.databases.ibm-db2


Discuss Insert slow in DPF environment. in the comp.databases.ibm-db2 forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Anwei Shen
 
Posts: n/a

Default Insert slow in DPF environment. - 07-06-2010 , 01:43 PM





DB2 DPF V9.1.7 on Linux

Have partition table based on PK like this.

CREATE TABLE DOC (
"SOURCE_ID" INTEGER NOT NULL ,
"DRN" BIGINT NOT NULL ,
"DOCTYPE" CHAR(10) NOT NULL ,
"DW_INSERT_TS" TIMESTAMP NOT NULL ,
"DW_UPDATE_TS" TIMESTAMP NOT NULL ,

....
)
DISTRIBUTE BY HASH("DRN", "SOURCE_ID") ;

Create table LOAD_TAB(
DW_LOAD_TS timestamp );

create table test
(
DRN BIGINT NOT NULL,
SOURCE_ID INTEGER NOT NULL,
CHANGE_IND CHAR(1),
....
PRIMARY KEY (DRN, SOURCE_ID)
)
DISTRIBUTE BY HASH (DRN, SOURCE_ID) ;

The following query is to find only the changed records since last
run, it took seconds to 86K records, which is OK

WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION ALL
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT * FROM ALL_CHANGED_DOC

;

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER
(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC

;

But when insert into a partitioned table, it took 5 minutes.

insert into dmtrstg.test (DRN, SOURCE_ID, CHANGE_IND, ANCHORID,
RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION ALL
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT * FROM ALL_CHANGED_DOC

;

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER
(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC

;

Reply With Quote
  #2  
Old   
Anwei Shen
 
Posts: n/a

Default Re: Insert slow in DPF environment. - 07-06-2010 , 01:49 PM





DB Snapshot show Billions of Reads from temp table on all partitions.

Explain from both statement are similar

For the query
==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM",


Statement:

WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS
THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >=L.DW_LOAD_TS
THEN 'I' END CHANGE_IND
FROM DOCHEADER D, LOAD_TAB L ),
UPDATED_DOC_ANCHORID (ANCHORID)AS (
SELECT D.ANCHORID
FROM DOCHEADER D JOIN LOAD_TAB L ON
D.CURR_IND=1 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS AND
D.DW_INSERT_TS < L.DW_LOAD_TS ), UPDATED_DOC_BY_ANCHORID
(DRN, SOURCE_ID)AS (
SELECT D.DRN, D.SOURCE_ID
FROM DOCHEADER D JOIN UPDATED_DOC_ANCHORID U ON
D.ANCHORID =U.ANCHORID ),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID,
CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID)NOT IN
(SELECT DRN, SOURCE_ID
FROM New_Obsolete_Doc)), ALL_CHANGED_DOC(DRN,
SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)AS
(
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN DOCHEADER D ON
U.DRN =D.DRN AND U.SOURCE_ID=
D.SOURCE_ID
UNION ALL
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOCHEADER D ON U.DRN =
D.DRN AND U.SOURCE_ID=D.SOURCE_ID )
SELECT *
FROM ALL_CHANGED_DOC


Section Code Page = 1208

Estimated Cost = 628178.625000
Estimated Cardinality = 196823328.000000

Coordinator Subsection - Main Processing:
Distribute Subsection #2
Quote:
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #6
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #5
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #4
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #7
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #3
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #1
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Access Table Queue ID = q1 #Columns = 6
Return Data to Application
Quote:
#Columns = 6
Subsection #1:
(
Quote:
Access Table Queue ID = q2 #Columns = 8
Nested Loop Join
| Access Table Name = LOAD_TAB ID = 6,149
| | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: DW_LOAD_TS (Ascending)
| | #Columns = 1
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
UNION
Access Table Queue ID = q3 #Columns = 7
Nested Loop Join
| Access Table Name = LOAD_TAB ID = 6,149
| | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: DW_LOAD_TS (Ascending)
| | #Columns = 1
| | #Key Columns = 1
| | | Start Key: Exclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
)
Insert Into Asynchronous Table Queue ID = q1
Quote:
Broadcast to Coordinator Node
Rows Can Overflow to Temporary Table
Subsection #2:
Access Table Name = DOCHEADER ID = 6,5
Quote:
#Columns = 8
Compressed Table
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q2
| | Broadcast to All Nodes of Subsection 1
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q2

Subsection #3:
Access Table Queue ID = q4 #Columns = 5
Hash Join
Quote:
Estimated Build Size: 400000
Estimated Probe Size: 480000
Access Table Queue ID = q7 #Columns = 3
Insert Into Asynchronous Table Queue ID = q3
Broadcast to All Nodes of Subsection 1
Rows Can Overflow to Temporary Table
Subsection #4:
Data Stream 1:
Quote:
Not Piped
Access Table Queue ID = q5 #Columns = 2
Insert Into Temp Table ID = t1
| #Columns = 2
End of Data Stream 1
Access Table Name = DOCHEADER ID = 6,5
Quote:
#Columns = 5
Compressed Table
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
| Access Data Stream 1
| Access Temp Table ID = t1
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
| ALL Subquery
| | Access Data Stream 1
| Insert Into Asynchronous Table Queue ID = q4
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q4

Subsection #5:
Access Table Name = DOCHEADER ID = 6,5
Quote:
Index Scan: Name = DOCHEADER_PK ID = 1
| Regular Index (Not Clustered)
| Index Columns:
| | 1: DRN (Ascending)
| | 2: SOURCE_ID (Ascending)
#Columns = 2
Compressed Table
Volatile Cardinality
#Key Columns = 0
| Start Key: Beginning of Index
| Stop Key: End of Index
Index-Only Access
Index Prefetch: Eligible 23
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Nested Loop Join
Data Stream 2:
| Not Piped
| Access Table Queue ID = q6 #Columns = 0
| Insert Into Temp Table ID = t2
| | #Columns = 0
End of Data Stream 2
Access Temp Table ID = t2
| #Columns = 0
| Single Record
| Relation Scan
| | Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q5
Broadcast to All Nodes of Subsection 4
Rows Can Overflow to Temporary Table
Subsection #6:
Access Table Name = LOAD_TAB ID = 6,149
Quote:
Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| Regular Index (Not Clustered)
| Index Columns:
| | 1: DW_LOAD_TS (Ascending)
#Columns = 0
#Key Columns = 0
| Start Key: Beginning of Index
| Stop Key: End of Index
Index-Only Access
Index Prefetch: None
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Index Predicate(s)
| Insert Into Asynchronous Table Queue ID = q6
| | Broadcast to All Nodes of Subsection 5
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q6

Subsection #7:
Access Table Name = DOCHEADER ID = 6,5
Quote:
Index Scan: Name = DB2P0003.IDX_DOCHEADER2 ID = 2
| Regular Index (Not Clustered)
| Index Columns:
| | 1: CURR_IND (Ascending)
#Columns = 4
Compressed Table
Volatile Cardinality
#Key Columns = 1
| Start Key: Inclusive Value
| | | 1: 1
| Stop Key: Inclusive Value
| | | 1: 1
Data Prefetch: Eligible 891
Index Prefetch: Eligible 891
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q7
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q7

End of section

Reply With Quote
  #3  
Old   
Anwei Shen
 
Posts: n/a

Default Re: Insert slow in DPF environment. - 07-06-2010 , 01:50 PM


From the insert

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM",


Statement:

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER (DRN, SOURCE_ID,
CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS
THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >=L.DW_LOAD_TS
THEN 'I' END CHANGE_IND
FROM DOCHEADER D, LOAD_TAB L ),
UPDATED_DOC_ANCHORID (ANCHORID)AS (
SELECT D.ANCHORID
FROM DOCHEADER D JOIN LOAD_TAB L ON
D.CURR_IND=1 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS AND
D.DW_INSERT_TS < L.DW_LOAD_TS ), UPDATED_DOC_BY_ANCHORID
(DRN, SOURCE_ID)AS (
SELECT D.DRN, D.SOURCE_ID
FROM DOCHEADER D JOIN UPDATED_DOC_ANCHORID U ON
D.ANCHORID =U.ANCHORID ),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID,
CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID)NOT IN
(SELECT DRN, SOURCE_ID
FROM New_Obsolete_Doc)), ALL_CHANGED_DOC(DRN,
SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)AS
(
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN DOCHEADER D ON
U.DRN =D.DRN AND U.SOURCE_ID=
D.SOURCE_ID
UNION
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOCHEADER D ON U.DRN =
D.DRN AND U.SOURCE_ID=D.SOURCE_ID )
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID,
RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC


Section Code Page = 1208

Estimated Cost = 626511.687500
Estimated Cardinality = 14257.333008

Coordinator Subsection - Main Processing:
Distribute Subsection #4
Quote:
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #3
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #6
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #5
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #9
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #8
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #7
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #2
Broadcast to Node List
| Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #1
Broadcast to Node List
| Nodes = 0, 1, 2, 3, 4, 5, 6, 7, 8
Subsection #1:
Access Table Queue ID = q1 #Columns = 6
Insert Into Sorted Temp Table ID = t1
Quote:
#Columns = 6
#Sort Key Columns = 6
| Key 1: (Ascending)
| Key 2: (Ascending)
| Key 3: (Ascending)
| Key 4: (Ascending)
| Key 5: (Ascending)
| Key 6: (Ascending)
Sortheap Allocation Parameters:
| #Rows = 14258.000000
| Row Width = 36
Piped
Duplicate Elimination
Access Temp Table ID = t1
#Columns = 6
Relation Scan
| Prefetch: Eligible
Insert: Table Name = DMTRSTG.DW_EXTRACT_DRIVER ID = 2,59

Subsection #2:
(
Quote:
Access Table Queue ID = q2 #Columns = 6
| Output Sorted and Unique
| | #Key Columns = 3
| | | Key 1: (Ascending)
| | | Key 2: (Ascending)
| | | Key 3: (Ascending)
UNION
Access Table Queue ID = q4 #Columns = 1
Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 352000
| Estimated Probe Size: 304000
| Access Table Queue ID = q6 #Columns = 5
)
Insert Into Asynchronous Table Queue ID = q1
Quote:
Hash to Specific Node
Rows Can Overflow to Temporary Tables
Subsection #3:
Access Table Queue ID = q3 #Columns = 8
Nested Loop Join
Quote:
Access Table Name = LOAD_TAB ID = 6,149
| Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DW_LOAD_TS (Ascending)
| #Columns = 1
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Insert Into Sorted Temp Table ID = t2
#Columns = 6
#Sort Key Columns = 3
| Key 1: (Ascending)
| Key 2: (Ascending)
| Key 3: (Ascending)
Sortheap Allocation Parameters:
| #Rows = 20273928.000000
| Row Width = 48
Piped
Duplicate Elimination
Access Temp Table ID = t2
#Columns = 6
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q2
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q2

Subsection #4:
Access Table Name = DOCHEADER ID = 6,5
Quote:
#Columns = 8
Compressed Table
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q3
| | Broadcast to All Nodes of Subsection 3
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q3

Subsection #5:
Access Table Queue ID = q5 #Columns = 3
Nested Loop Join
Quote:
Access Table Name = LOAD_TAB ID = 6,149
| Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DW_LOAD_TS (Ascending)
| #Columns = 1
| Single Record
| #Key Columns = 1
| | Start Key: Exclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Insert Into Asynchronous Table Queue ID = q4
Hash to Specific Node
Rows Can Overflow to Temporary Tables
Subsection #6:
Access Table Name = DOCHEADER ID = 6,5
Quote:
Index Scan: Name = DB2P0003.IDX_DOCHEADER2 ID = 2
| Regular Index (Not Clustered)
| Index Columns:
| | 1: CURR_IND (Ascending)
#Columns = 4
Compressed Table
Volatile Cardinality
#Key Columns = 1
| Start Key: Inclusive Value
| | | 1: 1
| Stop Key: Inclusive Value
| | | 1: 1
Data Prefetch: Eligible 891
Index Prefetch: Eligible 891
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q5
| | Broadcast to All Nodes of Subsection 5
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q5

Subsection #7:
Data Stream 1:
Quote:
Not Piped
Access Table Queue ID = q7 #Columns = 2
Insert Into Temp Table ID = t3
| #Columns = 2
End of Data Stream 1
Access Table Name = DOCHEADER ID = 6,5
Quote:
#Columns = 5
Compressed Table
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
| Access Data Stream 1
| Access Temp Table ID = t3
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
| ALL Subquery
| | Access Data Stream 1
| Insert Into Asynchronous Table Queue ID = q6
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q6

Subsection #8:
Access Table Name = DOCHEADER ID = 6,5
Quote:
Index Scan: Name = DOCHEADER_PK ID = 1
| Regular Index (Not Clustered)
| Index Columns:
| | 1: DRN (Ascending)
| | 2: SOURCE_ID (Ascending)
#Columns = 2
Compressed Table
Volatile Cardinality
#Key Columns = 0
| Start Key: Beginning of Index
| Stop Key: End of Index
Index-Only Access
Index Prefetch: Eligible 23
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Nested Loop Join
Data Stream 2:
| Not Piped
| Access Table Queue ID = q8 #Columns = 0
| Insert Into Temp Table ID = t4
| | #Columns = 0
End of Data Stream 2
Access Temp Table ID = t4
| #Columns = 0
| Single Record
| Relation Scan
| | Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q7
Broadcast to All Nodes of Subsection 7
Rows Can Overflow to Temporary Table
Subsection #9:
Access Table Name = LOAD_TAB ID = 6,149
Quote:
Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| Regular Index (Not Clustered)
| Index Columns:
| | 1: DW_LOAD_TS (Ascending)
#Columns = 0
#Key Columns = 0
| Start Key: Beginning of Index
| Stop Key: End of Index
Index-Only Access
Index Prefetch: None
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Index Predicate(s)
| Insert Into Asynchronous Table Queue ID = q8
| | Broadcast to All Nodes of Subsection 8
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q8

End of section

Reply With Quote
  #4  
Old   
Anwei Shen
 
Posts: n/a

Default Re: Insert slow in DPF environment. - 07-07-2010 , 01:55 PM


application status shows

Pending remote request

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top