| Any statement will run using a custom plan at first. Only a prepared statement creates the memory area in the backend that can hold the custom plan statistics, that is why generic plans only work with prepared statements. A prepared statement has to run at least 5 times before the planner looks at the plan statistics and determine whether a generic plan would work (=generic plan cost being equal or lower than the average custom plan cost) That means that the values in the binds/filters also play a role (as far as I know). Once a generic plan is selected, it doesn’t do the statistics evaluation anymore, and thus the generic plan is fixed until the prepared statement is closed or the session is terminated. It therefore also cannot choose a different plan based on the bind values anymore. This means that if you want to manually replay your issue, just issuing it using a prepared statement manually is not exactly what happens in a session. Such a replay will always use a custom plan. You have to perform it at least 5 times for the generic plan to be considered. And because that is evaluated cost based upon the cost of the previous custom plans, the binds (filter values) have to entered correctly too that have lead up to a potential generic plan having been chosen. Frits Hoogland
|
