1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

optimizing query and identify the bottleneck

Discussion in 'SQL PL/SQL' started by andrewscharles89, Jul 1, 2014.

  1. andrewscharles89

    andrewscharles89 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    i have a query which takes 2 mins to exceute in qa environmnet
    and the same query takes 10 mins in test environment.


    in qa environmnet

    Code: [Select all] [Show/ hide]
    Elapsed: 00:00:02.86

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1402972727

    --------------------------------------------------------------------------------
    -----------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU
    )| Time | Pstart| Pstop |

    --------------------------------------------------------------------------------
    -----------------------------

    | 0 | DELETE STATEMENT | | 5 | 230 | 18 (0
    )| 00:00:01 | | |

    | 1 | DELETE | MOD_RP_TCHFCST_BO | | |
    | | | |

    | 2 | PARTITION RANGE SINGLE| | 5 | 230 | 18 (0
    )| 00:00:01 | 125 | 125 |

    |* 3 | TABLE ACCESS FULL | MOD_RP_TCHFCST_BO | 5 | 230 | 18 (0
    )| 00:00:01 | 125 | 125 |

    --------------------------------------------------------------------------------
    -----------------------------


    Statistics
    ----------------------------------------------------------
    63 recursive calls
    9 db block gets
    40 consistent gets
    35 physical reads
    2488 redo size
    483 bytes sent via SQL*Net to client
    29847 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    0 rows processed




    in test environment. the same query takes 10 mins to execute

    Code: [Select all] [Show/ hide]
    Elapsed: 00:12:06.73

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4154840340

    --------------------------------------------------------------------------------
    ----------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost
    (%CPU)| Time | Pstart| Pstop |

    --------------------------------------------------------------------------------
    ----------------------------------

    | 0 | DELETE STATEMENT | | 1 | 104 | 0
    (0)| 00:00:01 | | |

    | 1 | DELETE | MOD_RP_TCHFCST_BO | | |
    | | | |

    | 2 | PARTITION RANGE SINGLE| | 1 | 104 | 0
    (0)| 00:00:01 | 125 | 125 |

    | 3 | INLIST ITERATOR | | | |
    | | | |

    |* 4 | INDEX RANGE SCAN | IDXU_MOD_RP_TCHFCST_BO | 1 | 104 | 0
    (0)| 00:00:01 | 125 | 125 |

    --------------------------------------------------------------------------------
    ----------------------------------



    Statistics
    ----------------------------------------------------------
    485 recursive calls
    12 db block gets
    1685 consistent gets
    48 physical reads
    2392 redo size
    479 bytes sent via SQL*Net to client
    29847 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    11 sorts (memory)
    0 sorts (disk)
    0 rows processed


    DELETE FROM Mod_RP_TCHFCST_BO WHERE SCENARIOS_Key = AND GEOGRAPHY_Key IN ( )
    AND LEGALENTITY_Key IN ( ) AND PRODUCT_Key IN () AND PROFITCENTER_Key =
    AND TRADECHANNEL_Key IN () AND ACCOUNT_Key = AND TIMEPERIOD_Key =

    i have unique index on all the where conds

    question is why the same query takes time in different environment
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    http://docs.oracle.com/cd/E29505_01/server.1111/e16638/optimops.htm

    Access Path

    1) Different access methods: FTS and INDEX (RANGE SCAN)
    FTS :
    Index RS :

    http://docs.oracle.com/cd/E29505_01/server.1111/e16638/optimops.htm#i45075
    Note: Also pay attention to statistics of runtime of the first and second request

    How many percent of records get to selection on your predicates?
    If more than 20% (as a rule for big tables), then the index is ineffective.
    In this case there will be an excess input-output