Search Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Search Query

  1. #1
    Join Date
    Apr 2006
    Posts
    8

    Search Query

    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)

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and how long do you expect it to take? you are doing a lot of full table scans, 2 seconds doesnt seem unreasonable

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It could take 2 seconds to parse that!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is the Union required, or can you use Union All?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ...and what tkprof says

    Tamil

  6. #6
    Join Date
    Apr 2006
    Posts
    50
    How about posting some more info, such as autotrace, tkprof output or if you're feeling really keen set the 10046 event and let's see the tracefile.

    Without knowing anything about the data in the tables trying to tune your query is like asking 'How long is a piece of string?'.

  7. #7
    Join Date
    Apr 2006
    Posts
    8

    tkprof of my search query

    Hello Sir,

    Pl. find the tkprof output attached (search.txt). Pl. explain me this

    Thanks
    Sathidevi
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Well I was almost right: 0.8 seconds to parse!

    Given that you're outputing 2'500 rows somewhere (report?) - do you actually NEED it to run any faster?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width