This is a follow up to my previous post with a real world example of how much difference indexes can make on tables that do not have them.
I looked at an AWR report of a peak hour on a busy database and noticed sql_id 028pmgvcryk5n:
The top SQL is a PL/SQL call so it does not have a plan but 028pmgvcryk5n stood out because it was taking 11.24% of the total elapsed time and running over 2 seconds (2.23) per execution. Most queries on this system run in hundredths of a second like .02 seconds.
I looked at this query’s plan and it included two tables with full scans on both:
I put indexes on each table and the new plan looks like this:
With indexes it ran the query in about 1 millisecond:
Without indexes it ran from 600 to 2000 milliseconds:
I guess I have a simple point. Indexes can make a big difference. A query that runs in 600 milliseconds may be good enough in some cases but if it can run in 1 millisecond it is worth putting on the right indexes.
Bobby
p.s. I used my sqlstat.sql and vsqlarea.sql scripts to get the execution history with and without the indexes. I used getplans.sql to get the plan without indexes from the AWR and extractplansqlid.sql to get the plan with the indexes from the V$ views. The query ran too fast to show up on the AWR once the indexes were in place so that is why I used the V$ queries to get information about the query after adding the indexes.
