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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

Attached are 2 execution plans of a query - one is with index BITMAP_COACH_STATUS_FLAG and one is without the aforementioned index.

The execution time of query was 55 seconds. Using the execution plan, I created a BITMAP index mentioned above. The CPU cost went down from 383K to 96K.
Interestingly using the index, the CPU cost reduced 3-4 times (96K) but the execution time increased 5 times (4 minutes 47 seconds). When I used the NO_INDEX hint, the CPU cost again went up to 383K but the execution time went down to 55 seconds.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Perhaps if you could find a column (indexed) where you can join the table AKHIL_TEST2 with one of the other two tables, you would avoid the "| 7 | MERGE JOIN CARTESIAN . . ." from the plan(s) and get better performance.

"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Perhaps if you could find a column (indexed) where you can join the table AKHIL_TEST2 with one of the other two tables, you would avoid the "| 7 | MERGE JOIN CARTESIAN . . ." from the plan(s) and get better performance.

MERGE JOIN CARTESIAN was unavoidable. However, I was able to bring down the CPU cost further to approx. 3200 by creating a composite function based BITMAP index on columns (EXTRACT(YEAR FROM COACH_LAST_EVENT_DT), EXTRACT(MONTH FROM COACH_LAST_EVENT_DT)). Afterwards, I flushed all the buffers in buffer cache to find the worst case execution time of query - the execution time of query is now 16 seconds which is okay, but I hope that I could reduce it further!!!!

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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?***************

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.