Abstract plan capture saves the full text of the SQL statement and abstract plan association is based on the full text of the SQL query. If users submit ad hoc SQL statements, rather than using stored procedures or Embedded SQL, abstract plans are saved for each different combination of query clauses. This can result in a very large number of abstract plans.
If users check the price of a specific title_id using select statements, an abstract plan is saved for each statement. The following two queries each generate an abstract plan:
select price from titles where title_id = "T19245"
select price from titles where title_id = "T40007"
In addition, there is one plan for each user, that is, if several users check for the title_id “T40007”, a plan is save for each user ID.
If such queries are included in stored procedures, there are two benefits:
Only only one abstract plan is saved, for example, for the query:
select price from titles where title_id = @title_id
The plan is saved with the user ID of the user who owns the stored procedure, and abstract plan association is made based on the procedure owner’s ID.
Using Embedded SQL, the only abstract plan is saved with the host variable:
select price from titles where title_id = :host_var_id
