A Hint without +

I always though, every hint would start with /*+ or –+, SQL reference   http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF00219 also says that.

But I learnt something new on

http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm

about the

“adaptive cursor sharing hint (acs_1)“

:

The same on

http://www.dba-oracle.com/t_cursor_sharing_similar.htm :

an “11g adaptive cursor sharing tip“

Interesting, isn’t it ?

Couldn’t find something about this hint without a + in the docs nor on support.

But didn’t optimizermagic himself write about it on http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html :

Plenty of

/*ACS_1*/

‘s there ! ( as on http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html )

Well, they only use /*ACS_1 */ as a marker comment to find the sql later on in v$sql, so they can demonstrate the new 11g adaptive cursor sharing feature.

I arrived to a conclusion how this “tip”  originated and had a great laugh !

Should everyone who read this come to his own conclusions

By the way, on http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html , they indeed announce a hint to mark a statement as bind aware:

“

…

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

…

“

Seems to me that it is the BIND_AWARE hint mentioned in Bug 9532657 :

“

…

For cursors where one knows it wants to be bind aware then add a 
/*+ BIND_AWARE */ hint. 
This makes the cursor aware of bind values rather than automatically 
trying to work out if it should change from non-aware to aware 
based on execution statistics.
...
"

Can’t check it now because I have no access to an 11g at the moment, will check it later on and update this not.

Update:

seems to be correct, BIND_AWARE ( which does occur in V$SQL_HINT ) suffices to mark an sql bind aware, as long as statistics do exist:

sokrates@11.2 > create table t ( i int );

Table created.

sokrates@11.2 > exec dbms_stats.gather_table_stats(user, 'T', cascade => true)

PL/SQL procedure successfully completed.

sokrates@11.2 > variable n number
sokrates@11.2 > exec :n := 1

PL/SQL procedure successfully completed.

sokrates@11.2 > select /*+BIND_AWARE */ * from t where i=:n;

no rows selected

sokrates@11.2 > select /*ACS_1 */ * from t where i=:n;

no rows selected

sokrates@11.2 > select /*placebo */ * from t where i=:n;

no rows selected

sokrates@11.2 > select is_bind_sensitive, is_bind_aware, sql_text from v$sql
where sql_text like 'select /*ACS_1%' or sql_text like 'select /*+BIND%' or sql_text like 'select /*placebo%';

IS_ IS_
--- ---
SQL_TEXT
---------------------------------
Y N
select /*placebo */ * from t where i=:n

Y N
select /*ACS_1 */ * from t where i=:n

Y Y
select /*+BIND_AWARE */ * from t where i=:n

sokrates@11.2 > REM stats on t suffice to become bind aware with hint "BIND_AWARE", ACS_1 behaves like placebo


About these ads