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.
Please trace both executions, check buffer_gets, I'm pretty sure the version of the query with index is going to show a much larger number. By the way, couldn't see the exectuion plans.
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!!!!
I am not able to attach new execution plan, so I am pasting it here:
SQL> l
1 SELECT DISTINCT(B.COACH_NUM) FROM AKHIL_TEST1 A,AKHIL_TEST2 B,AKHIL_TEST3 C
2 WHERE A.COACH_ID = B.COACH_ID
3 AND A.COACH_CURR_LOCATION_ID = C.LOCATION_ID
4 AND C.RLY_ZONE_CD = 'WR'
5 AND C.RLY_DIV_CD = 'BCT'
6 AND C.RLY_DEPOT_CD = 'BCT'
7 AND EXTRACT(MONTH FROM A.COACH_LAST_EVENT_DT) = 9
8 AND EXTRACT(YEAR FROM A.COACH_LAST_EVENT_DT) = 2012
9 AND B.COACH_TYP_CD = 'GS'
10* AND A.COACH_STATUS_FLAG IN ('SHOPCH','SHONRK','WTSHOP','INSHOP','SHONRN')
Elapsed: 00:00:14.91
Execution Plan
----------------------------------------------------------
Plan hash value: 755460096
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."COACH_ID"="B"."COACH_ID" AND "A"."COACH_CURR_LOCATION_ID"="C"."LOCATION_ID")
3 - filter("A"."COACH_STATUS_FLAG"='INSHOP' OR "A"."COACH_STATUS_FLAG"='SHONRK' OR
"A"."COACH_STATUS_FLAG"='SHONRN' OR "A"."COACH_STATUS_FLAG"='SHOPCH' OR "A"."COACH_STATUS_FLAG"='WTSHOP')
6 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("COACH_LAST_EVENT_DT"))=2012 AND EXTRACT(MONTH FROM
INTERNAL_FUNCTION("COACH_LAST_EVENT_DT"))=9)
8 - access("A"."COACH_STATUS_FLAG"='INSHOP')
9 - access("A"."COACH_STATUS_FLAG"='SHONRK')
10 - access("A"."COACH_STATUS_FLAG"='SHONRN')
11 - access("A"."COACH_STATUS_FLAG"='SHOPCH')
12 - access("A"."COACH_STATUS_FLAG"='WTSHOP')
15 - access("C"."RLY_DEPOT_CD"='BCT' AND "C"."RLY_DIV_CD"='BCT' AND "C"."RLY_ZONE_CD"='WR')
17 - filter("B"."COACH_TYP_CD"='GS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17524 consistent gets
17481 physical reads
0 redo size
305 bytes sent via SQL*Net to client
251 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
... bring down the CPU cost further to approx. 3200...
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.
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?***************
Last edited by mahajanakhil198; 02-25-2013 at 05:07 AM.
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.