0

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?

1

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

  • Oracle 12.1 introduced adaptive plans that can be changed during execution if the cardinality feedback deviates too much from the plan estimates – a_horse_with_no_name Feb 21 '18 at 9:34
  • @a_horse_with_no_name Which is also a nice feature on paper, but fails in practice. 12.2 disabled some of the new features from 12.1. Even Oracle recommends disabling adaptive features in the installation guide of their own products such as Enterprise Manager Cloud Control or Oracle Flexcube. Cardinality feedback (renamed to statistics feedback) is different, that kicks in on consequent executions. With adaptive plans, the database can change the execution plan during the actual execution. – Balazs Papp Feb 21 '18 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.