dblook 导出表结构很慢,很慢
查看了 执行了的sql,这个sql执行了 几百次,感觉上 导出一张表,就执行下面的sql一次
怀疑是下面这个sql执行的:
DECLARE THIS_TABLE_ALIAS CURSOR FOR
with iv (tabname, tabschema, base_tabname, base_tabschema, remarks,create_time, definer) as
((SELECT tabname, tabschema, base_tabname, base_tabschema, remarks,create_time, definer
FROM SYSCAT.TABLES
WHERE type = 'A' and base_tabname = :H00005
and base_tabschema = :H00007 )
union all
(select st.tabname, st.tabschema, st.base_tabname, st.base_tabschema, st.remarks,st.create_time, st.definer
from iv, SYSCAT.TABLES st
where iv.tabname = st.base_tabname and iv.tabschema = st.base_tabschema and st.type = 'A'))
select tabname,
tabschema,
base_tabname,
base_tabschema,
remarks,
create_time,
definer
from iv
ORDER BY tabschema, tabname FOR READ ONLY
我执行了一下这个sql的部分
Statement:
DECLARE C1 CURSOR
FOR
SELECT tabname, tabschema, base_tabname, base_tabschema, remarks,
create_time, definer
FROM SYSCAT.TABLES
WHERE type ='A' and base_tabname ='a' and base_tabschema ='a'
Section Code Page = 1208
Estimated Cost = 56141.507812 --这么高的cost
Estimated Cardinality = 0.000000
( 2) Access Table Name = SYSIBM.SYSTABLES ID = 0,5
| Index Scan: Name = SYSIBM.INDTABLES09 ID = 9
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: AUDITPOLICYID (Ascending)
| #Columns = 7
| Evaluate Block/Data Predicates Before Locking Committed Row
| Evaluate Predicates Before Locking for Committed Key
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Data Prefetch: Eligible 3506
| Index Prefetch: Eligible 3506
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 3
( 1) | | Return Data to Application
| | | #Columns = 7
( 1) Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
3.27867e-10
n/a
RETURN
( 1)
56141.5
|
3.27867e-10
n/a
FETCH
( 2)
56141.5
/
55226 55226
n/a n/a
IXSCAN Table:
( 3) SYSIBM
395.307 SYSTABLES
|
1
Index:
SYSIBM
INDTABLES09
导出很慢很慢,想这个sql 这么优化呢?
附件:
db2trc.part01.rar (8.58 MB)
db2trc.part02.rar (8.58 MB)
db2trc.part02.rar (8.58 MB)
db2trc.part03.rar (5.65 MB)
db2support.zip (1.4 MB)
db2batch.sql (88.24 KB)
db2support.zip (1.36 MB)
收起