FAQ: Adaptive Cursor Sharing (ACS) Frequently Asked Questions
(Doc ID 1518681.1)
Last updated on MAY 15, 2023
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterOracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A 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:
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 no 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 |