You can use set tracefile in combination with other set commands and options that provide diagnostic information for a better understanding of slow-running queries. These are the set commands and options that save diagnostic information to a file:
set show_sqltext [on | off]
set showplan [on | off]
set statistics io [on | off]
set statistics time [on | off]
set statistics plancost [on | off]
These are the set options:
set option show [normal | brief | long | on | off]
set option show_lop [normal | brief | long | on | off]
set option show_parallel [normal | brief | long | on | off]
set option show_search_engine [normal | brief | long | on | off]
set option show_counters [normal | brief | long | on | off]
set option show_managers [normal | brief | long | on | off]
set option show_histograms [normal | brief | long | on | off]
set option show_abstract_plan [normal | brief | long | on | off]
set option show_best_plan [normal | brief | long | on | off]
set option show_code_gen [normal | brief | long | on | off]
set option show_pio_costing [normal | brief | long | on | off]
set option show_lio_costing [normal | brief | long | on | off]
set option show_log_props [normal | brief | long | on | off]
set option show_elimination [normal | brief | long | on | off]
