Hi,
Oracle: 10.2.0.1.0 on Linux
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
I had an index on a column whose values are greatly skewed; 25,000,000 rows have the one particular value value, and a small number have different values. This data distribution is a realistic scenario in production. Similar selects varied greatly in performance due to this, so, I've added a histogram to the some_column column (and also to some_date_column, but I don't think that has any added value):
exec dbms_stats.gather_table_stats (ownname=> 'PR_PERF', tabname => 'X', estimate_percent =>dbms_stats.auto_sample_size,method_opt=>'for columns some_column, some_date_column',cascade => true,degree => 7);
After doing so, the following selects began to run perfectly:
select * from X where some_column = 'commonValue' order by some_date_column asc;
select * from X where some_column = 'uncommonValue' order by some_date_column asc;
BUT, I'm using a persistence framework (Hibernate), and it seems it hands in bound-variable statements. I'm not actually sure whats going on under-the-hood, but Enterprise Manager shows statements like so:
select * from X where some_column = :1 order by some_date_column asc;
As a result, the first time that query is issued to the server, an execution plan is created, and that plan is always used for subsequent calls. The histogram isn't having an influence on the CBOs choices like it did for non-bound statements.
As suggested in
2532711 (and various other resources I've found) I set cursor_sharing=similar - but to no avail. Nothing seems to have changed.
Can anyone advise me on what's happening and why cursor_sharing=similar isn't solving this issue? From what I've learned, I thought that was a well-known fix for this issue.
Thanks,
Cormac