Difference between revisions of "Capturing IQ query plans"

From SybaseWiki
Jump to: navigation, search
m
 
(One intermediate revision 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 run from your session (since the option is temporary). Output from the index advisor is also added.
+
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 these 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.
+
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:
+
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    temporary option <username>.query_plan_as_html_directory = '<directory>'
+
  set    option <username>.query_plan_as_html_directory = '<directory>'
  
 
An HTML query plan looks like this:
 
An HTML query plan looks like this:
Line 23: Line 23:
 
There is also a lot of other detailed information included in the html file, but not shown in the image above.
 
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]]
 
[[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:

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