Oracle SQL Tuning Advisor with changing query

Question

We have an C# app which will search the DB for activity in a couple of tables. The user can specify to search for certain criteria, which in turn build upon the base SQL query.

This query takes a very long time to execute and we wanted to use Oracle's Tuning Advisor on this query. Our question is if this tuning advisor will only work for the given query?

For example, if we do the tuning for applications the base query (they don't specify any search criteria):

SELECT Actv, FullName, DOB FROM Pers;

Does the created SQL Profile only apply to that statement? What if they specify search criteria so the statement grows to:

SELECT Actv, FullName, DOB FROM Pers WHERE Actv = 'Foo' AND DOB >= :Date;

Would we need to create an another SQL profile for this query or would the optimizer used part of the baseline SQL Profile in it's creation of the execution plan?

Answer 1

As a rule of thumb each query is optimized separately. For example, the indexes the Tuning Advisor may suggest may be great for some queries, but may end up being useless for other queries. The same can be said about heap clustering and data partitioning.

A naive solution would be to add all the indexes that all queries need. Even though this can be done, too many indexes will slow down all data modification SQL statements. That is, all INSERTs, UPDATEs, and DELETEs would become slow and expensive.

So, what should you do? That depends on your specific case. My [very personal] rule of thumb is not to exceed 10 indexes per table.

Of course, if your table has a high rate of update/insert/delete I would consider a lower limit.

On the flip side, if you table data has a low/none modification rate (a mostly read-only table), then you can raise this limit and even maybe add as many indexes as the Tuning Advisor suggests.

In the end, the rule of thumb is just a guide. You will need to try and see what's the best balance between fast SELECTs and fast UPDATEs.