Abstract Plans in Cached Statements

Adaptive Server version 15.7 introduces the ability to save abstract plan information in the statement cache.

In this example, which includes an abstract plan, the hash table saves select * from t1 plan '(use optgoal allrows_mix)', as shown in the SQL TEXT line:

1> select * from t1 plan '(use optgoal allrows_mix)'
2> go
1> dbcc prsqlcache
2> go

Start of SSQL Hash Table at 0x0x1474c9050

Memory configured: 1000 2k pages        Memory used: 17 2k pages


Bucket# 243 address 0x0x1474c9f80

SSQL_DESC 0x0x1474cd070
ssql_name *ss0626156152_0290084701ss*
ssql_hashkey 0x0x114a575d       ssql_id 626156152
ssql_suid 1             ssql_uid 1      ssql_dbid 1      ssql_spid 0
ssql_status 0x0xa0      ssql_parallel_deg 1
ssql_isolate 1          ssql_tranmode 32
ssql_keep 0             ssql_usecnt 1   ssql_pgcount 6
SQL TEXT: select * from t1 plan '(use optgoal allrows_mix)'

End of SSQL Hash Table

In versions of Adaptive Server earlier than 15.7, the SQL TEXT line included only the select * from t1 command, without the plan clause.

See the Performance and Tuning Series: Query Processing and Abstract Plans.