Home » RDBMS Server » Performance Tuning » Help in Oracle Query Optimization (Oracle 10g)
Help in Oracle Query Optimization [message #571948] Tue, 04 December 2012 15:01 Go to next message
sanand7984
Messages: 11
Registered: January 2012
Location: Atlanta
Junior Member
WITH x 
     AS (SELECT AA.ccti_category, 
                AA.assigned_to_group_name, 
                AA.case_id 
                ||'-' 
                ||AA.ticket_id 
                   AS 
                    TICKET_ID, 
                AA.ticket_priority, 
                AA.severity, 
                AA.ccti_item, 
                AA.ticket_description, 
                AA.description_long, 
                AA.assignedtoindividual, 
                AA.ticket_status, 
                AA.createddatetext, 
                AA.modifieddatetext, 
                AA.modifieddatetext - AA.createddatetext 
                   AS 
                    high_diff, 
                Pending_hrs(AA.createddatetext, AA.modifieddatetext, 'D', 'OFF', 
                '24' 
                ) 
                   AS 
                nor_diff, 
                AA.ticket_reason_code, 
                AA.item_id 
         FROM   inteq_incidents AA 
         WHERE  ( AA.ticket_status = 'Closed' 
                  AND Trunc(AA.modifieddatetext, 'MONTH') = 
                      Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
                 OR ( AA.ticket_status != 'Closed' 
                      AND Trunc(AA.createddatetext, 'MONTH') <= 
                          Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
         UNION 
         SELECT AA.ccti_category, 
                AA.assigned_to_group_name, 
                AA.case_id 
                ||'-' 
                ||AA.ticket_id 
                AS 
                TICKET_ID, 
                AA.ticket_priority, 
                '' 
                AS 
                SEVERITY, 
                AA.ccti_item, 
                AA.ticket_description, 
                AA.description_long, 
                AA.assignedtoindividual, 
                AA.ticket_status, 
                AA.createddatetext, 
                AA.modifieddatetext, 
                AA.modifieddatetext - AA.createddatetext 
                AS 
                high_diff, 
                Pending_hrs(AA.createddatetext, AA.modifieddatetext, 'D', 'OFF', 
                '24' 
                ) 
                AS 
                nor_diff, 
                AA.ticket_reason_code, 
                AA.item_id 
         FROM   inteq_service_requests AA 
         WHERE  ( AA.ticket_status = 'Closed' 
                  AND Trunc(AA.modifieddatetext, 'MONTH') = 
                      Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
                 OR ( AA.ticket_status != 'Closed' 
                      AND Trunc(AA.createddatetext, 'MONTH') <= 
                          Add_months(Trunc(SYSDATE, 'MONTH'), -1) )), 
     y 
     AS (SELECT BB.item_id, 
                Max(CASE 
                      WHEN BB.attr_name = 'Ticket Type' THEN BB.attr_value 
                    END) TICKETTYPE, 
                Max(CASE 
                      WHEN BB.attr_name = 'Ticket Cause' THEN BB.attr_value 
                    END) TICKETCAUSE, 
                Max(CASE 
                      WHEN BB.attr_name = 'HMHIT Involved' THEN BB.attr_value 
                    END) HMHITINVOLVED, 
                Max(CASE 
                      WHEN BB.attr_name = 'Work Around' THEN BB.attr_value 
                    END) WORKAROUND 
         FROM   inteq_custom_fields BB 
         WHERE  BB.item_id IN (SELECT item_id 
                               FROM   x) 
         GROUP  BY BB.item_id 
         ORDER  BY BB.item_id), 
     z 
     AS (SELECT item_id, 
                actiontakendatetext, 
                Lead(actiontakendatetext, 1, SYSDATE) 
                  over ( 
                    PARTITION BY item_id 
                    ORDER BY actiontakendatetext) AS end_date, 
                status 
         FROM   bi_prod.inteq_history 
         WHERE  Trim(status) IS NOT NULL 
                AND item_id IN (SELECT item_id 
                                FROM   x)), 
     w 
     AS (SELECT item_id, 
                SUM(Pending_hrs(actiontakendatetext, end_date, 'H', 'ON', '8')) 
                AS 
                   pend_hrs, 
                SUM(Pending_hrs(actiontakendatetext, end_date, 'D', 'ON', '8')) 
                AS 
                   pend_dys 
         FROM   z 
         WHERE  z.status = 'Pending' 
         GROUP  BY item_id), 
     v 
     AS (SELECT a.item_id, 
                b.actiontakendatetext, 
                b.act_name, 
                Row_number() 
                  over ( 
                    PARTITION BY a.item_id 
                    ORDER BY b.actiontakendatetext) AS row_num 
         FROM   x a 
                left outer join inteq_history b 
                             ON a.item_id = b.item_id 
                                AND a.assignedtoindividual = 
                                    b.assignedtoindividual 
                                AND a.assigned_to_group_name = b.group_name), 
     u 
     AS (SELECT item_id, 
                actiontakendatetext 
         FROM   v 
         WHERE  row_num = 1) 
SELECT x1.ccti_category, 
       x1.assigned_to_group_name, 
       x1. ticket_id, 
       x1.ticket_priority, 
       x1.severity, 
       x1.ccti_item, 
       x1.ticket_description, 
       x1.description_long, 
       x1.assignedtoindividual, 
       x1.ticket_status, 
       x1.createddatetext, 
       x1.modifieddatetext, 
       x1.high_diff, 
       x1.nor_diff, 
       x1.ticket_reason_code, 
       x1.item_id, 
       y1.tickettype, 
       y1.ticketcause, 
       y1.hmhitinvolved, 
       y1.workaround, 
       w1.pend_hrs, 
       w1.pend_dys, 
       u1.actiontakendatetext 
FROM   x x1 
       left outer join y y1 
                    ON x1.item_id = y1.item_id 
       left outer join w w1 
                    ON x1.item_id = w1.item_id 
       left outer join u u1 
                    ON x1.item_id = u1.item_id 
ORDER  BY x1.item_id 

*BlackSwan added {code} tag & formatting. Do so yourself in the future
  • Attachment: Qry.txt
    (Size: 3.88KB, Downloaded 37 times)

[Updated on: Tue, 04 December 2012 15:19] by Moderator

Report message to a moderator

Re: Help in Oracle Query Optimization [message #571950 is a reply to message #571948] Tue, 04 December 2012 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Help in Oracle Query Optimization [message #571951 is a reply to message #571950] Tue, 04 December 2012 15:32 Go to previous messageGo to next message
sanand7984
Messages: 11
Registered: January 2012
Location: Atlanta
Junior Member
Thanks, I will try to provide the details.
Re: Help in Oracle Query Optimization [message #571953 is a reply to message #571951] Tue, 04 December 2012 17:01 Go to previous message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
You can always use query decomposition to find the pain. Break up your query into pieces and time each piece. There will likely be one piece that takes way longer than the rest. Make that piece go fast. You particular query seems well suited to decomposition because of all the little pieces it is made up by.

Kevin
Previous Topic: Parallel Feature
Next Topic: need more memory for hash joins
Goto Forum:
  


Current Time: Thu Apr 17 22:43:01 CDT 2014

Total time taken to generate the page: 0.09795 seconds