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