Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    20

    Question Unanswered: Please help me to understand why the query works longer on server_A than on server_B

    Hi all,

    Please help me to understand why the query works longer on server_A than on server_B


    This is explain plan from server_A. The query seems to run forever.


    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);

    Code:
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                         | Name                     | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                  |                          |     1 |   870 | 37945 |
    |   1 |  HASH GROUP BY                                    |                          |     1 |   870 | 37945 |
    |   2 |   NESTED LOOPS OUTER                              |                          |     1 |   870 | 37944 |
    |   3 |    VIEW                                           |                          |     1 |   861 | 37943 |
    |   4 |     MAT_VIEW ACCESS BY INDEX ROWID                | COST_DETAIL_ITEMS        |     4 |   116 |     4 |
    |   5 |      NESTED LOOPS                                 |                          |     1 |   561 | 37826 |
    |   6 |       NESTED LOOPS                                |                          |     1 |   532 | 37822 |
    |   7 |        NESTED LOOPS                               |                          |     1 |   505 | 37821 |
    |   8 |         NESTED LOOPS                              |                          |     1 |   492 | 37818 |
    |   9 |          NESTED LOOPS                             |                          |     1 |   479 | 37816 |
    |  10 |           NESTED LOOPS                            |                          |     1 |   449 | 37815 |
    |  11 |            NESTED LOOPS                           |                          |     3 |  1239 | 37806 |
    |  12 |             NESTED LOOPS                          |                          |     3 |  1047 | 37803 |
    |  13 |              NESTED LOOPS                         |                          |     3 |   966 | 37800 |
    |  14 |               HASH JOIN                           |                          |     3 |   885 | 37797 |
    |  15 |                NESTED LOOPS OUTER                 |                          |     1 |   269 | 37761 |
    |  16 |                 NESTED LOOPS                      |                          |     1 |   242 | 37760 |
    |  17 |                  NESTED LOOPS OUTER               |                          |     1 |   215 | 37759 |
    |  18 |                   NESTED LOOPS                    |                          |     1 |   182 | 37756 |
    |  19 |                    NESTED LOOPS                   |                          |     3 |   501 | 37752 |
    |  20 |                     HASH JOIN                     |                          |   580 | 76560 | 37171 |
    |  21 |                      VIEW                         | VW_SQ_2                  |   580 | 22040 | 36148 |
    |  22 |                       HASH GROUP BY               |                          |   580 | 44660 | 36148 |
    |  23 |                        MERGE JOIN                 |                          |   156M|    11G| 24560 |
    |  24 |                         SORT JOIN                 |                          |   220K|  6680K|  2178 |
    |  25 |                          INDEX FAST FULL SCAN     | DUC_COMP_IDX1            |   220K|  6680K|   288 |
    |  26 |                         FILTER                    |                          |       |       |       |
    |  27 |                          SORT JOIN                |                          |   283K|    12M|  3491 |
    |  28 |                           INDEX FAST FULL SCAN    | DUM_DEPART_TIME_IDX      |   283K|    12M|   201 |
    |  29 |                      HASH JOIN                    |                          |   283K|    25M|  1021 |
    |  30 |                       MAT_VIEW ACCESS FULL        | DEDICATED_UNIT_MOVE_TYPE |     8 |    88 |     3 |
    |  31 |                       MAT_VIEW ACCESS FULL        | DEDICATED_UNIT_MOVE      |   283K|    22M|  1016 |
    |  32 |                     MAT_VIEW ACCESS BY INDEX ROWID| DEDICATED_UNIT_COST      |     1 |    35 |     1 |
    |  33 |                      INDEX UNIQUE SCAN            | DUC_PK                   |     1 |       |     0 |
    |  34 |                    MAT_VIEW ACCESS BY INDEX ROWID | DEDICATED_LOAD_MOVE      |     1 |    15 |     2 |
    |  35 |                     INDEX RANGE SCAN              | DLM_DUM_FK_I             |     1 |       |     1 |
    |  36 |                   MAT_VIEW ACCESS BY INDEX ROWID  | DEDICATED_UNIT_MOVE      |     1 |    33 |     3 |
    |  37 |                    INDEX RANGE SCAN               | DUM_DEADHEAD_IDX2        |     1 |       |     2 |
    |  38 |                  MAT_VIEW ACCESS BY INDEX ROWID   | DEDICATED_LOAD           |     1 |    27 |     1 |
    |  39 |                   INDEX UNIQUE SCAN               | DLO_PK                   |     1 |       |     0 |
    |  40 |                 MAT_VIEW ACCESS BY INDEX ROWID    | ORGANIZATIONS            |     1 |    27 |     1 |
    |  41 |                  INDEX UNIQUE SCAN                | ORG_PK                   |     1 |       |     0 |
    |  42 |                VIEW                               | VW_SQ_1                  |  1493 | 38818 |    35 |
    |  43 |                 HASH GROUP BY                     |                          |  1493 | 22395 |    35 |
    |  44 |                  INDEX FAST FULL SCAN             | DPU_COMP_IDX2            | 26995 |   395K|    33 |
    |  45 |               MAT_VIEW ACCESS BY INDEX ROWID      | DEDICATED_PROGRAM_UNIT   |     1 |    27 |     1 |
    |  46 |                INDEX UNIQUE SCAN                  | DPU_PK                   |     1 |       |     0 |
    |  47 |              MAT_VIEW ACCESS BY INDEX ROWID       | ORGANIZATIONS            |     1 |    27 |     1 |
    |  48 |               INDEX UNIQUE SCAN                   | ORG_PK                   |     1 |       |     0 |
    |  49 |             MAT_VIEW ACCESS BY INDEX ROWID        | DEDICATED_PROGRAM        |     1 |    64 |     1 |
    |  50 |              INDEX UNIQUE SCAN                    | DPR_PK                   |     1 |       |     0 |
    |  51 |            MAT_VIEW ACCESS BY INDEX ROWID         | LOAD_COST_DETAILS        |     1 |    36 |     3 |
    |  52 |             INDEX RANGE SCAN                      | LCL_COMP_I1              |     1 |       |     2 |
    |  53 |              SORT AGGREGATE                       |                          |     1 |    12 |       |
    |  54 |               FILTER                              |                          |       |       |       |
    |  55 |                FIRST ROW                          |                          |     5 |    60 |     3 |
    |  56 |                 INDEX RANGE SCAN (MIN/MAX)        | LCL_COMP_I1              |     5 |    60 |     3 |
    |  57 |           MAT_VIEW ACCESS BY INDEX ROWID          | ORGANIZATIONS            |     1 |    30 |     1 |
    |  58 |            INDEX UNIQUE SCAN                      | ORG_PK                   |     1 |       |     0 |
    |  59 |          MAT_VIEW ACCESS BY INDEX ROWID           | LOADS                    |     1 |    13 |     2 |
    |  60 |           INDEX UNIQUE SCAN                       | LOD_PK                   |     1 |       |     1 |
    |  61 |         MAT_VIEW ACCESS BY INDEX ROWID            | LOAD_DETAILS             |     1 |    13 |     3 |
    |  62 |          INDEX RANGE SCAN                         | LDD_POINT_TYPE_IDX       |     1 |       |     2 |
    |  63 |        MAT_VIEW ACCESS BY INDEX ROWID             | ORGANIZATIONS            |     1 |    27 |     1 |
    |  64 |         INDEX UNIQUE SCAN                         | ORG_PK                   |     1 |       |     0 |
    |  65 |       INDEX RANGE SCAN                            | CDM_LCL_FK               |     5 |       |     2 |
    |  66 |    MAT_VIEW ACCESS BY INDEX ROWID                 | MAP_ORG_COMPANY          |     1 |     9 |     1 |
    |  67 |     INDEX UNIQUE SCAN                             | MO_PK                    |     1 |       |     0 |
    --------------------------------------------------------------------------------------------------------------
     
    Note
    -----
       - 'PLAN_TABLE' is old version
     
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    "CORE	10.2.0.3.0	Production"
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production

  2. #2
    Join Date
    Sep 2013
    Posts
    20
    This is from server_B. The query works fine enough.



    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);

    Code:
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name                     | Rows  | Bytes | Cost  |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |                          |     1 |   869 |  2626K|
    |   1 |  HASH GROUP BY                             |                          |     1 |   869 |  2626K|
    |   2 |   NESTED LOOPS OUTER                       |                          |     1 |   869 |  2626K|
    |   3 |    VIEW                                    |                          |     1 |   860 |  2626K|
    |   4 |     FILTER                                 |                          |       |       |       |
    |   5 |      HASH JOIN                             |                          |   505K|   263M|  1154K|
    |   6 |       HASH JOIN                            |                          |   129K|    63M|   821K|
    |   7 |        HASH JOIN                           |                          |   118K|    56M|   705K|
    |   8 |         INDEX FAST FULL SCAN               | ORG_NETWORK_IDX          |   113K|  2984K|   181 |
    |   9 |         HASH JOIN                          |                          |   118K|    53M|   702K|
    |  10 |          HASH JOIN                         |                          |   118K|    52M|   547K|
    |  11 |           HASH JOIN                        |                          | 50670 |    20M|   454K|
    |  12 |            MAT_VIEW ACCESS FULL            | DEDICATED_UNIT_COST      |   220K|  7307K|   755 |
    |  13 |            HASH JOIN                       |                          |    18M|  6883M|   101K|
    |  14 |             HASH JOIN RIGHT OUTER          |                          |   122K|    43M| 21266 |
    |  15 |              MAT_VIEW ACCESS FULL          | DEDICATED_UNIT_MOVE      |   136K|  4397K|  1201 |
    |  16 |              HASH JOIN RIGHT OUTER         |                          |   122K|    39M| 17746 |
    |  17 |               INDEX FAST FULL SCAN         | ORG_NETWORK_IDX          |   113K|  2984K|   181 |
    |  18 |               HASH JOIN                    |                          |   122K|    36M| 15484 |
    |  19 |                INDEX FAST FULL SCAN        | DLO_COMP_IDX1            |   121K|  3196K|   184 |
    |  20 |                HASH JOIN                   |                          |   131K|    35M| 13245 |
    |  21 |                 MAT_VIEW ACCESS FULL       | DEDICATED_LOAD_MOVE      |   129K|  1896K|   245 |
    |  22 |                 HASH JOIN                  |                          |   290K|    74M|  8982 |
    |  23 |                  MAT_VIEW ACCESS FULL      | ORGANIZATIONS            |   113K|  3315K|   553 |
    |  24 |                  HASH JOIN                 |                          |   290K|    66M|  4762 |
    |  25 |                   INDEX FAST FULL SCAN     | ORG_NETWORK_IDX          |   113K|  2984K|   181 |
    |  26 |                   HASH JOIN                |                          |   290K|    58M|  1303 |
    |  27 |                    MAT_VIEW ACCESS FULL    | DEDICATED_PROGRAM        |  1490 | 95360 |    10 |
    |  28 |                    HASH JOIN               |                          |   290K|    40M|  1292 |
    |  29 |                     INDEX FAST FULL SCAN   | DPU_COMP_IDX2            | 28396 |   748K|    41 |
    |  30 |                     HASH JOIN              |                          |   290K|    33M|  1250 |
    |  31 |                      VIEW                  | VW_SQ_1                  |  2672 | 69472 |    43 |
    |  32 |                       HASH GROUP BY        |                          |  2672 | 26720 |    43 |
    |  33 |                        INDEX FAST FULL SCAN| DPU_COMP_IDX2            | 28396 |   277K|    41 |
    |  34 |                      HASH JOIN             |                          |   287K|    25M|  1206 |
    |  35 |                       MAT_VIEW ACCESS FULL | DEDICATED_UNIT_MOVE_TYPE |     8 |    88 |     3 |
    |  36 |                       MAT_VIEW ACCESS FULL | DEDICATED_UNIT_MOVE      |   287K|    22M|  1202 |
    |  37 |             VIEW                           | VW_SQ_2                  |    42M|  1017M|  3390 |
    |  38 |              HASH GROUP BY                 |                          |    42M|  2076M|  3390 |
    |  39 |               HASH JOIN                    |                          |    42M|  2076M|  2068 |
    |  40 |                INDEX FAST FULL SCAN        | DUC_COMP_IDX1            |   220K|  5588K|   312 |
    |  41 |                INDEX FAST FULL SCAN        | DUM_DEPART_IDX2          |   287K|  7007K|   381 |
    |  42 |           MAT_VIEW ACCESS FULL             | LOAD_COST_DETAILS        |    16M|   576M| 53176 |
    |  43 |          MAT_VIEW ACCESS FULL              | LOADS                    |  7833K|    97M|   143K|
    |  44 |        MAT_VIEW ACCESS FULL                | LOAD_DETAILS             |  7947K|    98M|   103K|
    |  45 |       MAT_VIEW ACCESS FULL                 | COST_DETAIL_ITEMS        |    61M|  1688M|   211K|
    |  46 |      SORT AGGREGATE                        |                          |     1 |    12 |       |
    |  47 |       FILTER                               |                          |       |       |       |
    |  48 |        FIRST ROW                           |                          |     1 |    12 |     3 |
    |  49 |         INDEX RANGE SCAN (MIN/MAX)         | LCL_COMP_I1              |     1 |    12 |     3 |
    |  50 |    MAT_VIEW ACCESS BY INDEX ROWID          | MAP_ORG_COMPANY          |     1 |     9 |     1 |
    |  51 |     INDEX UNIQUE SCAN                      | MO_PK                    |     1 |       |     0 |
    -------------------------------------------------------------------------------------------------------
     
    Note
    -----
       - 'PLAN_TABLE' is old version
     
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    "CORE	11.2.0.4.0	Production"
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

  3. #3
    Join Date
    Sep 2013
    Posts
    20
    The text of sql query:
    Code:
     SELECT
    SHIPPER_ORG_ID
    ,COMPANY
    ,CARRIER_ORG_ID
    ,UNIT_ID
    ,UNIT_TRACKING_ID
    ,FOCUS_ID
    ,FOCUS_NAME
    ,PROGRAM_NAME
    ,UNIT_NAME
    ,SCAC
    ,CARRIER_NAME
    ,BILLING_TYPE
    ,BILLING_DAYS
    ,DAILY_RATE
    ,TOTAL_REVENUE
    ,TOTAL_COST
    ,INVOICE_NUMBER
    ,SHIP_DATE
    ,ACCOUNTING_STATUS_ID
    ,LCD_INVOICE_NUMBER
    ,BILLING_ACTION
    ,LOAD_MOVE_REVENUE
    ,REC_TYPE_TIME
    ,UNIT_MOVE_TYPE_ID
    ,MOVE_TYPE_DESC
    ,EMPTY_MILES
    ,EMPTY_ORIGIN_CITY
    ,EMPTY_ORIGIN_STATE
    ,EMPTY_DEST_CITY
    ,EMPTY_DEST_STATE
    ,LOAD_ID
    ,SHIPPER_REFERENCE_NUMBER
    ,WEIGHT
    ,NAME
    ,COMMENT_TEXT
    ,MILES
    ,ORIGIN_CITY
    ,ORIGIN_STATE
    ,ORIGIN_POSTAL_CODE
    ,DESTINATION_CITY
    ,DESTINATION_STATE
    ,DESTINATION_POSTAL_CODE
    ,MINIMUM_AMT
    ,MINIMUM_UNIT_TYPE
    ,MINIMUM_PERIOD
    ,PROGRAM_TRACKING_ID
    ,OVER_MILES
    ,OVER_RATE_PER_MILE
    ,OVER_PERIOD
    ,SW_FLAG
    ,sum(REVENUE)                     REVENUE
    ,sum(COST)                        COST
    ,sum(SHIPPER_LINEHAUL_TOTAL)      SHIPPER_LINEHAUL_TOTAL
    ,sum(SHIPPER_LINEHAUL_RATE)       SHIPPER_LINEHAUL_RATE
    ,sum(SHIPPER_LINEHAUL_QTY)        SHIPPER_LINEHAUL_QTY
    ,sum(CARRIER_LINEHAUL_TOTAL)      CARRIER_LINEHAUL_TOTAL
    ,sum(CARRIER_LINEHAUL_RATE)       CARRIER_LINEHAUL_RATE
    ,sum(CARRIER_LINEHAUL_QTY)        CARRIER_LINEHAUL_QTY
    ,sum(FUEL_REV)                    FUEL_REV
    ,sum(FUEL_REV_QTY)                FUEL_REV_QTY
    ,sum(FUEL_REV_UNIT_COST)          FUEL_REV_UNIT_COST
    ,sum(STOPOFFS)                    STOPOFFS
    ,sum(STOP_REV)                    STOP_REV
    ,sum(ACC_REV)                     ACC_REV
    ,sum(ACC_COST)                    ACC_COST
    ,sum(FUEL_COST)                   FUEL_COST
    ,sum(FUEL_COST_QTY)               FUEL_COST_QTY
    ,sum(FUEL_COST_UNIT_COST)         FUEL_COST_UNIT_COST
    ,sum(STOP_COST)                   STOP_COST
    ,sum(TX_FEE)                      TX_FEE
    ,sum(REBATE)                      REBATE
    ,sum(BILLABLE_PREMIUM)            BILLABLE_PREMIUM
    ,sum(NON_BILLABLE_PREMIUM)        NON_BILLABLE_PREMIUM
    ,sum(DEDICATED_FEE)               DEDICATED_FEE
    ,sum(PASS_THRU)                   PASS_THRU
    ,bol
    from
    (select
    nvl(nvl(l.org_id,dl.shipper_org_id),decode(dp.shipper_org_id,-1,dpu.focus_id,dp.shipper_org_id)) shipper_org_id,
    corg.org_id carrier_org_id,
    dpu.unit_id,
    dpu.unit_tracking_id,
    dpu.focus_id,
    decode(dpu.focus_id,-1,null,forg.name) focus_name,
    dp.program_name,
    dpu.unit_name ,
    corg.scac ,
    corg.name carrier_name,
    DUC.BILLING_TYPE,           -- Unit Bill Method
    DUC.BILLING_DAYS,           -- Unit Days
    DUC.DAILY_RATE,             -- Unit Daily Rate
    DUC.TOTAL_REVENUE,          -- Unit Override Amount
    DUC.TOTAL_COST,             -- Unit Invoice Amount
    DUC.INVOICE_NUMBER,         -- Unit Invoice Number
    LCD.SHIP_DATE,
    DUM.ACCOUNTING_STATUS_ID,   -- Finalization Flag
    LCD.INVOICE_NUMBER LCD_INVOICE_NUMBER, -- Loads Invoice Number
    LCD.BILLING_ACTION,         -- Repositions/Billable Customer/Billable Premiums
    dlm.revenue load_move_revenue,
    dum.departure_time rec_type_time,
    dum.type_id unit_move_type_id,
    dumt.description move_type_desc,
    dedhed.miles empty_miles,
    dedhed.origin_city empty_origin_city,
    dedhed.origin_state empty_origin_state,
    dedhed.destination_city empty_dest_city,
    dedhed.destination_state empty_dest_state,
    dl.load_id ,
    dl.shipper_reference_number ,
    dl.weight ,
    nvl(org.name,dlorg.name) name ,
    dum.comment_text ,
    dum.miles ,
    dum.origin_city,
    dum.origin_state,
    dum.origin_postal_code,
    dum.destination_city,
    dum.destination_state,
    dum.destination_postal_code,
    dp.minimum_amt,
    dp.minimum_unit_type,
    dp.minimum_period,
    dp.program_tracking_id,
    dp.over_miles,
    dp.over_rate_per_mile,
    dp.over_period,
    l.sw_flag,
    decode(cdi.ship_carr,'S',decode(cdi.billable_status,'Y',cdi.subtotal,0),0) revenue,
    decode(cdi.ship_carr,'C',decode(cdi.billable_status,'Y',cdi.subtotal,0),0) cost,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SRA',cdi.subtotal,0),0) shipper_linehaul_total,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SRA',cdi.unit_cost,0),0) shipper_linehaul_rate,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SRA',cdi.quantity,0),0) shipper_linehaul_qty,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CRA',cdi.subtotal,0),0) carrier_linehaul_total,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CRA',cdi.unit_cost,0),0) carrier_linehaul_rate,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CRA',cdi.quantity,0),0) carrier_linehaul_qty,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SFS',cdi.subtotal,'FS',cdi.subtotal,0),0) fuel_rev,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SFS',cdi.subtotal,'FS',cdi.quantity,0),0) fuel_rev_qty,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SFS',cdi.subtotal,'FS',decode(cdi.amount_uom,'PC',cdi.unit_cost/100,cdi.unit_cost),0),0) fuel_rev_unit_cost,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SO',cdi.quantity,0),0) stopoffs,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SO',cdi.subtotal,0),0) stop_rev,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'SO',0,
                                                 'TX',0,
                                                 'SRA',0,
                                                 'FS',0,
                                                 'DF',0,
                                                 'PR',0,
                                                 'PT',0,
                                                 'SFS',0,cdi.subtotal),0) acc_rev,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'SO',0,
                                                 'TX',0,
                                                 'CRA',0,
                                                 'FS',0,
                                                 'DF',0,
                                                 'PR',0,
                                                 'PT',0,
                                                 'CFS',0,cdi.subtotal),0) acc_cost,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CFS',cdi.subtotal,'FS',cdi.subtotal,0),0) fuel_cost,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CFS',cdi.subtotal,'FS',cdi.quantity,0),0) fuel_cost_qty,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'CFS',cdi.subtotal,'FS',decode(cdi.amount_uom,'PC',cdi.unit_cost/100,cdi.unit_cost),0),0) fuel_cost_unit_cost,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'SO',cdi.subtotal,0),0) stop_cost,
    decode(cdi.ref_type,'TX',cdi.subtotal,0) tx_fee,
    decode(cdi.ship_carr,'C',decode(cdi.ref_type,'RB',cdi.subtotal,0),0) rebate,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'PR',decode(cdi.billable_status,'Y',cdi.subtotal,0),0),0) billable_premium,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'PR',decode(cdi.billable_status,'N',cdi.subtotal,0),0),0) non_billable_premium,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'DF',cdi.subtotal,0),0) dedicated_fee,
    decode(cdi.ship_carr,'S',decode(cdi.ref_type,'PT',cdi.subtotal,0),0) pass_thru,
    ldo.bol
    from dedmgr.dedicated_program_unit dpu,
    RATER.DEDICATED_UNIT_COST DUC,
    flatbed.organizations forg,
    dedmgr.dedicated_load_move dlm,
    dedmgr.dedicated_load dl,
    flatbed.loads l,
    flatbed.load_details ldo,
    flatbed.organizations org,
    flatbed.organizations dlorg,
    dedmgr.dedicated_program dp,
    flatbed.organizations corg,
    dedmgr.dedicated_unit_move dum,
    dedmgr.dedicated_unit_move dedhed,
    dedmgr.dedicated_unit_move_type dumt,
    rater.load_cost_details lcd,
    rater.cost_detail_items cdi
    where 1=1
    --and dum.unit_tracking_id=dpu.unit_tracking_id
    AND DPU.UNIT_ID = (SELECT MAX(UNIT_ID) FROM DEDMGR.DEDICATED_PROGRAM_UNIT DPU2 WHERE DPU2.UNIT_TRACKING_ID = DUM.UNIT_TRACKING_ID)
    and duc.unit_tracking_id = dum.unit_tracking_id
    AND DUC.UNIT_COST_ID =
        (SELECT MAX(UNIT_COST_ID) FROM RATER.DEDICATED_UNIT_COST DUC2
        WHERE DUC2.UNIT_TRACKING_ID = DUM.UNIT_TRACKING_ID
        AND DUC2.BILLING_FROM <= TRUNC(DUM.DEPARTURE_TIME)
        AND DUC2.BILLING_TO >= TRUNC(DUM.DEPARTURE_TIME)) -- added constraint on date to get correct invoice - tmm 3/14/07
    and dpu.focus_id = forg.org_id
    and dlm.dum_id=dum.dum_id
    and dum.type_id = dumt.dumt_id
    and dum.dum_id=dedhed.deadhead_parent_id (+)
    and dl.dl_id=dlm.dl_id
    and dl.shipper_org_id=dlorg.org_id (+)
    and dpu.program_id=dp.program_id
    and dp.carrier_org_id=corg.org_id
    and dl.load_id = lcd.load_id
    AND LCD.COST_DETAIL_ID =
    (SELECT MAX(COST_DETAIL_ID) FROM RATER.LOAD_COST_DETAILS
    WHERE LOAD_ID = DL.LOAD_ID
    and lcd.status IN ('P','A'))
    and lcd.load_id = l.load_id
    and l.org_id=org.org_id
    and ldo.load_id = l.load_id
    and ldo.point_type = 'O'
    and cdi.cost_detail_id = lcd.cost_detail_id
    --and dpu.unit_name in ('bomu01')
    --and trunc(dum.departure_time ) between ((to_date('2006-06-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) + (-1))
    --and ((to_date('2006-06-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) + (5))
    ) X, FLATBED.MAP_ORG_COMPANY MOC
    WHERE MOC.ORG_ID (+) = X.SHIPPER_ORG_ID
    group by
    MOC.COMPANY
    ,X.SHIPPER_ORG_ID
    ,X.CARRIER_ORG_ID
    ,X.UNIT_ID
    ,X.UNIT_TRACKING_ID
    ,X.FOCUS_ID
    ,X.FOCUS_NAME
    ,X.PROGRAM_NAME
    ,X.UNIT_NAME
    ,X.SCAC
    ,X.CARRIER_NAME
    ,X.BILLING_TYPE
    ,X.BILLING_DAYS
    ,X.DAILY_RATE
    ,X.TOTAL_REVENUE
    ,X.TOTAL_COST
    ,X.INVOICE_NUMBER
    ,X.SHIP_DATE
    ,X.ACCOUNTING_STATUS_ID
    ,X.LCD_INVOICE_NUMBER
    ,X.BILLING_ACTION
    ,X.LOAD_MOVE_REVENUE
    ,X.REC_TYPE_TIME
    ,X.UNIT_MOVE_TYPE_ID
    ,X.MOVE_TYPE_DESC
    ,X.EMPTY_MILES
    ,X.EMPTY_ORIGIN_CITY
    ,X.EMPTY_ORIGIN_STATE
    ,X.EMPTY_DEST_CITY
    ,X.EMPTY_DEST_STATE
    ,X.LOAD_ID
    ,X.SHIPPER_REFERENCE_NUMBER
    ,X.WEIGHT
    ,X.NAME
    ,X.COMMENT_TEXT
    ,X.MILES
    ,X.ORIGIN_CITY
    ,X.ORIGIN_STATE
    ,X.ORIGIN_POSTAL_CODE
    ,X.DESTINATION_CITY
    ,X.DESTINATION_STATE
    ,X.DESTINATION_POSTAL_CODE
    ,X.MINIMUM_AMT
    ,X.MINIMUM_UNIT_TYPE
    ,X.MINIMUM_PERIOD
    ,X.PROGRAM_TRACKING_ID
    ,X.OVER_MILES
    ,X.OVER_RATE_PER_MILE
    ,X.OVER_PERIOD
    ,X.SW_FLAG
    ,X.bol
     ;
    P.S.: The fast plan has a cost 2,626,000 and slow one is 37945. That suggests the stats are wrong. But what to do to solve it?

  4. #4
    Join Date
    Sep 2013
    Posts
    20
    How do I find string of sql query where it performs merge join of slow plan?

    | 23 | MERGE JOIN | | 156M| 11G| 24560 |

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you waste everyone's time comparing apples to bricks


    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    "CORE 10.2.0.3.0 Production"
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production


    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    "CORE 11.2.0.4.0 Production"
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    Different Oracle versions on different hardware & different OS.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2013
    Posts
    20
    Big thanx for paid attention to my question.
    This task was just for learning purpose and server_A (with slow plan) is not available at this moment (we are in process of moving to new data center). I hope to go on after migration. We'll have test instance where quantity of rows should be similiar (if not equal).
    Thanks again.

Tags for this Thread

Posting Permissions

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