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.

What should be done in such a scenario?

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.

While I was traveling, I realized that I have not attached execution plans. The file is attached now.

I will see the buffer gets once I have access to database servers again. I will share that information tomorrow.

Next question is: Does the optimizer select execution plan solely on the basis of CPU cost?

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.
:rolleyes:

Quote:

---

Originally Posted by LKBrwn_DBA

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.
:rolleyes:

---

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

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2652 | 3241 (1)| 00:00:39 |
| 1 | HASH UNIQUE | | 34 | 2652 | 3241 (1)| 00:00:39 |
|* 2 | HASH JOIN | | 34 | 2652 | 3240 (1)| 00:00:39 |
|* 3 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST1 | 8855 | 371K| 2159 (1)| 00:00:26 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP AND | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | BITMAP_FN1_COACH_LAST_EVENT_DT | | | | |
| 7 | BITMAP OR | | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 12 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
| 13 | MERGE JOIN CARTESIAN | | 17130 | 585K| 1080 (1)| 00:00:13 |
| 14 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST3 | 1 | 17 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_DEPOT_DIV_ZONE | 1 | | 1 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 19159 | 336K| 1078 (1)| 00:00:13 |
|* 17 | TABLE ACCESS FULL | AKHIL_TEST2 | 19159 | 336K| 1078 (1)| 00:00:13 |
------------------------------------------------------------------------------------------------------------------

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

Quote:

---

Originally Posted by mahajanakhil198

... 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.

Quote:

---

Originally Posted by PAVB

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

Will try to resolve upload failure asap.

Was able to upload it using basic uploader.

The fact that COACH_OPERATIONS and 'Akhil_Test' tables have the same rows doesn't mean they are identical to Oracle.

Have you at least exported statistics from COACH_OPERATIONS and imported them into AKHIL_TEST table?

Thanks for your advice. You have always been of help, even in the past. I collected fresh statistics on both tables using dbms_stats.gather_table_stats and then started the test. The difference between 2 tables is that first has around 10 indexes and second has only primary key. Will exporting the statistics from original to replicated table create any difference?

However, after lot of investigation, I found that the column COACH_CURR_LOCATION_ID had cardinality of around 200. I was able to trim the execution of query to 2 seconds by creating a bitmap index on join column COACH_CURR_LOCATION_ID of heavy AKHIL_TEST1 table.

I have removed the last trace file as my team member just pushed me to do that. However, for your information and as it is moral responsibility, I am attaching the new execution plan with fictitious table names.