Difference between revisions of "Capturing IQ query plans"

Revision as of 22:09, 6 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 run from your 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 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.

set     temporary option query_plan_as_html = on
set     temporary 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:

set     temporary 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.