Please advice me on this query as this is taking more than 2 secs. if any background process is running.
See the attached file with planreport.
Thanks
Sathi
SQL Statement -- search.sql:
SELECT /*+ NO_INDEX (vp xtmpvehicle_price) */
vd.vehicle_id, vd.registration_nbr, vdr.vehicle_desc, vd.
non_gm_vehicle_flg, vo2.option_desc colour, vd.vehicle_status_id, vd.
vehicle_category_cd, vd.defleet_mileage_value, vd.vat_category_cd, vd.
location_cd, sc.sales_channel_nm, se.sale_event_nm, b.bulkpack_nm, bv.
delete_flg, vg.grade_cd, TRUNC(SYSDATE - vd.first_defleet_dt) stockage,
va.cherry_pick_flg, vp.vat_amt, vp.net_amt
FROM vehicle_detail vd, vehicle_derivative vdr, vehicle_option_relationship
vor2, vehicle_option vo2, vehicle_allocation va, sales_channel sc,
sale_event se, vehicle_grade vg, bulkpack_vehicle bv, bulkpack b,
vehicle_price vp
WHERE vd.pseudo_cd = vdr.pseudo_cd
AND vdr.delete_flg = 'N'
AND vdr.manufacturer_cd = '9'
AND vd.non_gm_vehicle_flg = 'N'
AND vd.vehicle_id = vor2.vehicle_id
AND vor2.manufacturer_cd = vo2.manufacturer_cd
AND vor2.model_year = vo2.model_year
AND vor2.model_year_suffix_typ = vo2.model_year_suffix_typ
AND vor2.option_cd = vo2.option_cd
AND UPPER(vor2.option_typ) = 'CC'
AND vor2.delete_flg = 'N'
AND vd.remarketing_company_id IN (1)
AND vd.vehicle_id = va.vehicle_id
AND va.cherry_pick_flg = 'N'
AND va.sales_channel_id = sc.sales_channel_id
AND sc.delete_flg = 'N'
AND sc.status_id = 10
AND va.sale_event_id = se.sale_event_id(+)
AND (se.delete_flg = 'N'
OR se.delete_flg IS NULL)
AND (se.remain_orig_channel_flg = 'Y'
OR se.remain_orig_channel_flg IS NULL)
AND (NOT se.status_id IN (102, 101)
OR se.status_id IS NULL)
AND vd.grade_id = vg.grade_id(+)
AND vd.vehicle_status_id = 39
AND vd.vehicle_id = vp.vehicle_id
AND LOWER(vp.price_typ) = 'sc'
AND vp.delete_flg = 'N'
AND vd.bulkpack_reserved_id IS NULL
AND vd.accepted_bulkpack_id IS NULL
AND vd.vehicle_id = bv.vehicle_id(+)
AND bv.bulkpack_id = b.bulkpack_id(+)
UNION
SELECT /*+ NO_INDEX (vp xtmpvehicle_price) */
vd.vehicle_id, vd.registration_nbr, vdr.vehicle_desc, vd.
non_gm_vehicle_flg, ' ' colour, vd.vehicle_status_id, vd.
vehicle_category_cd, vd.defleet_mileage_value, vd.vat_category_cd, vd.
location_cd, sc.sales_channel_nm, se.sale_event_nm, b.bulkpack_nm, bv.
delete_flg, vg.grade_cd, TRUNC(SYSDATE - vd.first_defleet_dt) stockage,
va.cherry_pick_flg, vp.vat_amt, vp.net_amt
FROM vehicle_detail vd, vehicle_derivative vdr, vehicle_allocation va,
sales_channel sc, sale_event se, vehicle_grade vg, bulkpack_vehicle bv,
bulkpack b, vehicle_price vp
WHERE vd.pseudo_cd = vdr.pseudo_cd
AND vdr.delete_flg = 'N'
AND vdr.manufacturer_cd = '9'
AND vd.non_gm_vehicle_flg = 'Y'
AND vd.remarketing_company_id IN (1)
AND vd.vehicle_id = va.vehicle_id
AND va.cherry_pick_flg = 'N'
AND va.sales_channel_id = sc.sales_channel_id
AND sc.delete_flg = 'N'
AND sc.status_id = 10
AND va.sale_event_id = se.sale_event_id(+)
AND (se.delete_flg = 'N'
OR se.delete_flg IS NULL)
AND (se.remain_orig_channel_flg = 'Y'
OR se.remain_orig_channel_flg IS NULL)
AND (NOT se.status_id IN (102, 101)
OR se.status_id IS NULL)
AND vd.grade_id = vg.grade_id(+)
AND vd.vehicle_status_id = 39
AND vd.vehicle_id = vp.vehicle_id
AND LOWER(vp.price_typ) = 'sc'
AND vp.delete_flg = 'N'
AND vd.bulkpack_reserved_id IS NULL
AND vd.accepted_bulkpack_id IS NULL
AND vd.vehicle_id = bv.vehicle_id(+)
AND bv.bulkpack_id = b.bulkpack_id(+)
ORDER BY stockage DESC
Get Explain Plan By:
Cost All Rows
Execution Steps:
50 SELECT STATEMENT
49 SORT (UNIQUE)
48 UNION-ALL
23 HASH JOIN
21 HASH JOIN (OUTER)
19 HASH JOIN
17 TABLE ACCESS (BY INDEX ROWID), VEHICLE_OPTION_RELATIONSHIP (SMVOWN)
16 NESTED LOOPS
14 HASH JOIN (OUTER)
12 HASH JOIN (OUTER)
10 HASH JOIN
1 TABLE ACCESS (FULL), VEHICLE_PRICE (SMVOWN)
9 HASH JOIN
2 TABLE ACCESS (FULL), VEHICLE_DETAIL (SMVOWN)
8 FILTER
7 HASH JOIN (OUTER)
5 HASH JOIN
3 TABLE ACCESS (FULL), SALES_CHANNEL (SMVOWN)
4 TABLE ACCESS (FULL), VEHICLE_ALLOCATION (SMVOWN)
6 TABLE ACCESS (FULL), SALE_EVENT (SMVOWN)
11 TABLE ACCESS (FULL), VEHICLE_GRADE (SMVOWN)
13 TABLE ACCESS (FULL), BULKPACK_VEHICLE (SMVOWN)
15 INDEX (RANGE SCAN), X_IE1_VOR_VEHICLE_ID (SMVOWN)
18 TABLE ACCESS (FULL), VEHICLE_OPTION (SMVOWN)
20 TABLE ACCESS (FULL), BULKPACK (SMVOWN)
22 TABLE ACCESS (FULL), VEHICLE_DERIVATIVE (SMVOWN)
47 NESTED LOOPS
44 NESTED LOOPS (OUTER)
41 HASH JOIN (OUTER)
39 FILTER
38 NESTED LOOPS (OUTER)
35 HASH JOIN
33 NESTED LOOPS
30 NESTED LOOPS
27 NESTED LOOPS (OUTER)
24 TABLE ACCESS (FULL), VEHICLE_DETAIL (SMVOWN)
26 TABLE ACCESS (BY INDEX ROWID), VEHICLE_GRADE (SMVOWN)
25 INDEX (UNIQUE SCAN), XPKVEHICLE_GRADE (SMVOWN)
29 TABLE ACCESS (BY INDEX ROWID), VEHICLE_PRICE (SMVOWN)
28 INDEX (RANGE SCAN), XIDX_VEHICLE_ID_VP (SMVOWN)
32 TABLE ACCESS (BY INDEX ROWID), VEHICLE_DERIVATIVE (SMVOWN)
31 INDEX (UNIQUE SCAN), XPKVEHICLE_DERIVATIVE (SMVOWN)
34 TABLE ACCESS (FULL), VEHICLE_ALLOCATION (SMVOWN)
37 TABLE ACCESS (BY INDEX ROWID), SALE_EVENT (SMVOWN)
36 INDEX (UNIQUE SCAN), XPKSALE_EVENT (SMVOWN)
40 TABLE ACCESS (FULL), BULKPACK_VEHICLE (SMVOWN)
43 TABLE ACCESS (BY INDEX ROWID), BULKPACK (SMVOWN)
42 INDEX (UNIQUE SCAN), XPKBULKPACK (SMVOWN)
46 TABLE ACCESS (BY INDEX ROWID), SALES_CHANNEL (SMVOWN)
45 INDEX (UNIQUE SCAN), XPKSALES_CHANNEL (SMVOWN)
