DB2 UDB 系统SQL性能优化经历

DB2 UDB 系统SQL性能优化经历

l 监控系统发现系统CPU使用情况如下：

Total System I/O Statistics		CPU:	User%	Sys%	Wait%	Idle%	CPU%
Avg tps during an interval:	3	Avg	15.5	1.6	0.5	82.4	17.1
Max tps during an interval:	137	Max	63.0	32.0	17.7	99.3	65.9
Max tps interval time:	8:00:04	Max:Avg	4.1	19.8	34.5	1.2	3.9
Total number of Mbytes read:	258
Total number of Mbytes written:	474
Read/Write Ratio:	0.5

l 分析：

cpu的User%每隔2分钟左右有一次高峰。在2007-3-3的图中可以看到最高峰达到了63%。需要进一步观察是那个进程消耗了cpu.。

l 数据库监控

通过语句时间监控发现如下语句对cpu的消耗比较大，而且出现的频率很高：

696) Statement Event ... Appl Handle: 270 Appl Id: C0A8090B.BBB3.0D6C42234946 Appl Seq number: 0930 Record is the result of a flush: FALSE ------------------------------------------- Type : Dynamic Operation: Close Section : 5 Creator : NULLID Package : SYSSH200 Consistency Token : SYSLVL01 Package Version ID : Cursor : SQL_CURSH200C5 Cursor was blocking: TRUE Text : select ID from TBL_TASK_EXECUTE_INFO where TASK_ID=? and SUB_TASK_ID=? and START_TIME=? ------------------------------------------- Start Time: 03-13-2007 10:04:45.137947 Stop Time: 03-13-2007 10:04:49.703124 Exec Time: 4.565177 seconds Number of Agents created: 1 User CPU: 3.030000 seconds System CPU: 0.010000 seconds Fetch Count: 1 Sorts: 1 Total sort time: 1155 Sort overflows: 1 Rows read: 486610 Rows written: 243305 Internal rows deleted: 0 Internal rows updated: 0 Internal rows inserted: 0 Bufferpool data logical reads: 32158 Bufferpool data physical reads: 0 Bufferpool temporary data logical reads: 1921 Bufferpool temporary data physical reads: 0 Bufferpool index logical reads: 244085 Bufferpool index physical reads: 0 Bufferpool temporary index logical reads: 0 Bufferpool temporary index physical reads: 0 SQLCA: sqlcode: 100 sqlstate: 02000

l 分析结果

目前的索引使用效率不理想，导致语句的执行成本很高：

例如：Estimated Cost = 10263.299805

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 ******************** PACKAGE *************************************** Package Name = "DB2INST2"."DYNEXPLN" Version = "" Prep Date = 2007/03/13 Prep Time = 11:00:38 Bind Timestamp = 2007-03-13-11.00.38.122323 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2INST2" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: DECLARE C1 CURSOR FOR select id from ccweb.tbl_task_execute_info where task_id=5 and sub_task_id=6 and start_time= '2005-07-15-22.50.25.906000' Section Code Page = 1386 Estimated Cost = 10263.299805 Estimated Cardinality = 0.012346 Index ANDing | Optimizer Estimate of Set Size: 12604 | Index ANDing Bitmap Build Using Row IDs | | Optimizer Estimate of Set Size: 12604 | | Access Table Name = CCWEB.TBL_TASK_EXECUTE_INFO ID = 2,2311 | | | Index Scan: Name = CCWEB.IND_017_2 ID = 3 | | | | Regular Index (Not Clustered) | | | | Index Columns: | | | | | 1: SUB_TASK_ID (Ascending) | | | #Columns = 0 | | | #Key Columns = 1 | | | | Start Key: Inclusive Value | | | | | | 1: 6 | | | | Stop Key: Inclusive Value | | | | | | 1: 6 | | | Index-Only Access | | | Index Prefetch: Eligible 177 | | | Isolation Level: Uncommitted Read | | | Lock Intents | | | | Table: Intent None | | | | Row : None | Index ANDing Bitmap Probe Using Row IDs | | Optimizer Estimate of Set Size: 12604 | | Access Table Name = CCWEB.TBL_TASK_EXECUTE_INFO ID = 2,2311 | | | Index Scan: Name = CCWEB.IND_017 ID = 2 | | | | Regular Index (Not Clustered) | | | | Index Columns: | | | | | 1: TASK_ID (Ascending) | | | #Columns = 0 | | | #Key Columns = 1 | | | | Start Key: Inclusive Value | | | | | | 1: 5 | | | | Stop Key: Inclusive Value | | | | | | 1: 5 | | | Index-Only Access | | | Index Prefetch: Eligible 177 | | | Isolation Level: Uncommitted Read | | | Lock Intents | | | | Table: Intent None | | | | Row : None Insert Into Sorted Temp Table ID = t1 | #Columns = 1 | #Sort Key Columns = 1 | | Key 1: (Ascending) | Sortheap Allocation Parameters: | | #Rows = 12605 | | Row Width = 12 | Piped | Duplicate Elimination List Prefetch Preparation | Access Table Name = CCWEB.TBL_TASK_EXECUTE_INFO ID = 2,2311 | | #Columns = 1 | | Relation Scan | | | Prefetch: 2113 Pages | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Sargable Predicate(s) | | | #Predicates = 3 | | | Return Data to Application | | | | #Columns = 1 Return Data Completion End of section Optimizer Plan: RETURN ( 1) | * * | RIDSCN ( 3) | SORT ( 4) | IXAND ( 5) /---------/ -------- IXSCAN IXSCAN ( 6) ( 7) /--/ /---/ Index: Table: Index: Table: CCWEB CCWEB CCWEB CCWEB IND_017_2 TBL_TASK_EXECUTE_INFO IND_017 TBL_TASK_EXECUTE_INFO

l 优化建议

目前的索引如下：

索引名	列名	模式名	表名	描述
SQL050715224522130	+ID	SYSIBM	Tbl_task_execute_info	主索引
IND_017	+TASK_ID	CCWEB	Tbl_task_execute_info	重复索引
IND_017_2	+SUB_TASK_ID	CCWEB	Tbl_task_execute_info	重复索引

建议：

l 创建如下索引

索引名	列名	模式名	表名
Ind_017_03	+task_id+sub_task_id+start_time	ccweb	Tbl_task_execute_info

语句如下：

create index ccweb.ind_017_03 on ccweb.tbl_task_execute_info(task_id,sub_task_id,start_time)

l 删除如下索引：

索引名	列名	模式名	表名	描述
IND_017	+task_id	ccweb	Tbl_task_execute_info	需要应用人员确定

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67233/viewspace-904988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67233/viewspace-904988/