My Oracle Support Banner

FAQ: Adaptive Cursor Sharing (ACS) Frequently Asked Questions (Doc ID 1518681.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.1.0.6 and later
Oracle Database - Personal Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Purpose

This series of Questions and Answers was generated from the Q&A Session for "Understanding Adaptive Cursor Sharing (ACS) with a Test Case and live Demo" Webcast. This recording for this webcast can be accessed in the following document:

<Document 740966.1> Advisor Webcasts: Current Schedule and Archived recordings

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 Ask Questions, Get Help, And Share Your Experiences With This Article
Questions and Answers
 General ACS Questions
 Where are the main documents and Blogs relating to ACS?
 Are the ACS buckets based on actual row processes OR estimated row process ?
 Should STATISTIC_LEVEL be ALL with ACS?
 What is the impact of ACS on the Shared pool?
 Is there any impact/or consideration when using ACS with RAC database ? 
 In RAC, when one instance gets bounced, will it get back the ACS cache from any other instance?
 Does ACS work for the SQL from PL/SQL?
 What is the suggestion to have the CURSOR_SHARING parameter for ACS to be effective?
 Is there a plan to have persistent ACS cursors?
 Licenses and Editions
 Is ACS a separately licensed feature?
 Does ACS work with Standard Edition ?
 ACS and Parameters
 How does CURSOR_SHARING=FORCE/EXACT affect ACS?
 When cursor_sharing=EXACT, will ACS still work?
 Are there drawbacks or negative effects to using the cursor_sharing=FORCE initialisation parameter for Oracle 11g database? Does this have effects on ACS?
 If an application uses literals and CURSOR_SHARING = EXACT, will adding a "bind aware" hint help?
 Are there any recommendations with regard to the SESSION_CACHED_CURSORS initialisation parameter relate to ACS?
 How does the OPEN_CURSORS initialisation parameter relate to ACS?
 ACS and Bind Variables
 Does bind peeking play a role in ACS?
 What is the difference between bind aware and bind sensitive?
 Why is a query containing more than 8 bind variables not bind sensitive?
 Why are there hints to enable/disable bind aware features?
 Can we enable BIND_AWARE hint in any way for all connections from a user or service?
 Should ACS be disabled if the Bind Variable Selectivity Changes too frequently?
 What does it mean for ACS when bind aware is true, but bind sensitive is false?
 ACS and Histograms
 Are histograms always needed for ACS?
 If there is are histograms, how does Oracle determine that a SQL statement is bind sensitive?
 ACS and Query Optimization
 Can ACS choose a Bad Plan when there is a better one available?
 Can ACS make the CBO choose a bad plan?
 Does ACS impact SQLT output?
 ACS and Sharing of Cursors
 Once ACS has made a SQL statement non-shareable, will that plan never be used?
 Is there a danger of generating a high number of version counts with ACS enabled?
 How do SQL profiles play a role in ACS ?
 ACS and SPM
 What is the relationship between ACS and Sql Plan Management (SPM) ?
 If I have some plan baselines that I am happy with, then I enable ACS which creates some plans which look promising, how does SPM and ACS interract?
 What would be the impact of ACS we're NOT using SPM and have Statistics locked
 Using SPM we can pin a good plan, can this be done in case of a situation demanding better plan (among the child cursors) arises with ACS....
 Discuss Adaptive Cursor Sharing
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.