Need help in tuning query

[patni]

Hi,

I need help in tuning this query.
I did search the threads on tuning and encorporated the suggested hints but no luck. The query runs for ever. If I remove the outer join it is fast but with outer join I have not seen the results.

Optimizer - CBO.

Explain plan COST = 1278095

SELECT /*+ ORDERED */
so.order_number order_number,
so.opportunity_number opportunity_number,
so.line_number order_line_number,
itm.item_id proj_item_id,
itm.item_number proj_item_number,
NULL, --stat_item_number
so.line_id parent_line_id,
dscf.component_line_id component_line_id,
itm.organization_id warehouse_id,
so.enter_date_id enter_date_id,
so.requested_date_id requested_date_id,
order_req_d.report_date order_requested_ship_date,
dtd.Technology_desc Technology,
dpd.Platform_desc Platform_Type,
nvl(dfd.fab_id,1) fab_id,
dfd.operational_strategic_acct strategic_account,
dfd.black_book_sub_account sub_account,
nvl(dscf.inventory_org_id,1),
so.ship_confirm_date_id,
nvl(so.revenue_status_id,1),
so.org_id,
promise_d.report_date promise_date,
so.pbg_div_kpu_id pbg_div_kpu_id,
so.customer_ship_id customer_ship_id,
so.customer_bill_id customer_bill_id,
nvl(so.customer_id,1) customer_id,
so.slotting_designator slotting_designator,
so.purchase_order_num purchase_order_number,
so.header_create_by_id header_create_by_id,
so.slotting_option firm_slot_flag,
itm.base_item_number fitem,
so.enter_order_id enter_order_id,
dscf.pick_release_date_id pick_release_date_id,
dscf.pick_line_id pick_line_id,
so.salesrep_id salesrep_id,
so.contact_name contact_name,
so.open_flag order_open_flag,
nvl(so.order_type_id,1) order_type_id,
decode(dstd.system_or_nso,'DW NO VALUE', NULL,dstd.system_or_nso) system_or_nso,
so.project_number om_project_number,
so.ordered_quantity ordered_quantity,
so.cancelled_quantity cancelled_quantity,
order_d.report_date order_date,
so.receivables_line_id receivables_line_id,
so.nso_category om_nso_category,
so.nso_type om_nso_type,
schedule_d.report_date scheduled_Date,
sf.schedule_Status_code demand_status,
--another 30 columns selected from table "SO"
dof.M5_OPPORTUNITY_LINE_ID,
dof.S5_OPPORTUNITY_LINE_ID,
dof.M5_PBG_DIV_KPU_id,
dof.S5_PBG_DIV_KPU_id
FROM
dscf,
So,
dof,
itm,
sf,
dstd,
calendar promise_d,
calendar schedule_d,
calendar order_d,
calendar order_req_d,
technology dtd,
platform dpd,
fab dfd
WHERE
dscf.parent_line_id (+) = so.line_id
AND dscf.config_flag (+) = 'Y'
AND dscf.ordered_quantity(+) - nvl(dscf.cancelled_quantity(+),0) > 0
AND dscf.item_id = itm.item_id (+)
AND nvl(dof.Opportunity_Number,so.Opportunity_Number) = so.Opportunity_Number
AND dof.order_number(+) = so.order_number
AND dscf.component_line_id = sf.line_id (+)
AND dstd.so_type_id = so.order_type_id
AND dstd.order_type_name != 'Archive'
AND dtd.technology_id = so.technology_id
AND dpd.platform_id = so.platform_id
AND dfd.fab_id = so.fab_id
AND promise_d.calendar_id = so.promise_date_id
AND schedule_d.calendar_id (+) = sf.schedule_date_id
AND order_d.calendar_id = so.order_date_id
AND order_req_d.calendar_id = so.requested_date_id;

Here are THE INDEXES ON THE TABLES.
--QUERY does NOT USE INDEXES ON tables dcsf, sf unless forced
--QUERY does NOT USE INDEX ON table "SO" even after forced index hint.

INDEXES

TABLE dcsf

config_flag (Bitmap INDEX)
parent_line_id

TABLE so
line_id

TABLE sf
line_id

TABLE dof
order_number

TABLE itm
item_id

Explain PLAN
ID OPERATION OPTIONS OBJECT_NAME COST CARDINALITY BYTES

1 1 NESTED LOOPS 1278095 1100810 855329370
2 2 NESTED LOOPS 1168014 1100810 839918030
3 3 NESTED LOOPS 1057933 1100810 817901830
4 4 NESTED LOOPS 947852 1100810 803591300
5 5 NESTED LOOPS 837771 1100810 789280770
6 6 NESTED LOOPS OUTER 727690 1100810 774970240
7 7 NESTED LOOPS 617609 1100810 746349180
8 8 NESTED LOOPS 507528 1100810 732038650
9 9 NESTED LOOPS OUTER 397447 1100810 700115160
10 10 NESTED LOOPS OUTER 287366 1100810 686905440
11 11 FILTER
12 12 NESTED LOOPS OUTER
13 13 MERGE JOIN OUTER 67204 1100810 311529230
14 14 SORT JOIN 42471 1100810 270799260
15 15 TABLE ACCESS FULL SO 3385 1100810 270799260
16 16 SORT JOIN 24732 99203 3670511
17 17 TABLE ACCESS BY INDEX ROWID DSCF 24085 99203 3670511
18 18 BITMAP CONVERSION TO ROWIDS
19 19 BITMAP INDEX SINGLE VALUE DSCF_B1
20 20 TABLE ACCESS BY INDEX ROWID DOF 1 168080 51432480
21 21 INDEX RANGE SCAN DOF_N2 168080
22 22 TABLE ACCESS BY INDEX ROWID ITM 1 9714300 340000500
23 23 INDEX UNIQUE SCAN ITM_PK 9714300
24 24 TABLE ACCESS BY INDEX ROWID SF 1 4638500 55662000
25 25 INDEX RANGE SCAN SF_N6 4638500
26 26 TABLE ACCESS BY INDEX ROWID SO_TYPE 1 62 1798
27 27 INDEX UNIQUE SCAN SO_TYPE_PK 62
28 28 TABLE ACCESS BY INDEX ROWID CALENDAR 1 9870 128310
29 29 INDEX UNIQUE SCAN CALENDAR_PK 9870
30 30 TABLE ACCESS BY INDEX ROWID CALENDAR 1 9870 256620
31 31 INDEX UNIQUE SCAN CALENDAR_PK 9870
32 32 TABLE ACCESS BY INDEX ROWID CALENDAR 1 9870 128310
33 33 INDEX UNIQUE SCAN CALENDAR_PK 9870
34 34 TABLE ACCESS BY INDEX ROWID CALENDAR 1 9870 128310
35 35 INDEX UNIQUE SCAN CALENDAR_PK 9870
36 36 TABLE ACCESS BY INDEX ROWID TECHNOLOGY 1 89 1157
37 37 INDEX UNIQUE SCAN TECHNOLOGY_PK 89
38 38 TABLE ACCESS BY INDEX ROWID PLATFORM 1 170 3400
39 39 INDEX UNIQUE SCAN PLATFORM_PK 170
40 40 TABLE ACCESS BY INDEX ROWID FAB 1 3810 53340
41 41 INDEX UNIQUE SCAN FAB_PK 3810
42 0 SELECT STATEMENT 1278095 1100810 855329370


Thanks!
Patni

[tamilselvan]

Try with ORDERED hint in the select. Since you select many rows from SO table, let it be driving table.

Post the explain plan.

Tamil

[raghud]

HE is already using Hint . Self joins and outers on them will cause a problem for the query .

[patni]

Yes, I already have posted the query with ORDERED hint. It runs for ever. Any other suggestions.

Thanks!
Patni

[DaPi]

You say it runs fast with inner joins - have you tried comparing that explain plan with the one from the outer joins? It might give you some ideas.

[wild idea] You could try putting
select . . . from dscf
WHERE dscf.config_flag = 'Y'
AND dscf.ordered_quantity - nvl(dscf.cancelled_quantity,0) > 0
as an in-line view - it confused me for a moment, perhaps it's confusing the CBO. [/wild idea]

[tamilselvan]

I said SO should be the driving table.
He needs to change the FROM clause.

Tamil

[DaPi]

Originally posted by tamilselvan
I said SO should be the driving table.
He needs to change the FROM clause.

Tamil

Quite!

[patni]

The optimizer is CBO, moreover I am giving HINT to the query. Does the occurence of the tables in FROM clause still matters??
Please clarify.

Thanks!
Patni

[patni]

These are the Explain Plan Statistics for the Query below.. Please give me some more suggestions to improve the performance. It ran for 25 minutes to insert 20535 rows, but I believe it should not take this long.

Statistics
----------------------------------------------------------
1432 recursive calls
281341 db block gets
1045181 consistent gets
563380 physical reads
61967416 redo size
442 bytes sent via SQL*Net to client
14401 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
20535 rows processed

Query

INSERT /*+ PARALLEL */ INTO x (
SELECT /*+ USE_NL(dstd dtd) USE_HASH(dsf dpd dfd dof) INDEX(dstd SO_TYPE_PK) INDEX(dof MPS3_N2)
INDEX(dfd FAB_PK) INDEX(dsf SO_N4) INDEX(dpd PLATFORM_PK) INDEX(dtd TECHNOLOGY_PK) */
'Firm',
dscf.component_line_id component_line_id,
nvl(dscf.inventory_org_id,1),
dscf.pick_release_date_id pick_release_date_id,
dscf.pick_line_id pick_line_id,
dsf.order_number order_number,
dsf.opportunity_number opportunity_number,
dsf.line_number order_line_number,
dsf.pst_spec_id pst_spec_id,
dsf.pst_spec_counter pst_spec_counter,
NULL, --stat_item_number
dsf.line_id parent_line_id,
dsf.enter_date_id enter_date_id,
dsf.requested_date_id requested_date_id,
dsf.ship_confirm_date_id,
nvl(dsf.revenue_status_id,1),
dsf.org_id,
dsf.pbg_div_kpu_id pbg_div_kpu_id,
dsf.customer_ship_id customer_ship_id,
dsf.customer_bill_id customer_bill_id,
nvl(dsf.customer_id,1) customer_id,
dsf.slotting_designator slotting_designator,
dsf.purchase_order_num purchase_order_number,
dsf.header_create_by_id header_create_by_id,
dsf.slotting_option firm_slot_flag,
dsf.enter_order_id enter_order_id,
dsf.salesrep_id salesrep_id,
dsf.contact_name contact_name,
dsf.open_flag order_open_flag,
nvl(dsf.order_type_id,1) order_type_id,
dsf.project_number om_project_number,
dsf.ordered_quantity ordered_quantity,
dsf.cancelled_quantity cancelled_quantity,
dsf.receivables_line_id receivables_line_id,
dsf.nso_category om_nso_category,
dsf.nso_type om_nso_type,
dsf.product_family,
--------------Score phase II
dsf.backorder_line_id,
dsf.backorder_release_date_id,
dsf.cancel_line_date_id ,
dsf.cancel_line_id ,
dsf.cancel_order_date_id ,
dsf.cancel_order_id ,
dsf.complete_line_date_id ,
dsf.complete_line_id ,
dsf.complete_order_date_id ,
dsf.complete_order_id ,
dsf.demand_interface_date_id ,
dsf.demand_line_id ,
dsf.inventory_intf_date_id ,
dsf.inventory_line_id ,
dsf.line_cancel_flag ,
dsf.line_closed_flag ,
dsf.manufacturing_line_id ,
dsf.mfg_release_date_id ,
dsf.order_cancel_flag ,
dsf.order_closed_flag ,
dsf.receivables_intf_date_id ,
dsf.rma_approval_date_id ,
dsf.rma_aprve_line_id ,
dsf.rma_interface_date_id ,
dsf.rma_intf_line_id ,
dsf.ship_line_id,
dsf.header_inventory_org_id header_organization_id,
dsf.purchase_line_id,
dsf.purchase_release_date_id,
dsf.service_line_id,
dsf.service_interface_date_id,
dsf.pst_line_id,
dsf.pst_interface_date_id,
did_proj.item_id proj_item_id,
did_proj.item_number proj_item_number,
nvl(did_proj.item_id,1) proj_item_id,
did_proj.organization_id warehouse_id,
did_proj.base_item_number fitem,
sf.schedule_Status_code demand_status,
M5_OPPORTUNITY_LINE_ID, S5_OPPORTUNITY_LINE_ID, M5_PBG_DIV_KPU_ID, S5_PBG_DIV_KPU_ID, OSM_ORDER_TYPE_ID,
decode(dstd.system_or_nso,'DW NO VALUE', NULL,dstd.system_or_nso) system_or_nso,
promise_d.report_date promise_date,
order_d.report_date order_date,
schedule_d.report_date scheduled_Date,
schedule_d.fiscal_year||'-Q'||schedule_d.fiscal_quarter_number Quarter_tag,
substr(schedule_d.fiscal_period_name,1,3) fiscal_month,
order_req_d.report_date order_requested_ship_date,
dtd.Technology_desc Technology,
dpd.Platform_desc Platform_Type,
nvl(dfd.fab_id,1) fab_id,
dfd.operational_strategic_acct strategic_account,
dfd.black_book_sub_account sub_account
FROM
itm did_proj,
sf,
dof,
calendar promise_d,
calendar order_d,
calendar schedule_d,
calendar order_req_d,
technology dtd,
platform dpd,
fab dfd,
(SELECT component_line_id component_line_id, item_id,parent_line_id,
nvl(inventory_org_id,1) inventory_org_id,
pick_release_date_id ,
pick_line_id
FROM component
WHERE config_flag = 'Y'
AND ordered_quantity - nvl(cancelled_quantity ,0) > 0) dscf,
(SELECT so_type_id, system_or_nso FROM so_type
WHERE order_type_name != 'Archive') dstd,
so dsf
WHERE dscf.parent_line_id = dsf.line_id
AND dscf.item_id = did_proj.item_id
AND dscf.component_line_id = sf.line_id
and nvl(dof.Opportunity_Number,dsf.Opportunity_Number) = dsf.Opportunity_Number
AND dof.order_number = dsf.order_number
AND dstd.so_type_id = dsf.order_type_id || ''
AND promise_d.calendar_id = dsf.promise_date_id
AND schedule_d.calendar_id = sf.schedule_date_id
AND order_d.calendar_id = dsf.order_date_id
AND order_req_d.calendar_id = dsf.requested_date_id
AND dtd.technology_id = dsf.technology_id
AND dpd.platform_id = dsf.platform_id
AND dfd.fab_id = dsf.fab_id

[DaPi]

Originally posted by patni
The optimizer is CBO, moreover I am giving HINT to the query. Does the occurence of the tables in FROM clause still matters??
Please clarify.

Thanks!
Patni

The ORDERED hint tells the optimiser to process the tables in the order they appear in the FROM clause. Tamil is saying SO should come first.