性能案例经验总结

 

1. 数据库案例总结
2. 案例一:索引创建不合适导致性能问题
3. 背景

接口getResourceByRoleID在单交易测试时，发现接口响应时间过长，DB 消耗资源比较严重。

• 关键字

db2advis 、执行计划、runstat 、权限接口

• 问题分析
• 首先是找出接口调用的SQL语句，有两个方法
• 通过DB2top 命令 查找
• 通过以下提供的SQL语句找出SQL语句（如果SQL语句特别长，采用此方法）

SELECT substr (appl_name, 1, 15) AS Appl_name,

       elapsed_time_min AS "Elapsed Min.",

       appl_status AS "Status ",

       substr (authid, 1, 10) AS auth_id,

       substr (inbound_comm_address, 1, 15) AS "IP Address",

       substr (stmt_text, 1, 2000) AS "SQL Statement"

  FROM sysibmadm.long_running_sql

ORDER BY 2 DESC;

--------------------------------------------------------------------------------------------------------------------------

找到了如下SQL：

SELECT DISTINCT rtrim (prv.PRVID) AS PRVID,

                prv.URL,

                prv.remark,

                prv.STATUS,

                prv.URI,

                rtrim (prv.PRVCODE) AS PRVCODE,

                prv.PRVTYPE,

                prv.METHODPARAM,

                prv.METHODVALUE,

                prv.OPERATE,

                prv.ISINDEX,

                prv.ISINNET,

                rtrim (appPrv.SYSID) AS BUSSYSID,

                appPrv.MENUNAME,

                appPrv.MENUORDER,

                appPrv.MENUDES,

                rtrim (appPrv.PARENTID) AS PARENTID

  FROM MPRIVILEGE AS prv,

       SYSTEM_PRV AS appPrv,

       MGROUP_PRV AS rolePrv,

       MGROUP AS role

 WHERE     prv.PRVID = appPrv.PRVID

       AND rolePrv.PRVID = appPrv.PRVID

       AND role.sysid = appPrv.sysid

       AND role.roleid = rolePrv.roleid

       AND prv.STATUS = 1

       AND appPrv.SYS_STATUS = 1

       AND role.STATUS = 1

       AND role.roleID = ？

ORDER BY BUSSYSID, appPrv.MENUORDER, PRVID

  WITH UR

1. 分析SQL执行计划，执行计划的Cost 达到了105

1. 在执行计划中10-12 步骤获取结果时，cost 由7.64 直接跳到40，原因可能是索引扫描时，索引筛选的结果集较大，估计索引创建不合理
2. 通过db2advis 工具利用db2advis 给出了两个索引的创建，可以提升60%的性能

Db2advis –d sample –s  “sql_text”

CREATE INDEX ODP.IDX_SYSTEM_PRV_IDX1 ON ODP.SYSTEM_PRV

   (SYSID ASC, SYS_STATUS ASC, PARENTID ASC, MENUDES

   ASC, MENUORDER ASC, MENUNAME ASC, PRVID ASC)

   ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

 CREATE UNIQUE INDEX ODP.UNIX_MPRIVILEGE_IDX1

   ON ODP.MPRIVILEGE (PRVID ASC) INCLUDE (STATUS,

   ISINNET, ISINDEX, OPERATE, METHODVALUE, METHODPARAM,

   PRVTYPE, PRVCODE, URI, REMARK, URL) ALLOW

   REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

COMMIT WORK

      注意使用db2advis 命令时，可能会出现如下错误：

Using user id as default schema name. Use -n option to specify schema

Explain tables not set up properly for schema DB2INST4

The insert into the ADVISE_INSTANCE table has failed.

      解决方法：

无法使用db2advis  ，由于EXPALAIN 和ADVISE 相关表没有创建，在此模式下

$DB2HOME/sqllib/misc/explain.ddl   -Linux下

%DB2HOME\sqllib\misc\explain.ddl   -Windows 下

db2 -tvf EXPlAIN.DDL

1. 重新执行相关表的统计信息分析，方法如下：

单张表进行RUNSTATS 的命令：

db2  runstats on table ODP.SYSTEM_PRV  and indexes all;

db2  runstats on table ODP. MPRIVILEGE and indexes all

这个命令将和收集该表及其所有索引（基本级别）的统计信息 

查看是否执行了RUNSTATS命令

select tbname,nleaf ,nlevels,stats_time from sysibm.sysindexes

全表进行RUNSTATS 的方法

select 'db2 runstats on table ODP.'||rtrim(NAME)||'  and indexes all' from SYSIBM.SYSTABLES  s where s.creator='ODP' and s.name not like 'BAK%'

1. 查看添加索引后的执行计划

发现 cost 有105 下降到了34

• 解决方案

创建以下两个索引

CREATE INDEX ODP.IDX_SYSTEM_PRV_IDX1 ON ODP.SYSTEM_PRV

   (SYSID ASC, SYS_STATUS ASC, PARENTID ASC, MENUDES

   ASC, MENUORDER ASC, MENUNAME ASC, PRVID