Execution plan shows 3 times less CPU cost but query now takes 4 times more time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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?
    Attached Files Attached Files
    lucky

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Cool

    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

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by LKBrwn_DBA View Post
    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

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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by mahajanakhil198 View Post
    ... 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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    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 04:07 AM.
    lucky

  8. #8
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Will try to resolve upload failure asap.
    Last edited by mahajanakhil198; 02-25-2013 at 04:08 AM.
    lucky

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Was able to upload it using basic uploader.
    lucky

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width