DB2中如何查看执行计划

最新推荐文章于 2024-03-22 16:05:02 发布

匿_名_用_户

最新推荐文章于 2024-03-22 16:05:02 发布

阅读量2.9w

点赞数 5

分类专栏： DB2 文章标签： DB2 sql 执行计划

本文链接：https://blog.csdn.net/qingsong3333/article/details/51025860

版权

DB2 专栏收录该内容

181 篇文章 8 订阅

订阅专栏

说明：

本文讲述了DB2中查看执行计划的方法，包括动态SQL的执行计划，存储过程、包的执行计划，以及如何查看package cache中的执行计划

测试环境：

DB2 10.5fp8/Linux

前提条件:

查看执行计划需要先创建explain表：
$ db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL

详解：

1.查看普通SQL语句的执行计划

例如下面的例子是查看"SELECT NAME FROM T2 WHERE ID > 15" 的执行计划

inst105@db2a:~$ db2 explain plan for "SELECT NAME FROM T2 WHERE ID > 15" 
DB20000I  The SQL command completed successfully.

inst105@db2a:~$ db2exfmt -d sample -o db2exfmt.out
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==> 
Enter up to 128 character source name (SOURCE_NAME, Default %%) ==> 
Enter source schema (SOURCE_SCHEMA, Default %%) ==> 
Enter section number (0 for all, Default 0) ==> 
Output is in db2exfmt.out.
Executing Connect Reset -- Connect Reset was Successful.

inst105@db2a:~$ more db2exfmt.out 
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       10.05.8
FORMATTED ON DB:   SAMPLE
SOURCE_NAME:       SQLC2K26
SOURCE_SCHEMA:     NULLID  
SOURCE_VERSION:      
EXPLAIN_TIME:      2017-06-30-09.09.31.533874
EXPLAIN_REQUESTER: INST105 

Database Context:
----------------
        Parallelism:          None
        CPU Speed:            1.298947e-07
        Comm Speed:           100
        Buffer Pool size:     1000
        Sort Heap size:       256
        Database Heap size:   1200
        Lock List size:       4096
        Maximum Lock List:    10
        Average Applications: 1
        Locks Available:      13107

Package Context:
---------------
        SQL Type:           Dynamic
        Optimization Level: 5
        Blocking:           Block All Cursors
        Isolation Level:    Cursor Stability



---------------- STATEMENT 1  SECTION 203 ----------------
        QUERYNO:       1
        QUERYTAG:                          
        Statement Type:   Select
        Updatable:        No
        Deletable:        No
        Query Degree:     1

Original Statement:
------------------
SELECT 
  NAME 
FROM 
  T2 
WHERE 
  ID > 15


Optimized Statement:
-------------------
SELECT 
  Q1.NAME AS "NAME" 
FROM 
  INST105.T2 AS Q1 
WHERE 
  (15 < Q1.ID)

Access Plan:
-----------
        Total Cost:             6.81246
        Query Degree:           1

             Rows 
            RETURN
            (   1)
             Cost 
              I/O 
              |
               1 
            FETCH 
            (   2)
            6.81246 
               1 
         /----+----\
        1             3 
     IXSCAN    TABLE: INST105 
     (   3)          T2
   0.00553156        Q1
        0 
       |
        3 
 INDEX: INST105 
      IDX2
       Q1

可以看到，这条动态SQL语句中的执行计划用到了索引扫描.

2. 存储过程/package执行计划

存储过程执行计划是放在包里面的，因此，要首先找到对应的包名，然后使用db2expln查看包的执行计划：

存储过程定义如下：

inst105@db2a:~$ cat createproce.txt 
CREATE OR REPLACE PROCEDURE PROCEDURE01 ()
        DYNAMIC RESULT SETS 2
P1: BEGIN
        -- Declare cursor
        DECLARE cursor1 CURSOR WITH RETURN for
        SELECT ID FROM T1;

        DECLARE cursor2 CURSOR WITH RETURN for
        SELECT NAME FROM T2 WHERE ID > 15;

        -- Cursor left open for client application
        OPEN cursor1;
        OPEN cursor2;
END P1
@

找到存储过程对应的包：

inst105@db2a:~$ db2 "select trim (substr (r.routineschema, 1, 10)) as routineschema,trim (substr (r.routinename, 1, 30)) as routinename, r.valid, trim (substr (p.pkgschema, 1, 15)) as pkgschema, trim (substr (p.pkgname, 1, 15)) as pkgname, p.valid from syscat.routines r, syscat.packages p, syscat.procedures a,syscat.routinedep b where b.specificname=r.specificname and r.specificname=a.specificname and r.routinetype = 'P' and b.bname=p.pkgname and a.procname='PROCEDURE01' order by p.create_time desc fetch first 5  rows only"   

ROUTINESCHEMA ROUTINENAME                    VALID PKGSCHEMA       PKGNAME         VALID
------------- ------------------------------ ----- --------------- --------------- -----
INST105       PROCEDURE01                    Y     INST105         P1884571328     Y    

  1 record(s) selected.

查看包的执行计划，其中INST105是模式名，因为存储过程中有两条SQL，所以结果里有两个section：

inst105@db2a:~$ db2expln -database SAMPLE -schema INST105 -package  P1884571328 -graph -terminal

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** PACKAGE ***************************************

Package Name = "INST105"."P1884571328"  Version = ""

        Prep Date = 2017/06/30
        Prep Time = 08:46:52

        Bind Timestamp = 2017-06-30-08.46.52.002217

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

        Partition Parallel       = No
        Intra-Partition Parallel = No

        Reoptimization           = No

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

-------------------- SECTION ---------------------------------------
Section = 1


Statement:
  DECLARE CURSOR1 CURSOR 
  WITH RETURN 
  for 
     SELECT ID 
     FROM T1


Section Code Page = 1208

Estimated Cost = 6.812466
Estimated Cardinality = 3.000000

Access Table Name = INST105.T1  ID = 3,5
|  #Columns = 1
|  Skip Inserted Rows
|  May participate in Scan Sharing structures
|  Scan may start anywhere and wrap, for completion
|  Fast scan, for purposes of scan sharing management
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 1
Return Data Completion

End of section


Optimizer Plan:

   Rows   
 Operator 
   (ID)   
   Cost   
         
    3    
 RETURN  
  ( 1)   
 6.81247 
   |     
    3    
 TBSCAN  
  ( 2)   
 6.81247 
   |     
    3    
 Table:  
 INST105 
 T1      



-------------------- SECTION ---------------------------------------
Section = 2


Statement:
  DECLARE CURSOR2 CURSOR 
  WITH RETURN 
  for 
     SELECT NAME 
     FROM T2 
     WHERE ID > 15


Section Code Page = 1208

Estimated Cost = 6.812640
Estimated Cardinality = 1.000000

Access Table Name = INST105.T2  ID = 3,6
|  #Columns = 1
|  Skip Inserted Rows
|  Evaluate Block/Data Predicates Before Locking Committed Row
|  May participate in Scan Sharing structures
|  Scan may start anywhere and wrap, for completion
|  Fast scan, for purposes of scan sharing management
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 1
Return Data Completion

End of section


Optimizer Plan:

   Rows   
 Operator 
   (ID)   
   Cost   
         
    1    
 RETURN  
  ( 1)   
 6.81264 
   |     
    1    
 TBSCAN  
  ( 2)   
 6.81264 
   |     
    3    
 Table:  
 INST105 
 T2

可以看到，package当中，SQL语句 "SELECT NAME FROM T2 WHERE ID > 15"的执行计划并未用到索引扫描，原因是索引是在存储过程创建之后才创建的。

3.查看package cache中的执行计划

如果应用程序的SQL中，使用了邦定变量的方式，必须得从package cache中抓取执行计划

a. 刷新 package cache：
db2 flush package cache dynamic

b. 执行SQL语句，并且不要在其他地方执行这条SQL

c. 使用下面的查询找到SQL对应的executable_id
SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,400) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T

d. 根据上一步中的executable_id，运行下面的存储过程:
CALL EXPLAIN_FROM_SECTION( <executable_id>, 'M', NULL, 0, <schema name of explain tables>, ?, ?, ?, ?, ? )

e. 使用db2exfmt生成执行计划：
db2exfmt -d <dbname> -o explain.out

示例：

inst105@db2a:~$  db2 "flush package cache dynamic"
DB20000I  The SQL command completed successfully.'

inst105@db2a:~$ db2 "SELECT NAME FROM T2 WHERE ID > 15" 

NAME                
--------------------
qing                
song                

  2 record(s) selected.

inst105@db2a:~$ db2 "SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,50) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T "

EXECUTABLE_ID                                                       STMT_EXEC_TIME       TOTAL_CPU_TIME       STMT_TEXT                                         
------------------------------------------------------------------- -------------------- -------------------- --------------------------------------------------
x'0100000000000000110100000000000000000000020020170630092845270960'                    0                    0 SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time
SQL0445W  Value "SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(s" 
has been truncated.  SQLSTATE=01004

x'0100000000000000100100000000000000000000020020170630092841223134'                    0                   73 SELECT NAME FROM T2 WHERE ID > 15                 

  2 record(s) selected with 1 warning messages printed.

inst105@db2a:~$ db2 "CALL EXPLAIN_FROM_SECTION(x'0100000000000000100100000000000000000000020020170630092841223134' , 'M', NULL, 0, 'INST105', ?, ?, ?, ?, ? )" 

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : INST105 

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : INST105 

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2017-06-30-09.29.03.900808

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2K26

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID  

  Parameter Name  : SOURCE_VERSION
  Parameter Value : 

  Return Status = 0
inst105@db2a:~$ db2exfmt -d sample -o packagecache.out1 
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==> 
Enter up to 128 character source name (SOURCE_NAME, Default %%) ==> 
Enter source schema (SOURCE_SCHEMA, Default %%) ==> 
Enter section number (0 for all, Default 0) ==> 
Output is in packagecache.out1.
Executing Connect Reset -- Connect Reset was Successful.
inst105@db2a:~$ more packagecache.out1 
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       10.05.8
FORMATTED ON DB:   SAMPLE
SOURCE_NAME:       SQLC2K26
SOURCE_SCHEMA:     NULLID  
SOURCE_VERSION:      
EXPLAIN_TIME:      2017-06-30-09.29.03.900808
EXPLAIN_REQUESTER: INST105 

Database Context:
----------------
        Parallelism:          None
        CPU Speed:            1.298947e-07
        Comm Speed:           100
        Buffer Pool size:     1000
        Sort Heap size:       256
        Database Heap size:   1200
        Lock List size:       4096
        Maximum Lock List:    10
        Average Applications: 1
        Locks Available:      13107

Package Context:
---------------
        SQL Type:           Dynamic
        Optimization Level: 5
        Blocking:           Block All Cursors
        Isolation Level:    Cursor Stability



---------------- STATEMENT 1  SECTION 201 ----------------
        QUERYNO:       0
        QUERYTAG:      CLP                 
        Statement Type:   Select
        Updatable:        No
        Deletable:        No
        Query Degree:     1

Original Statement:
------------------
SELECT 
  NAME 
FROM 
  T2 
WHERE 
  ID > 15


Optimized Statement:
-------------------
SELECT 
  Q1.NAME AS "NAME" 
FROM 
  INST105.T2 AS Q1 
WHERE 
  (15 < Q1.ID)

Explain level:    Explain from section

Access Plan:
-----------
        Total Cost:             6.81246
        Query Degree:           1

             Rows 
            RETURN
            (   1)
             Cost 
              I/O 
              |
               1 
            FETCH 
            (   2)
            6.81246 
              NA 
         /----+----\
        1             3 
     IXSCAN    TABLE: INST105 
     (   3)          T2
   0.00553156        Q1
       NA 
       |
        3 
 INDEX: INST105 
      IDX2
       Q1