Execution plan shows 3 times less CPU cost but query now takes 4 times more time

[mahajanakhil198]

I would stop focusing on cpu cost and look deeper into consistent gets a.k.a. buffer_gets - the slowest component of the system is I/O, reduce I/O and query will perform better.

I understand your point. When I started this thread, I used fictitious table names but the execution plans and every other information was true.

Please find the trace file attached. I did the following test:
1. COACH_OPERATIONS table is the culprit and has around 52 million records. So, I created a replica named 'Akhil_Test' of this table with no indexes (so, if a query is issued on this table, it would result in full table scan).
2. Issued queries on both tables and traced the session using events 10046.


I flushed buffer cache and made observed the following:
1. On replicated table, query takes 44 seconds to execute.
2. Flushed buffer cache again. On original table that has all indexes, query takes 24 seconds to execute.
Even the trace file output shows that the query on original table takes less time.

What I want to know:
Is there any scope to reduce further the execution time of query on original table?


************It is not allowing me to upload the trace file. Neither does it give me the reason for failure, it just shows an exclamtion mark. It is just a 14KB txt file. Can I upload the file somewhere else?***************