大家好,我有一条SQL语句在执行过程之中吃CPU非常严重,我检测发现是因为大量sort引起的。并我先使用runstats更新统计信息并且使用db2advis工具来添加对应的索引。但是发现还是不起作用。还是有大量的排序而且排序也会益处,我的sortheap设置为automaitc,并且AIX机器的内存还有大量的多余。麻烦大家看一下这个SQL语句到底是怎么一个情况如何优化。下面是该SQL语句
Number of executions = 787
Number of compilations = 1
Worst preparation time (ms) = 6
Best preparation time (ms) = 6
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 133804156
Internal rows updated = 0
Rows written = 200671179
Statement sorts = 779
Statement sort overflows = 779
Total sort time = 229421
Buffer pool data logical reads = 67260418
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 144015288
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 26486
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Total execution time (sec.microsec)= 704.447443
Total user cpu time (sec.microsec) = 350.010853
Total system cpu time (sec.microsec)= 2.192628
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Statement text = SELECT WIID,SYSTEM_ID,TEMPLATE_ID,TEMPLATE_VER,APPLY_ID,APPLY_TITLE,STATE,CREATE_TIME,FINISH_TIME,PIID,NODE_ID,NODE_NAME,NODE_TYPE,OWNER,LASTMODIFIED,HANDLE_URL,HANDLER,PWID,PROVINCE_ID,TEMPLATE_ALIAS,MAP_WIID,ORIGINAL_OWNER,GRANT_TYPE FROM mss.UNIFLOW_WORKITEM WHERE SYSTEM_ID=? AND TEMPLATE_ALIAS IN(?) AND (OWNER IN(?) OR HANDLER IN(?)) AND STATE IN(?) ORDER BY CREATE_TIME DESC
还有对应的执行计划
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"DB2INST1"
Statement:
SELECT WIID, SYSTEM_ID, TEMPLATE_ID, TEMPLATE_VER, APPLY_ID,
APPLY_TITLE, STATE, CREATE_TIME, FINISH_TIME, PIID, NODE_ID,
NODE_NAME, NODE_TYPE, OWNER, LASTMODIFIED, HANDLE_URL,
HANDLER, PWID, PROVINCE_ID, MAP_WIID, ORIGINAL_OWNER,
GRANT_TYPE
FROM mss.UNIFLOW_WORKITEM
WHERE SYSTEM_ID='FinancialAssist' AND TEMPLATE_ALIAS='1301-AC' AND
(OWNER IN('puhuiyan' )OR HANDLER IN('puhuiyan' ))AND STATE
IN(2, 1)
ORDER BY CREATE_TIME DESC
Section Code Page = 1208
Estimated Cost = 20.011341
Estimated Cardinality = 0.392774
Access Table Name = MSS.UNIFLOW_WORKITEM ID = -6,-32767
| Index Scan: Name = MSS.TEST4 ID = 20
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: TEMPLATE_ALIAS (Ascending)
| | | 2: SYSTEM_ID (Ascending)
| | | 3: HANDLER (Ascending)
| | | 4: OWNER (Ascending)
| #Columns = 22
| Data-Partitioned Table
| Avoid Locking Committed Data
| All data partitions will be accessed
| Evaluate Predicates Before Locking for Committed Key
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: '1301-AC'
| | | | 2: 'FinancialAssist'
| | Stop Key: Inclusive Value
| | | | 1: '1301-AC'
| | | | 2: 'FinancialAssist'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | #Predicates = 2
| Sargable Predicate(s)
| | #Predicates = 1
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 22
| | | #Sort Key Columns = 1
| | | | Key 1: CREATE_TIME (Descending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1.000000
| | | | Row Width = 512
| | | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 22
| Relation Scan
| | Prefetch: Eligible
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 22
Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
0.392774
RETURN
( 1)
20.0113
|
0.392774
TBSCAN
( 2)
20.0113
|
0.392774
SORT
( 3)
20.011
|
0.392774
FETCH
( 4)
20.0103
/
0.423429 3.6234e+06
IXSCAN Table:
( 5) <65530,32769>
16.8065
|
117328
Index:
<65530,32769,20>
收起