临时表统计？ [英] Statistics on temporary tables?
问题描述
是否可以获得临时表的统计信息？我经常将
放在一个临时表中10000行或更多行，并且想知道它。
查询变得更快，并对这些表进行统计。
w。********* @ googlemail.com 写道：
是否可以对临时表进行统计？我经常将
放在临时表中10000行或更多行，并且想知道它。
这些表的统计信息使查询变得更快。
绝对。
如果你想用程序以编程方式收集统计数据
声明临时使用ADMIN_CMD （）程序
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
好的，有效。现在我想要解释详细信息：
哪个小节适用于SQL的哪个部分？这是由
dynexplain提供的。或db2expln。但是：我不能运行统计数据，然后在解释中使用
，因为对于db2expln而言在-setup选项中没有runstats
支持。
所以我没有机会看到优化在DGTT上使用runstats时，将SQL分发到
小节。
这对进一步分析非常有用：运行的哪一部分
SQL在第6小节（例如）中导致这种高CPU / MEM使用率。这个
信息是由运行SQL的应用程序的快照给出的。
但是使用runstats没有机会看到处理的SQL部分
在第6小节...
9月13日，8：31 * pm，Serge Rielau< srie ... @ ca.ibm.comwrote：
wlfisc ... @ googlemail.com写道：
是否可以对临时表进行统计？我经常将
放在临时表中10000行或更多行，并且想知道它。
这些表的统计信息使查询变得更快。
绝对。
如果你想用程序以编程方式收集统计数据
声明临时使用ADMIN_CMD （）程序
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
9月24日，1：40 * am， " stefan.albert" < stefan.alb ... @ spb.dewrote：
好的，有效。现在我想要解释详细信息：
哪个小节适用于SQL的哪个部分？这是由
dynexplain提供的。或db2expln。但是：我不能运行统计数据，然后在解释中使用
，因为对于db2expln而言在-setup选项中没有runstats
支持。
所以我没有机会看到优化在DGTT上使用runstats时，将SQL分发到
小节。
这对进一步分析非常有用：运行的哪一部分
SQL在第6小节（例如）中导致这种高CPU / MEM使用率。这个
信息是由运行SQL的应用程序的快照给出的。
但是使用runstats没有机会看到处理的SQL部分
在第6小节中...
9月13日，8：31 * pm，Serge Rielau< srie ... @ ca.ibm.comwrote:
wlfisc ... @ googlemail.com写道：
是否可以对临时表进行统计？我经常将
放在临时表中10000行或更多行，并且想知道它。
这些表的统计信息使查询变得更快。
绝对。
如果你想用程序以编程方式收集统计数据
声明临时使用ADMIN_CMD（）程序
干杯
Serge
< blockquote class =post_quotes>
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Stefan。
我很可能误解了你的核心问题''
试图解决，所以请耐心等待，但如果问题是你
想要解析DGTT（实际上，存储的SQL中的
程序），你会想要设置一个预编译选项，就是：
CALL SET_ROUTINE_OPTS_GET_ROUTINE_OPTS（）||''EXPLAIN ALL''）;
然后运行，例如，db2exfmt
如果问题是你只是感叹只收集并显示DGTT的统计数据，那么你是对的。
- 杰夫
Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.
w.*********@googlemail.com wrote:Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Ok, that works. Now I want the explain with detailed information:
Which subsection works on what part of the SQL? This is provided by
"dynexplain" or "db2expln". But: I can''t run statistics and then make
the explain, because for "db2expln" in the -setup option no runstats
are supported.
So I have NO chance to see the "optimzed" distribution of the SQL into
subsections when using runstats on DGTTs.
This is very helpful for further analysis: Which part of the running
SQL causes this high CPU/MEM-usage in subsection 6 (e.g.). This
information is given by snapshot for application for a running SQL.
But with runstats NO chance to see the part of SQL which is processed
in subsection 6...
On Sep 13, 8:31*pm, Serge Rielau <srie...@ca.ibm.comwrote:w.l.fisc...@googlemail.com wrote:Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.
Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Sep 24, 1:40*am, "stefan.albert" <stefan.alb...@spb.dewrote:Ok, that works. Now I want the explain with detailed information:
Which subsection works on what part of the SQL? This is provided by
"dynexplain" or "db2expln". But: I can''t run statistics and then make
the explain, because for "db2expln" in the -setup option no runstats
are supported.
So I have NO chance to see the "optimzed" distribution of the SQL into
subsections when using runstats on DGTTs.
This is very helpful for further analysis: Which part of the running
SQL causes this high CPU/MEM-usage in subsection 6 (e.g.). This
information is given by snapshot for application for a running SQL.
But with runstats NO chance to see the part of SQL which is processed
in subsection 6...
On Sep 13, 8:31*pm, Serge Rielau <srie...@ca.ibm.comwrote:
w.l.fisc...@googlemail.com wrote:Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.
Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi, Stefan.
It''s quite possible I''m misunderstanding the core problem you''re
trying to solve, so please bear with me, but if the issue is that you
want to EXPLAIN against the DGTT (indeed, the SQL in stored
procedures), you''ll want to set a precompile option, to wit:
CALL SET_ROUTINE_OPTS_GET_ROUTINE_OPTS()||'' EXPLAIN ALL'');
And then run, e.g., db2exfmt
If the issue is that you''re just lamenting that only abridged
statistics are collected and displayed for DGTTs, you''re right.
--Jeff
这篇关于临时表统计？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
