Does oracle database suffer from parameter sniffing issue in stored procedures?
Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a feature. Also considering with Sql Server 2017 features like automatic query tuning, I was wondering is SQL Server late in fixing these estimates/plan caching issues compared to other Database management system?
Yes it does.
Starting with version 11.1, we have Adaptive Cursor Sharing with bind sensitivity and bind awareness, which allows the optimizer to produce multiple optimal execution plans for the same SQL statement, depending on the bind values.
Purpose of Adaptive Cursor Sharing
With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. If a column in a WHERE clause has skewed data, however, then a histogram may exist on this column. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values.
In adaptive cursor sharing, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 10 and a different plan for bind value 50. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the optimizer does not always choose the same plan for each execution or bind variable value. Thus, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.
This does wonders on paper and in presentations, but in the real world, there is still room for improvement.
Shortly: the problem with ACS is that it does not work immediately for new values. It uses the already existing execution plan optimal for other values, and after that, on a repeated attempt, it may choose a different plan. We may not have time to wait for the first sub-optimal attempt for the new bind value to finish, as it may take orders of magnitude longer depending on the actual statement and data quantity/distribution.
https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1
