OraFAQ Forum: Performance Tuning » Please help me to understand why the query works longer of server_A than on server

Home » RDBMS Server » Performance Tuning » Please help me to understand why the query works longer of server_A than on server_B (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me to understand why the query works longer of server_A than on server_B [message #628607]

Wed, 26 November 2014 07:27

ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine

Senior Member

Hi all,

Please help me to understand why the query works longer of 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);

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

| 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

This is from server_B. The query works fine enough.

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);


 

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

| 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

The 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

;

In case something else is needed to help me, then please tell me.
Thanks ahead.

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628608 is a reply to message #628607]

Wed, 26 November 2014 07:33

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

So the fast plan has a cost 2,626,000 and slow one is 37945.
That suggests the stats are wrong.

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628612 is a reply to message #628608]

Wed, 26 November 2014 07:45

ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine

Senior Member

cookiemonster wrote on Wed, 26 November 2014 15:33

So the fast plan has a cost 2,626,000 and slow one is 37945.
That suggests the stats are wrong.

So what can you suggest please?

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628618 is a reply to message #628612]

Wed, 26 November 2014 07:59

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

regather all stats and see what difference it makes.

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628631 is a reply to message #628618]

Wed, 26 November 2014 08:40

ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine

Senior Member

Well, tech lead said that stat are actual.

How do I find string of sql query where it performs merge join of slow plan?

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

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628632 is a reply to message #628631]

Wed, 26 November 2014 08:42

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

So you have two different database versions. I assume two different sets of hardware.

Is the data even identical?

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628641 is a reply to message #628632]

Wed, 26 November 2014 12:11

ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine

Senior Member

Roachcoach wrote on Wed, 26 November 2014 16:42

So you have two different database versions. I assume two different sets of hardware.

Yes.

Quote:

Is the data even identical?

They should be identical.

How do I find string of sql query where it performs merge join of slow plan?

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

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628642 is a reply to message #628641]

Wed, 26 November 2014 12:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from SQL below from both databases

select count(*) from dedmgr.dedicated_load dl;
select count(*) from dedmgr.dedicated_load_move dlm;
select count(*) from dedmgr.dedicated_program dp;
select count(*) from dedmgr.dedicated_program_unit dpu;
select count(*) from dedmgr.dedicated_unit_move dedhed;
select count(*) from dedmgr.dedicated_unit_move dum;
select count(*) from dedmgr.dedicated_unit_move_type dumt;
select count(*) from flatbed.load_details ldo;
select count(*) from flatbed.loads l;
select count(*) from flatbed.organizations corg;
select count(*) from flatbed.organizations dlorg;
select count(*) from flatbed.organizations forg;
select count(*) from flatbed.organizations org;
select count(*) from rater.cost_detail_items cdi
select count(*) from RATER.DEDICATED_UNIT_COST DUC;
select count(*) from rater.load_cost_details lcd;

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628643 is a reply to message #628607]

Wed, 26 November 2014 13:19

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Can you drop the plan_table, re-create it by running $ORACLE_HOME/rdbms/admin/utlxplan.sql, and then explain the query again? You have an old version of the table, which is why your execution pans don't show everything one needs to know,

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628666 is a reply to message #628607]

Wed, 26 November 2014 21:23

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Let me try to give some background to help you.

99% of the time, a query on will take one of two forms (or styles). A query will execute using either PRECISION QUERY STYLE (because it is looking for a small number of rows), or it will execute using WAREHOUSE QUERY STYLE (because it is looking for a large number of rows). A precision query will use NESTED LOOPS JOIN with supporting INDEX LOOKUPS on the inner tables of joins. A warehouse query will use HASH JOINS supported by FULL TABLE SCANS that bring data into the query.

If the optimizer incorrectly categorizes a query, then it will generate a query plan using the wrong query style. For example, if the optimizer thinks the query will be manipulating a small number of rows (say < 2%) then is will generate a plan using precision style (that means a query heavy on nested loops join). If the optimizer estimated incorrectly and in fact there are a large number of rows to be manipulated (say > 2%) then it should have generated a warehouse style plan heavy on hash join. In this situation, the nested loops joins will be iterating over a large number of rows and the query will take a much longer time to run than it should. This mistake compounds with the number of nested loops joins in the plan. I note there are many in the poorly performing query plan.

This appears to be what has happened to this query. The optimizer opted for a nested loops join heavy plan (precision style execution) because it thinks there is a relatively small number of rows that need to be carried forward through query execution, when in fact it appears there are many rows that need to be carried around. This is why the warehouse style plan performs better in your example.

As was suggested, this is almost always a statistics issue though the specific statistical problem could be one of many things. I suggest you follow the current advice that has already been giving in this thread, and start by recollecting statistics. Ask you DBA for help.

I have uploaded chapter #1 of my book on SQL tuning that describes the cardinality based tuning process using the FILTERED ROWS PERCENTAGE method of Cardinality Based Analysis. It is the full chapter and so may prove of some help in understanding why there are really only two types of queries (precision or warehouse) and how you can determine if the optimizer made a mistake in its initial estimation of cardinalities and if that mistake in turn had an impact as we just discussed. This is where BlackSwan may be directing you by starting you off with getting the cardinalities of the underlying tables.

Good luck. Kevin

Attachment: Oracle SQL Performance Tuning and Optimization v25 chapter 1.pdf
(Size: 780.44KB, Downloaded 2745 times)

Report message to a moderator

Re: Please help me to understand why the query works longer of server_A than on server_B [message #628718 is a reply to message #628666]

Thu, 27 November 2014 06:22

ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine

Senior Member

Big thanx to all of you who paid attention to my question.
Sorry for disappointing you, this task was just for learning purpose and server_A 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.

P.S.: Kevin, I can't download your book for some reason.

Upd: Kevin, it says "Can't save /tmp/zZPEUbmv.pdf.part because it's impossible to read source file. Wait a little and try again or ask server administrator for help" (I translated it from my native language).
Ubuntu 14.04.1 LTS, Firefox 33.0

Upd: It was browser issue, downloaded successfully in Chromium 39.0.2171.65 Ubuntu 14.04

[Updated on: Thu, 27 November 2014 06:48]

Report message to a moderator

Previous Topic:	A question about execution plan
Next Topic:	Change from LOGGING to NOLOGGING for a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 17:54:20 CST 2025