Hi we are using version 12.1.0.2.0 of Oracle Exadata. I came across a situation in which a column(C1) of TAB1 having skewed data in it and also having histogram present. Tab1 holds ~20million rows , out of which ~97k holds unique C1 values but rest all are holding a default value i.e. 'NA'. This column C1 is not null with default 'NA'.
So when the query(as below) using bind variable as a filter to this column C1 gets a most popular value(i.e 'NA') its using index1(composite unique index on c4,c3,c1) or say execution path-1 but when it gets a less popular value it used index2(which is on C1) or execution path-2. ~90% of the execution happens with the most popular bind value(i.e 'NA') and opts the index1 path but when any one input bind came as the less popular value and the query hard parsed , it opts for index2 path (which works fine), but then next few execution for the most popular incoming bind value(i.e 'NA') suffers badly when they execute with the index2. It doing the damage till the ACS triggers and get the plan reverted back to index1 path.
I know histograms+binds doesn't work well together even in current version. So few options we are thinking of ,if we can use literals here, but considering the query executes million of time , it may negatively affect the shared pool. Again thought of if removing histogram , but then considering the column holds skewed values and it must be helping other queries for doing correct estimation when applying filter on this column, so we are hesitating for that option.
Few team members suggesting to set the cursor_sharing to FORCE and use the literals in the query, so that share pool fragmentation wont happen and also the histogram can be utilized. Want to know from experts , if this is correct approach to handle this scenario? Or any negative side of this?
select * from tab1 where c3=:1 and c4=:2 and c1=:3;
index1 - is unique index on (c4,c3,c1);
index2- is btree non unique index on (c1)