This issue refers to situations where the optimizer generates an execution plan that is optimal for a specific dataset—such as queries involving accounts with either a small or large amount of data—and caches it. However, when the same plan is reused for subsequent queries with different data distributions, such as accounts with significantly more or fewer records, it may no longer be optimal. This can lead to increased SQL request latency and reduced throughput.
Consider the following scenario:
A table stores order information by user, with each record containing both a user ID and an order ID. Indexes are created for both fields.
To address this issue, you can try the following approaches:
Refresh the plan cache: This action clears the cached plans and triggers a hard parse to generate a new execution plan. However, this may still result in a suboptimal plan.
Bind an execution plan: Use the
CREATE OUTLINEstatement to bind a specific plan to the target SQL.
For more information, see the section How to resolve SQL query issues related to account size skew in the SQL FAQ, and the Account size skew scenario in Typical SQL tuning scenarios and cases.