Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 сравнение плана выполнения на сервере_А и сервере_Б  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Привет всем.

Помогите понять, в чем кардинальная разница между сервером_А и сервером_Б.

Сервер_А - запрос по ходу выполняется "вечно". Пожалуйста, подскажите - почему.

план выполнения "А"
+

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




план выполнения "Б"
+

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



Текст запроса:
+

  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
 ;



Спасибо за внимание.
26 ноя 14, 16:40    [16909308]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
При этом cost плана выполнения на "быстром" сервере_Б = 2,626,000
а на "медленном" = 37945.
По ходу статистика врет.
Что можно сделать?
26 ноя 14, 16:45    [16909336]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
Добрый Э - Эх
Guest
ecivgamer,

для начала - статистика-то хоть актуальная? На 10-ке автосбор включен? Как вариант - пересобрать статистику на 10-ке, либо перенести на неё статистику с 11-й версии сервера.
В целом, оптимизатор на одной базе решил преимущественно использовать индексный доступ и соединения методом вложенных циклов (10g) , а на другой базе - полное сканирование + HASH-соединение (11g)
26 ноя 14, 17:01    [16909443]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
А как понять, где именно в запросе на сервере_А получается эта кака?

|  23 |                        MERGE JOIN                 |                          |   156M|    11G| 24560 |
26 ноя 14, 17:37    [16909683]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
Добрый Э - Эх
Guest
ecivgamer
А как понять, где именно в запросе на сервере_А получается эта кака?

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




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


смотришь по user_indexes, каким таблицам принадлежат сии индексы, ищешь в запросе упоминание этих таблиц
26 ноя 14, 20:50    [16910473]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Спасибо, нашел соответствующие таблицы:

SELECT table_name, index_name
FROM all_indexes
WHERE index_name in ('DUC_COMP_IDX1', 'DUM_DEPART_TIME_IDX') 

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
DEDICATED_UNIT_COST DUC_COMP_IDX1
DEDICATED_UNIT_MOVE DUM_DEPART_TIME_IDX 
26 ноя 14, 21:54    [16910683]     Ответить | Цитировать Сообщить модератору
 Re: сравнение плана выполнения на сервере_А и сервере_Б  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Большое спасибо всем за участие.
Этот случай рассматривался лишь с целью обучения и сервер_А на данный момент недоступен (мы в процессе миграции на новый датацентр). Надеюсь продолжить после завершения миграции. Будет тестовый инстанс с похожим (если не идентичным) количеством строк в соответствующих таблицах.
27 ноя 14, 15:41    [16914403]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить