Either the query optimizer or the query execution layer can generate diagnostic output. To generate diagnostic output in text format, use this set option command:
set option
{ {show | show_lop | show_managers | show_log_props |
show_parallel | show_histograms | show_abstract_plan |
show_search_engine | show_counters | show_best_plan |
show_code_gen | show_pio_costing | show_lio_costing |
show_pll_costing | show_elimination | show_missing_stats}
{normal | brief | long | on | off} }...
Each option specified must be followed by a choice of
normal, brief, long, on, or off. On and normal are equivalent. More
than one option, and its corresponding choice, may be specified
in a set option command, with each pair separated
by a comma.
Option |
Definition |
|---|---|
show |
Shows a reasonable collection of details, where the collection depends on the choice of {normal | brief | long | on | off}. |
show_lop |
Shows the logical operators used. |
show_managers |
Shows data structure managers used during optimization. |
show_log_props |
Shows the logical properties evaluated. |
show_parallel |
Shows details of parallel query optimization. |
show_histograms |
Shows the processing of histograms associated with SARG/join columns. |
show_abstract_plan |
Shows the details of an abstract plan. |
show_search_engine |
Shows the details of the join-ordering algorithm. |
show_counters |
Shows the optimization counters. |
show_best_plan |
Shows the details of the best query plan selected by the optimizer. |
show_code_gen |
Shows details of code generation. |
show_pio_costing |
Shows estimates of physical input/output (reads/writes from/to the disk). |
show_lio_costing |
Shows estimates of logical input/output (reads/writes from/to memory). |
show_pll_costing |
Shows estimates relating to costing for parallel execution. |
show_elimination |
Shows partition elimination. |
show_missing_stats |
Shows details of useful statistics missing from SARG/join columns. |
