Sokrates on Oracle

A Hint without +

Posted by Matthias Rogel on 4. June 2011

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

Advertisements

2 Responses to “A Hint without +”

  1. some weeks ago there was an interesting discussion on Richard Foote’s blog (http://richardfoote.wordpress.com/2011/04/20/blks_gets_per_access-index-rebuild-criteria-twisted-logic/#comment-12417):
    David Aldridge stated “Interesting quote from Page 728 of that book [i.e. Oracle Tuning: The Definitive Reference]: ‘Even if index rebuilding were to be proven as [sic] a useless activity, the Placebo effect on the end users is enough to justify the task.’. It opens up a whole new class of performance tuning … perhaps it could be called ‘Faith-based Tuning’.” And Jonathan Lewis commented: “If the only effect is a placebo effect couldn’t you just tell the users that you had rebuilt the indexes rather than actually doing it ?”
    Perhaps this ACS_1-“hint” is also used for its Placebo effect on the end users (or on the developers and DBAs).

    Regards
    Martin Preiss

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: