Question: What is
extended cursor_sharing (ECS), and how does extended
cursor_sharing differ from adaptive cursor_sharing?
Answer: The extended
cursor_sharing (ECS) working with adaptive cursor_sharing to
allow real-time bind peeking to see if the bind variable
value influences the execution plan for the SQL statement,
See these important notes on
adaptive cursor_sharing and also see my note on the
bind_aware hint
According to the Oracle optimizer group. "Bind Aware
Cursor Sharing" (BASC) is a complex technology that consists
of Adaptive Cursor Sharing (ACS) and Extended Cursor Sharing
(ECS) technologies:
- Adaptive cursor_sharing monitors queries where
extended cursor_sharing is "applicable" (e.g. binds
were peeked, predicate shape).
-
Adaptive cursor_sharing identify queries with
variable data volume.
-
Adaptive cursor_sharing requires you to enable
extended. cursor_sharing (bind_aware hint).
According to Oracle, if any of the following checks fail,
extended cursor_sharing will be disabled:
- other hints are used in the query
- Stored outlines (plan stability) is in-use
- There are more than 14 binds in the SQL statement
- Parameters such as bind_peeking=false is
set
- The release of Oracle does not support extended
cursor sharing
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
