SQL performance tuning using Toad for Oracle Base Explain Plan was the first blog. This is the second blog in a series that focuses on how Quest® Toad® can display SQL execution plans and the usefulness of that information. Learn how to optimize SQL query - in minutes.

Leave it in and fix the other code duplication.Can’t find time for SQL performance tuning? Reliant on overstretched DBAs who don’t have a spare minute? Don’t let your queries slow down the database. However, my last point below makes it a moot point. Whatever it returns can't possibly = 'Cancelled' so you should just rip it out and replace with null if necessary.

However since I know nothing about your system or what the query is supposed to, and because it's depressingly complicated I'll limit myself to a few obvious suggestions:ġ) Query 2b is a complete waste of time. There should be no need to repeat all those sub-queries. Queries with the same number have the same from and where but different selects. I've added comments to inditify all the individual queries contained within. WHERE rcpt_status.receipt_header_key = rh.receipt_header_keyĪND rcpt_status.new_status = '1500') -rcpt_status.new_status_date- rcpt_status.new_status_date THEN cancelled_date.status_date -cancelled_date.status_dateĮLSE (SELECT MAX (rcpt_status.new_status_date)įROM yfs_receipt_status_audit rcpt_status WHERE st.status = (SELECT MAX (status) -query 4 (SELECT 'Partially ' || st.description -query 2b (same as 2 apart from the ||) WHERE st.status = (SELECT MIN (status) -query 3

WHERE ors.order_header_key = oh.order_header_keyĪND ors.order_line_key = ol.order_line_key WHERE st.status = (SELECT MAX (status) -query 3ĪND p.process_type_key = st.process_type_keyĪND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key -query 3a (SELECT SUM (sts.status_quantity) -query 1a WHERE sts.order_header_key = oh.order_header_keyĪND sts.order_line_key = ol.order_line_keyĭECODE ((SELECT SUM (ol.ordered_qty)-query 1 WHEN DECODE ((SELECT SUM (ol.ordered_qty)-query 1

Toad sql developer