so we have many Oracle views that we expose to other teams to work with, and they run queries against the views to extract data.
Recently we realized that one of the views we expose, a user did a select * with a date range and the query just doesn't return in a timely fashion. After investigation, we decided to 'optimize' the view by converting a select subquery into a left join, something that I know normally improves query performance.
Previous view definition :
select a.date, (select name from table_b b where b.id = a.id), a.id
from table_a a
New view definition :
select a.date, b.name, a.id
from table_a a left join table_b b on a.id = b.id
We tested it with the user and his queries are now much more performant so the change was rolled out to production. A day later we realized another user had been using this view within some complex query, and his query went from running 2 hours everyday to > 7 hours or not completing at all.
So i guess my question is, how do I deal with this tuning issue, where improving one query's performance degrades another query's performance? I'm in the process of rollback such that I can examine the two different query plans, but I'm not sure what insight I can gain from the plan differences. I checked the table statistics and they all look good.
"user did a select * with a date range ".
Date range scans are notoriously hard to tune. A plan which is great for date '2018-04-01' to date '2018-04-02' may well suck for date '2017-04-01' to date '2018-04-01'. And of course vice versa.
So what you may be suffering from here is that your user is using bind variables for the date range values. Bind variables are normally good for performance because they allow Oracle to re-use the same execution plan for all executions of the query with any value for those variables. This is a good thing when the pertinent values have a normal distribution. Then we save the cost of a hard parse and use an efficient path. This is called Bind Variable Peeking.
However, when the data is an uneven distribution or when we are are specifying ranges we need a different strategy. The overhead of a hard parse is trivial compared to the cost of using an indexed read to retrieve 20% of the rows in a table. So you need a different approach, one which doesn't rely on bind variables. Ideally you can work with your users, understand what they're doing and help them write better queries. However, the Oracle database does have features like Adaptive Cursors which allows the database to assess whether the cached plan is still good for new values of bind variables. This doesn't guarantee good performance but can help in situations where we have users running ad hoc queries. Find out more.
" the underlying tables was partitioned by date and also indexed by date hence I believe the date range should not be an issue."
Belief is not the same as proof. If the date range is within a single partition then maybe it's not the issue. If the queried range spans several partitions then it's a potential culprit. Consider: if your table is partitioned into one day sections then a date range of date '2017-04-01' to date '2018-04-01' would scan 365 partitions. Partition pruning won't do much for you then. But if you don't think it's worth investigating that's cool.
"my general question was how to tune one thing without breaking another (that you may not be aware of)"
As I think you know already, this is not possible. The best we can hope for is to tune a query to perform optimally under the conditions we know about. If it were possible to write a query so that it executed perfectly in any scenario then all those Oracle tuning consultants would not make the fine livings that they do.