How can I optimize the performance of this SQL

Posted on 2007-07-27
Medium Priority
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

ID: 19583352
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.


Accepted Solution

jtrifts earned 1500 total points
ID: 19585066
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 distinct....as 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?



Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 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