Random ramblings
Racer I., February 28, 2017 - 12:05 pm UTC
Assuming the tables have some additional key, say customer or order, they should have indexes like (tenant,customer) or (tenant,order) and queries should provide both. I think per default the analyzer will provide histograms per column, so there will be big and small tenants, but you can tell it to compute extended heuristics (multi-column). Then all queries should be considered small (say orders per customer) and it should always go for the index.
https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt exec dbms_stats.gather_tables_stats('user', 'table', method_op => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254 (tenant,customer)');
Caveat : never tried it myself.
Alternatively you can gather statistics without a histogram on the tenant column. Of course Oracle can then decide to use FULL SCANS for all tentants.
Regards,
Racer I.
Cheating with statistics worked like a charm
Martin Schayna, March 06, 2017 - 12:26 pm UTC
After some poking around (except partitioning due to licensing issues), I found cheating statistics as a super useful. I ended up with:
- create additional index per tenantId + timeStart (very common column in the where clause of almost all queries)
- disable histogram for tenantId index, i.e.
dbms_stats.gather_table_stats('USER', 'TABLE', method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 TENANTID'
);
All queries have meaningful plan and performance, disk read throughput decreased and users are happy now.
Many thanks to both of you!
Martin
March 07, 2017 - 2:14 am UTC
Glad to hear about the good outcome