SQL 如何使用 DB2 Explain？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1098814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use DB2 Explain?
提问by Trevel
How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2?
如何使用 DB2 的 Explain 功能？-- 既可以运行它，也可以使用它来优化查询。是否有更好的工具可用于 DB2？
I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal.
我以前构建过查询，但我必须告诉他们需要多长时间的唯一方法是运行它们并为它们计时——这并不理想。
Edit: The answer for me turned out to be "You can't. You don't have and cannot get the access." Don't you love bureaucracy?
编辑：我的答案原来是“你不能。你没有也不能获得访问权限。” 你不喜欢官僚主义吗？
采纳答案by Fred Sobotka
What you're looking for is covered by two DB2 utilities:
您要查找的内容包含在两个 DB2 实用程序中：
- The explain utility, which shows the optimizer's access plan and estimated cost for a specific query (based on current RUNSTATS statistics)
- The design advisor, which recommends structural changes to improve the performance of one or more queries
Both utilities require specialized tablesto be created in the database.
这两个实用程序都需要在数据库中创建专门的表。
I tend to use the explain utility more than the advisor, especially if I am able to change the SQL for the query to influence a better access plan. I use the db2expln command-line utility to explain different versions of a query I'm tuning and compare their costs. What's most important is that your table and index statistics are up to date when running explain or the design advisor.
我更倾向于使用解释实用程序而不是顾问程序，尤其是当我能够更改查询的 SQL 以影响更好的访问计划时。我使用 db2expln 命令行实用程序来解释我正在调整的查询的不同版本并比较它们的成本。最重要的是，您的表和索引统计信息在运行解释或设计顾问时是最新的。
回答by Josh Hull
IBM offers Data Studioas a free tool built on eclipse, which among other benefits includes a GUI for running visual explain, as well as providing tuning help through a query adviser.I highly recommend using Data Studio.
IBM 提供Data Studio作为构建在 Eclipse 上的免费工具，其中包括用于运行可视化解释的 GUI，以及通过查询顾问提供调优帮助。我强烈推荐使用数据洞察。
It is relatively easy to set up the correct resources (the explain tables that need to be built, and the bind that need to be done) by right clicking a connected data source, and choosing
通过右键单击连接的数据源，然后选择正确的资源（需要构建的解释表和需要完成的绑定）相对容易设置
analyze and tune > configure for tuning > guided configuration.
分析和调整 > 配置调整 > 引导配置。
To generate the explain graph- simply highlight your query, right click, and choose "Open Visual Explain":
要生成解释图- 只需突出显示您的查询，右键单击，然后选择“打开可视化解释”：
To use the query advisor, choose "start tuning" instead. It will take you through a process which will generate the explain, as well as recommend any tuning opportunities it can determine.
要使用查询顾问程序，请改为选择“开始调整”。它将带您完成一个生成解释的过程，并推荐它可以确定的任何调整机会。
回答by khkarens
db2expln -database sample -t -g -f "sql query"
db2expln -database sample -t -g -f "sql 查询"
This worked fine for me.
这对我来说很好。
回答by phatmanace
I'\ve always found running the explain utitlity from the command line with '-g' mode turned on has helped me pinpoint problems.
我总是发现在打开“-g”模式的情况下从命令行运行解释实用程序可以帮助我查明问题。
I can't recall the options now, and IIRC, db2expln is a little fussy about the ordering {i.e you can't put username after password!!} - but it works well.
我现在不记得这些选项了，而且 IIRC、db2expln 对排序有点挑剔{即你不能在密码后面加上用户名！！} - 但它运行良好。
