Difference between revisions of "Capturing IQ query plans"
m |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | Capturing IQ query plans can be done when you set a few options. With the options given below a query plan is generated into the IQ message file for each query | + | Capturing IQ query plans can be done when you set a few options. With the options given below a query plan is generated into the IQ message file for each query query from your own session (since the option is temporary). Output from the index advisor is also added. |
set temporary option query_plan = on | set temporary option query_plan = on | ||
| Line 8: | Line 8: | ||
set temporary option index_advisor = on | set temporary option index_advisor = on | ||
| − | With | + | With the following options the query plan is also generated in HTML format. For practical reasons store these plans into a separate directory. The directory should be accessible by the IQ server. |
set temporary option query_plan_as_html = on | set temporary option query_plan_as_html = on | ||
| − | set temporary option query_plan_as_html_directory = '<directory>' | + | set option query_plan_as_html_directory = '<directory>' |
| + | |||
| + | To set the option query_plan_as_html_directory you need DBA permission, so when you set the option it makes sense to also include the username, but do not set it as temporary. | ||
| + | |||
| + | set option <username>.query_plan_as_html_directory = '<directory>' | ||
| + | |||
| + | An HTML query plan looks like this: | ||
| + | |||
| + | [[Image:Html_query_plan.gif]] | ||
| + | |||
| + | There is also a lot of other detailed information included in the html file, but not shown in the image above. | ||
| + | |||
| + | Please note that what happens during the phase "Condition Execution" is described here: | ||
| + | http://infocenter.sybase.com/help/topic/com.sybase.dc38151_1270/html/iqref/CACBCCAH.htm | ||
| + | [[Category:IQ]] | ||
Latest revision as of 07:39, 7 January 2008
Capturing IQ query plans can be done when you set a few options. With the options given below a query plan is generated into the IQ message file for each query query from your own session (since the option is temporary). Output from the index advisor is also added.
set temporary option query_plan = on set temporary option query_plan_after_run = on set temporary option query_timing = on set temporary option query_detail = on set temporary option row_counts = on set temporary option index_advisor = on
With the following options the query plan is also generated in HTML format. For practical reasons store these plans into a separate directory. The directory should be accessible by the IQ server.
set temporary option query_plan_as_html = on set option query_plan_as_html_directory = '<directory>'
To set the option query_plan_as_html_directory you need DBA permission, so when you set the option it makes sense to also include the username, but do not set it as temporary.
set option <username>.query_plan_as_html_directory = '<directory>'
An HTML query plan looks like this:
There is also a lot of other detailed information included in the html file, but not shown in the image above.
Please note that what happens during the phase "Condition Execution" is described here: http://infocenter.sybase.com/help/topic/com.sybase.dc38151_1270/html/iqref/CACBCCAH.htm
