set option show_abstract_plan prints the optimal abstract plan currently running on the TDS connection. It prints the plan after optimization and before execution, and is the only set option show_ command that does not depend on trace flag 3604 or 3605.
Printing the final plan’s abstract plan is similar to viewing showplan output: it provides information to the user, but the abstract plan is not saved in sysqueryplans, and is not used if you use the apstract plan load mode.
This example shows the optimal abstract plan currently running on the TDS connection:
1> set option show_abstract_plan on 2> go 1> select r1, sum(s1) 2> from r, s 3> where r2=s2 4> group by r1
The Abstract Plan (AP) of the final query execution plan: ( group_sorted ( nl_join ( i_scan ir12 r ) ( i_scan is21 s ) ) ) ( prop r (parallel 1 ) ( prefetch 2 ) ( lru ) ) ( prop s ( parallel 1 ) ( prefetch 2 ) (lru ) ) To experiment with the optimizer behavior, this AP can be modified and then passed to the optimizer using the PLAN clause: SELECT/INSERT/DELETE/UPDATE ... PLAN '( ... )'. r1 ----------- ----------- 1 2 2 4 (2 rows affected)
