BIND_AWARE and NO_BIND_AWARE Hints:
From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.
SQL> SELECT /*+ BIND_AWARE */ MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type;
The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.
There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.
Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the “adaptive” approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.
Thank you for reading… This is Airy…Enjoy