Question: I noticed a query using
the bind_aware hint and I was hoping to get more information
about the bind_aware hint and how it works. Can you
explain the bind_aware hint?
Answer: The bind_aware
hint is used with Oracle's
adaptive cursor sharing facility.
select
/*+ bind_aware */
customer_stuff
where
region = 'ATLANTA';
Starting in release 11.1.0.7 and beyond, using the
bind_aware hint forces bind-aware cursor_sharing
from the first execution of the SQL statement. By
making the SQL statement bind aware, Oracle considers the
bind variable value in determining whether to use the
existing execution plan or generate a new execution plan.
Plan first for bind_aware
Oracle Corporation strongly recommends examining the
existing child cursors in the library cache before using the
bind_aware hint, largely because there is
additional overhead associated with the bind_aware
hint
- Additional cursor memory required
- Overhead of extra soft & hard parses
- Extra child cursors
|
|
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.
|