Forum Stats

  • 3,874,727 Users
  • 2,266,768 Discussions
  • 7,911,960 Comments

Discussions

column property BIND_AWARE to control ACS (adaptive cursor sharing)

Rainer Stenzel
Rainer Stenzel Member Posts: 64 Bronze Badge
edited Jan 17, 2019 5:31AM in Database Ideas - Ideas

To improve cursors using bind variables where literal usage would be more appropriate by avoiding adaptive cursor sharing instability a column property as BIND_AWARE (syntax example: alter table sys.obj$ modify (type# BIND_AWARE)) could force bind awareness (is_bind_aware=YES) immediately for cursors having access or filter predicates on such columns or even force distinct child cursors for any occurring bind value (within an upper limit).

This might mitigate the instable (unpredictable) adaptive cursor sharing behavior.

I can imagine at least to shapes

ADAPTIVE

could force any cursor using this column to behave as hinted with BIND_AWARE

skipping the adaptive cursor sharing monitoring phase as described in

https://blogs.oracle.com/optimizer/how-do-i-force-a-query-to-use-bind-aware-cursor-sharing

FORCE

could modify any cursor using this column to make them non sharable for different bind values to avoid the ACS overhead anyway (by replacing the bind variable with the actual bind value literal or adding it as a comment)

example

select * from sys.obj$ where type# = :1

may end up as

select * from sys.obj$ where type# = 57

select * from sys.obj$ where type# = 5

resp.

select * from sys.obj$ where type# = :1 /*57*/

select * from sys.obj$ where type# = :1 /*5*/

when called with 57 (EDITIONS) and 5 (SYNONYMS) for bind variable :1.

Find BIND_AWARE candidates:

select table_name,column_name from user_ind_columns where column_position=1

intersect

select table_name,column_name from USER_TAB_HISTOGRAMS

join USER_TAB_COLUMNS using (table_name,column_name)

where histogram<>'NONE'

-- and histogram='FREQUENCY'  -- FORCE should be applied to FREQUENCY histograms only

group by table_name,column_name

having count(*)<10  -- FORCE should be applied to a rather low number of distinct values only

ctrieb
2 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    A cursor is a memory object. Is the suggestion ment for a single cursor or for all cursors on the instance? I don't understand how a table manipulation (alter table !?) would influence single cursor behaviour. Do cursors appear in sys.obj$?

    The only way I see forcing cursors to be bind aware whould be somehow creating a profile/baseline for a SQL statement and flagging that profile.

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 64 Bronze Badge

    A cursor is a memory object. Is the suggestion ment for a single cursor or for all cursors on the instance? I don't understand how a table manipulation (alter table !?) would influence single cursor behaviour. Do cursors appear in sys.obj$?

    The only way I see forcing cursors to be bind aware whould be somehow creating a profile/baseline for a SQL statement and flagging that profile.

    As other information such column property should be regarded in the parsing/optimization phase and emphasize/force the bind sensitivity/awareness for skewed low cardinality columns.

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    As other information such column property should be regarded in the parsing/optimization phase and emphasize/force the bind sensitivity/awareness for skewed low cardinality columns.

    Can you clarify? I really don't get it. i understand the intention, but I completly do not understand how your proposal is supposed to work.

    I assume you suggest a new column BIND_AWARE in sys.obj$. And then an UPDATE to that column (update would be dml not ddl as an alter table statement). The update probably would be done via some supplied dbms-package.

    But in sys.obj$ are no cursors stored. Only tables. And I think columns are also not in there, but somewhere else.

    So I assume you want to influence the bind sensitivty used for adaptive cursor sharing based upon the table+column with which that bind parameter is compared.

    But would that be proper for other comparisons, like range comparisons instead of equality comparisons? Also not every bind parameter is directly compared to a column. The  suggestion then could not influence such other scenarios. You would only be able to influence the behaviour on an instance wide level. Typically we would try to do surgical changes. Influence the behaviour of a single specifc cursor instead of all cursors that happen to use this column or not.

    And what about statements that have an equality check for two different columns. One marked as adaptive allowed and the other one not. Should a new plan be considered? Should the new plan maybe only be sensitive for 1 of the columns, but not for the other?

    It seems as if you have a very specific problem in mind. Can you give the example?