How can I optimize the performance of this SQL

Posted on 2007-07-27
Last Modified: 2013-12-07
How can I improve the speed of this sql? It works but boy is it slow.
Thanks in advance.

Select distinct  
b.business_unit as BusUnt,
b.warehouse_id as WHseID,
b.WarehouseSegment_ID as WHseSeg_ID,
b.conveyor_name as Convname,
b.conveyor_number as ConvNumber,
b.service_type As Service,
b.station_name as Station,
b.start_area_string as StartArea,
to_char(b.inspection_dt, 'MM/DD/YYYY') as InspectionDate,
b.inspection_method as InspectionMethod,
nvl(b.reason_overdue,'NOREASONOVERDUE') as ReasonOverdue,
to_char(b.worst_edd, 'MM/DD/YYYY' )as EstDeliveryDate,
DECODE(b.wol_action,        'REPLACE', 'R',     'TURN IN', 'T') as WolAction,
(select decode(min(decode(prd_dt.warehouse_location, 'BOTH', 1,'INT',2,'EXT',3,'ND', 4)),1,'BOTH',2,'INT',3,'EXT',4,'ND')
from dbname.productdetail prd_dt where b.inspection_id = prd_dt.inspection_id and prd_dt.record_status = 'ACTIVE') as warehouseLocation,
b.design_barcode as DesignBarcode,
(select min(prd_dt.pad_protection_thickness - nvl(prd_dt.REMAIN_COAT_ERROR,0)) from dbname.productdetail prd_dt
        where b.inspection_id = prd_dt.inspection_id and prd_dt.record_status = 'ACTIVE') as AdjustedPadthicknessRequired,
b.station_type as Station_type,  
b.scanner_num as ScannerNumber,
b.module as Module,
b.inspection_subject as InspectionSubject,
decode(b.inspection_method, 'MAN', 1,'AUTO',2,'COMBO',3) as PrecedenceValue
dbname.inspection_current_v b
 left outer join (Select a.*
               from dbname.inspection_v a,
                    dbname.curr_insp_pt c
               where a.inspection_id = c.inspection_id
               and   a.record_status = 'ACTIVE'
               and   a.inspection_method in ('AUTO','COMBO','MAN')
               and   a.category in ('INT','EXT')) b2

on b.line_station_id = b2.line_station_id  and
b.category = b2.category   and
b.auto_view = b2.auto_view   and
b.start_area = b2.start_area  and
nvl(b.inspection_subject,'NOINSPECTIONSUBJECT') = nvl(b2.inspection_subject,'NOINSPECTIONSUBJECT')  and
b.inspection_method <> b2.inspection_method
left outer join dbname.insp_loc_v c on c.business_unit = b.business_unit and
 b.business_unit = 'EXAMPLEBUSINESSUNIT' and
 (b2.inspection_method ='AUTO' or b2.inspection_method is null) and
 (b2.inspection_method ='AUTO' or (b2.inspection_method = 'MAN' and (b.inspection_dt>= b2.inspection_dt  and b.corr_bin_cd in ('S','M','L', 'I')))  or b2.inspection_method is null) and
 (b.inspection_method ='MAN' or b2.inspection_method is null or b.inspection_method ='COMBO' or (b.inspection_method = 'AUTO' and b2.inspection_method = 'COMBO')) and
 b.category in ('INT','EXT') and
 b.inspection_method in ('AUTO','COMBO','MAN') and
 (b.wol_action in ('REPLACE','TURN IN') or
((select min(prd_dt.pad_protection_thickness - nvl(prd_dt.REMAIN_COAT_ERROR,0)) from dbname.productdetail_v prd_dt where b.inspection_id = prd_dt.inspection_id and prd_dt.record_status = 'ACTIVE') < 0.1
 and (b.worst_impact_protected > 0 Or b.worst_actual_impact_protected > 0)) or
 nvl(b.ALT_FREIGHT_PRESS,0) * 1.05 >= nvl(b.min_cps,0))
Question by:Algorithmix
    LVL 58

    Expert Comment

    Remove distinct.

    No, seriously, what do you expect from us? Some general advice?

    In the FROM, WHERE, and ORDER BY clause, use exclusively =, >, >=, <, and <=, using fields and constant expressions. In particular, do not use any functions, like nvl(), convert, etc. Make sure you always link on indexed fields, one side of the relationship at least using a unique index.

    Avoid mixing WHERE and JOIN elements. In other words, try to build your FROM clause as if you could only join fields, and nothing else. The WHERE criteria should come either earlier or later.

    If you use subqueries (In(), Exists(), Any(), etc.), use the WHERE clause like a join; same advice as above.

    Do not use SELECT DISTINCT for more than half a dozen fields. Instead, think carefully which are the key fields you need to make DISTINCT and build a GROUP BY clause for them. For derived unique fields, use First() or a domain aggregate function.

    Ideally, your top-level query should not need DISTINCT at all. Place it in the relevant subqueries or source queries of the FROM clause.

    In a nutsheel: simplify your query. If you really can't, consider creating meaningful intermediate temporary tables, nor forgetting to index the relevant fields or field combinations.

    LVL 4

    Accepted Solution

    good advice Harfang has offered you from a theoretical perspective.

    In addition to his/her comments, you should run this query and examine the access path -- i.e. EXPLAIN_PLAN.

    This will tell you how Oracle intends to access the data, whether it will be performing full table scans instead of an index range scan, nested vs hash joins, etc.

    I think your boolean logic in the where predicate might be adjusted so fewer comparisons are made might have a small impact.

    I don't know the specifics of your data distribution -- but if you can do away with outer joins, great. While they do the trick, they often have a performance impact.

    Re-org for the group by rather than the indicated above...

    Bottom line: when performance tuning. Identify what the problem SQL is, and clarify how much faster you need to get it to run. Run it through EXPLAIN_PLAN, tkprof, etc. then remove the bottlenecks one at a time.  It may be that a single index will bring this to acceptable performance levels -- or it may be more.

    Can you post your access plan / EXPLAIN_PLAN?



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now