IT分销/经销数据库

select syscat.tables 很慢的一个摄取量

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.part03.rar (5.65 MB)

附件图标 db2support.zip (1.4 MB)

附件图标 db2batch.sql (88.24 KB)

附件图标 db2support.zip (1.36 MB)

关注1

参与87

86同行回答
全部行业
全部行业 IT分销/经销 互联网服务 政府机关 银行
|
按赞同排序
按时间排序

drdb2系统工程师se

回复 75# nowhill

Thank you for your update.
I think I have found the problem.
Sadly, I have to say "-z" introduced a "bug" in version 9.

Instead please try "-a".
Therefore, I'd like to try out "db2look -d -a -e -o ".
Please update us about the performance of speed with the old setting and the new setting of bufferpool.
Thanks.收起

互联网服务 · 2011-07-05

nowhill系统工程师

格式化了一下  刚才那个实在是太难看了

-- 导入导出过程

[root@localhost ~]# date
2011年 07月 05日星期二 08:56:18 CST
[root@localhost ~]# db2look -d suzhou -i l_sz_v15 -w afcapp -e -z l_sz_v15 -o /home/db2inst1/dbbackup/l_sz_v15_test.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: ROOT
-- Specified SCHEMA is: L_SZ_V15
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- Output is sent to file: /home/db2inst1/dbbackup/l_sz_v15_test.sql
[root@localhost ~]# date
2011年 07月 05日星期二 08:57:52 CST

--导出前
------------------------------ 输入的命令 ------------------------------
select current_timestamp from sysibm.dual;
select
num_executions  as "总的执行次数",
LAST_METRICS_UPDATE,
STMT_TEXT,
(POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读",
(POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读"
from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf
where num_executions>0
and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%'
and stmt_text not like '%MON_GET_PKG_CACHE_STMT%'
order by QUERY_COST_ESTIMATE/num_executions desc;

select current_timestamp from sysibm.dual;
------------------------------------------------------------------------------
select current_timestamp from sysibm.dual

1
--------------------------
2011-07-05-08.56.10.231731

  1 条记录已选择。

select num_executions  as "总的执行次数", LAST_METRICS_UPDATE, STMT_TEXT, (POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读", (POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读" from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf where num_executions>0 and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%' and stmt_text not like '%MON_GET_PKG_CACHE_STMT%' order by QUERY_COST_ESTIMATE/num_executions desc

总的执行次数       LAST_METRICS_UPDATE
13020          2011-07-05-08.54.32.638919
STMT_TEXT
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    :H00004
and                   base_tabschema = :H00007       :H00006          )
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
逻辑读             物理读
10103                   0

  1 条记录已选择。

select current_timestamp from sysibm.dual

1
--------------------------
2011-07-05-08.56.10.571164

  1 条记录已选择。

--导出后

------------------------------ 输入的命令 ------------------------------
select current_timestamp from sysibm.dual;
select
num_executions  as "总的执行次数",
LAST_METRICS_UPDATE,
STMT_TEXT,
(POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读",
(POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读"
from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf
where num_executions>0
and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%'
and stmt_text not like '%MON_GET_PKG_CACHE_STMT%'
order by QUERY_COST_ESTIMATE/num_executions desc;

select current_timestamp from sysibm.dual;
------------------------------------------------------------------------------
select current_timestamp from sysibm.dual

1
--------------------------
2011-07-05-08.57.59.949177

  1 条记录已选择。

select num_executions  as "总的执行次数", LAST_METRICS_UPDATE, STMT_TEXT, (POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读", (POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读" from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf where num_executions>0 and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%' and stmt_text not like '%MON_GET_PKG_CACHE_STMT%' order by QUERY_COST_ESTIMATE/num_executions desc

总的执行次数       LAST_METRICS_UPDATE
14667             2011-07-05-08.57.47.128307
STMT_TEXT
DECLARE THIS_TABLE_ALIAS CURSOR FOR
with iv (tabname, tabschema, base_tabname, base_tabschema, remarks,                   c
reate_time, definer) as          ((SELECT  tabname, tabschema, base_tabname, base_tabschema, remarks,
         create_time, definer             FROM SYSCAT.TABLES             WHERE type = 'A' and
                              base_tabname = :H00005    :H00004          and
                              base_tabschema = :H00007       :H00006          )
                                 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

逻辑读             物理读
10103                   0

  1 条记录已选择。

select current_timestamp from sysibm.dual

1
--------------------------
2011-07-05-08.58.00.533372

  1 条记录已选择。

14667-13020=1647 次

select count(*) from syscat.tables where tabschema='L_SZ_V15' and type='T'

1
-----------
   1645

  1 条记录已选择。


1645 张表  执行了  1647次，所有我怀疑导出一张表，就执行一次这个sql收起

IT分销/经销 · 2011-07-05

nowhill系统工程师

拷贝进去后好多空格啊收起

IT分销/经销 · 2011-07-05

weiruan85数据库管理员ibm

幸好这个还可以找到执行的sql, 不知道db2trc 能不能找到问题原因。收起

政府机关 · 2011-07-05

nowhill系统工程师

回复 nowhill

db2look is one of most important utility in db2.
I do not think it is that lousy to ...
drdb2 发表于 2011-7-5 00:55

看下面的收起

IT分销/经销 · 2011-07-05

start2000系统架构师ABB

dblook 导出表结构很慢，很慢
查看了执行了的sql，这个sql执行了几百次，感觉上导出一张表，就执行下面 ...
nowhill 发表于 2011-6-27 15:34

很好,很多人忽视系统这个default bufferpool,收起

互联网服务 · 2011-07-05

drdb2系统工程师se

回复 68# nowhill

db2look is one of most important utility in db2.
I do not think it is that lousy to loop each table.
Therefore I could not support your claim based on "observation",
that the query being excuted thousands of times.
You need to have evidence to supoort that,
such as number of executions.

To test out myself, I have traced db2look on serveral my
databases (9.7, 9.5, and 8.2) either 64 or 32 bit,
and I found the query being executed
only 1-3 times no matter how many tables.收起

互联网服务 · 2011-07-05

nowhill系统工程师

回复 nowhill

no major RDBMS allows users to add indexes to engine base tables. That's true f ...
drdb2 发表于 2011-7-4 11:15

这个倒是事实，oracle很多原始表也是不能更改的收起

IT分销/经销 · 2011-07-04

nowhill系统工程师

回复 nowhill

The query being executed 1-2 times during the db2look, even you had an index on TYPE ...
drdb2 发表于 2011-7-4 11:07

是导出一张表，就运行这个sql 1次，我这边观察的是这个结果收起

IT分销/经销 · 2011-07-04

drdb2系统工程师se

回复 65# nowhill

no major RDBMS allows users to add indexes to engine base tables. That's true for both Oracle and DB2.收起

互联网服务 · 2011-07-04

select syscat.tables 很慢的一个摄取量

86同行回答
全部行业
全部行业 IT分销/经销 互联网服务 政府机关 银行
|
按赞同排序
按时间排序

提问者

相关问题

相关资料

相关文章

问题状态

select syscat.tables 很慢的一个摄取量

86同行回答全部行业全部行业IT分销/经销互联网服务政府机关银行|按赞同排序按时间排序

提问者

相关问题

相关资料

相关文章

问题状态

86同行回答
全部行业
全部行业 IT分销/经销互联网服务政府机关银行
|
按赞同排序
按时间排序