- 论坛徽章:
- 0
|
现象描述：
talbe a:TP_USER_INFO_MON_12 4200万条数据 在user_id 字段上有索引
talbe b:KPIM_MID_JF_SCORES_TEMP 670万条数据 在user_id 字段上有索引
INSERT INTO FWAREA.KPIM_MID_JF_SCORES
( USER_ID ,PRODUCT_ID,EPARCHY_CODE,NET_TYPE_CODE,GROUP_BRAND_CODE, SCORE_VALUE , SCORE_SUM )
SELECT A.USER_ID,PRODUCT_ID,EPARCHY_CODE,NET_TYPE_CODE,GROUP_BRAND_CODE,SCORE_VALUE,SCORE_SUM
FROM DWAREA.TP_USER_INFO_MON_12 A , FWAREA.KPIM_MID_JF_SCORES_TEMP B
WHERE A.USER_ID =B.USER_ID
AND A.REMOVE_TAG ='0'
AND A.NET_TYPE_CODE IN ('1','2')；
这个SQL要跑5个小时，在同一主机上插入600万条数据大约5分钟，但是这个数据量相当的查询所需要的时间却是5个多小时左右，
现在怀疑是数据库IO有问题，或者是两表连接时用的NESTED JOIN有问题，请大哥们帮看看怎么优化，谢谢~下面是表信息和执行计划
CREATE TABLE TP_USER_INFO_MON_12
(TIMEST VARCHAR(6) NOT NULL,
USER_ID DECIMAL(16, 0) NOT NULL,
SERIAL_NUMBER VARCHAR(40),
GROUP_BRAND_CODE VARCHAR(4),
BRAND_CODE VARCHAR(4),
NET_TYPE_CODE VARCHAR(2),
REMOVE_TAG VARCHAR(1)
....
)
DATA CAPTURE NONE
IN TBS_MID_USER
INDEX IN TBS_IDX_MID
PARTITIONING KEY
(USER_ID
) USING HASHING
NOT LOGGED INITIALLY;
ALTER TABLE talbe_a
ADD CONSTRAINT P_KEY_1 PRIMARY KEY
(USER_ID
);
CREATE TABLE KPIM_MID_JF_SCORES_TEMP
(TIMEST VARCHAR( ,
USER_ID DECIMAL(16, 0),
SCORE_VALUE DECIMAL(20, 4),
SCORE_SUM DECIMAL(20, 4)
.....
)
DATA CAPTURE NONE
IN TBS_MID_MIS
INDEX IN TBS_IDX_MID
PARTITIONING KEY
(TIMEST,
USER_ID
) USING HASHING
在执行过程中锁情况：
Application handle = 83
Application ID = O2110067.DFD5.026192084509
Sequence number = 0004
Application name = db2bp
CONNECT Authorization ID = SDINST
Application status = Pending remote request
Status change time = Not Collected
Application code page = 1386
Locks held = 17508
Total wait time (ms) = Not Collected
List Of Locks
Database partition = 0
Lock Name = 0x00140144036EFF350000000052
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 57591093
Object Type = Row
Tablespace Name = KPIM_MID_JF_SCORES_TEMP
Table Schema =
Table Name = talbe_B
Mode = X
Database partition = 0
Lock Name = 0x00140144036EFF340000000052
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 57591092
Object Type = Row
Tablespace Name = TBS_MID_MIS
Table Schema = FWAREA
Table Name = KPIM_MID_JF_SCORES_TEMP
Mode = X
注：以上相同的锁有1万7千多了
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== 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", "SDINST"
SQL Statement:
INSERT INTO FWAREA.KPIM_MID_JF_SCORES (USER_ID , PRODUCT_ID,
EPARCHY_CODE, NET_TYPE_CODE, GROUP_BRAND_CODE, SCORE_VALUE ,
SCORE_SUM )
SELECT A.USER_ID, PRODUCT_ID, EPARCHY_CODE, NET_TYPE_CODE,
GROUP_BRAND_CODE, SCORE_VALUE, SCORE_SUM
FROM DWAREA.TP_USER_INFO_MON_12 A ,
FWAREA.KPIM_MID_JF_SCORES_TEMP B
WHERE A.USER_ID =B.USER_ID AND A.REMOVE_TAG ='0'AND
A.NET_TYPE_CODE IN ('1', '2')
Section Code Page = 1386
Estimated Cost = 27256.781250
Estimated Cardinality = 9860.469727
Coordinator Subsection - Main Processing:
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
| | 10, 11, 12, 13, 14, 15
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
| | 10, 11, 12, 13, 14, 15
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
| | 10, 11, 12, 13, 14, 15
Subsection #1:
Access Table Queue ID = q1 #Columns = 8
Insert: Table Name = FWAREA.KPIM_MID_JF_SCORES ID = 20,328
Subsection #2:
Access Table Queue ID = q2 #Columns = 3
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Nested Loop Join
| Access Table Name = DWAREA.TP_USER_INFO_MON_12 ID = 24,149
| | Index Scan: Name = SYSIBM.SQL070813191915840 ID = 2
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: USER_ID (Ascending)
| | #Columns = 6
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Share
| | | Row : None
| | Sargable Predicate(s)
| | | #Predicates = 2
Insert Into Asynchronous Table Queue ID = q1
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables
Subsection #3:
Access Table Name = FWAREA.KPIM_MID_JF_SCORES_TEMP ID = 20,327
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 3
| | | #Sort Key Columns = 1
| | | | Key 1: USER_ID (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 419070
| | | | Row Width = 40
| | | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 3
| 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
End of section
Optimizer Plan:
INSERT
( 2)
/--/ \
DTQ Table:
( 3) FWAREA
| KPIM_MID_JF_SCORES
NLJOIN
( 4)
/------/ \------\
MDTQ FETCH
( 5) ( 9)
| /------/ \
TBSCAN IXSCAN Table:
( 6) ( 9) DWAREA
| | TP_USER_INFO_MON_12
SORT Index:
( 7) SYSIBM
| SQL070813191915840
TBSCAN
(
|
Table:
FWAREA
KPIM_MID_JF_SCORES_TEMP |
-
1.JPG
(23.69 KB, 下载次数: 61)
|
免费注册
发表于 2008-01-22 20:26
