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)
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 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/681cc10362f3c2db5ac780627eba3710?s=64&d=mm&r=g) |
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.
|
|
|
|
|
|
|
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/681cc10362f3c2db5ac780627eba3710?s=64&d=mm&r=g) |
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
|
|
|
Goto Forum:
Current Time: Sat Aug 31 20:09:09 CDT 2024
|