银行
如何查看某个SQL的真实的执行计划
现在有个同事说他的SQL慢，我已经看到了他的SQL的语句，我想知道这个SQL对应的执行计划是什么样子的，是当时执行的执行计划，而不是现在通过db2expln这个工具现在才生成的执行计划。
9同行回答
two steps:
1) use "db2pd -d -dyn" get the AnchID StmtUID NumEnv NumVar for the SQL
2) use db2expln -db -cache ,,,
see an example:
[db2inst2@q2 ~]$ db2expln -d sample -cache 828,1,1,1 -t
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
******************** SQL Cache ***************************************
==================== Variation ==========================================
Anchor ID = 828
Statement UID = 1
Environment ID = 1
Variation ID = 1
Isolation Level = Cursor Stability
Blocking = Block All Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"DB2INST2"
Statement:
select *
from dba_objects, dba_objects, dba_objects
Section Code Page = 1208
Estimated Cost = 1169419.250000
Estimated Cardinality = 5040255488.000000
Data Stream 1:
| Not Piped
| (
| | Access Table Name = SYSIBM.SYSTRIGGERS ID = 0,35
| | | #Columns = 5
| | | 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
| UNION
| | Access Table Name = SYSIBM.SYSDATATYPES ID = 0,28
| | | #Columns = 8
| | | 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
....收起
1) use "db2pd -d
2) use db2expln -db
see an example:
[db2inst2@q2 ~]$ db2expln -d sample -cache 828,1,1,1 -t
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
******************** SQL Cache ***************************************
==================== Variation ==========================================
Anchor ID = 828
Statement UID = 1
Environment ID = 1
Variation ID = 1
Isolation Level = Cursor Stability
Blocking = Block All Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"DB2INST2"
Statement:
select *
from dba_objects, dba_objects, dba_objects
Section Code Page = 1208
Estimated Cost = 1169419.250000
Estimated Cardinality = 5040255488.000000
Data Stream 1:
| Not Piped
| (
| | Access Table Name = SYSIBM.SYSTRIGGERS ID = 0,35
| | | #Columns = 5
| | | 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
| UNION
| | Access Table Name = SYSIBM.SYSDATATYPES ID = 0,28
| | | #Columns = 8
| | | 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
....收起
浏览535
good 很不错收起
浏览550
浏览546
可否多讲讲db2batch哪？我在网上搜索这个命令，这个工具好像是用来执行一个sql脚本，然后统计里面的信息的！并不是从数据库里抓当前执行sql的执行计划啊？收起
浏览599
估计看不到，如果没有重新runstats，索引和数据量没有太大变化，之前和现在应该是一样的收起
浏览544