Home » RDBMS Server » Performance Tuning » Sql Query Tuning (11.2)
Sql Query Tuning [message #646848] Mon, 11 January 2016 14:20 Go to next message
mnraju999
Messages: 2
Registered: December 2008
Location: Hyderabad
Junior Member

Hello,

i have created one query for my business requirement to extract data , query getting only 200 records but it is taking around 3 hrs to execute, please help to tune this query.
SELECT DISTINCT segment1 
                Project#, 
                b.transaction_source, 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE, 
                        pa_tasks PT 
                 WHERE  PE.task_id = pt.task_id 
                        AND pt.task_number = 'RST' 
                        AND pe.project_id = a.project_id 
                        AND pe.attribute1 IS NULL 
                        AND pe.attribute2 IS NULL 
                        AND PE.org_id = 170 
                        AND pe.project_id = pt.project_id 
                        AND pe.transaction_source = b.transaction_source 
                        AND Trunc(pE.creation_date) > '01-OCT-2015')       "RST" 
                , 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE, 
                        pa_tasks PT 
                 WHERE  PE.task_id = pt.task_id 
                        AND pt.task_number = 'RGT' 
                        AND pe.project_id = a.project_id 
                        AND pe.attribute1 IS NULL 
                        AND pe.attribute2 IS NULL 
                        AND PE.org_id = 170 
                        AND pe.project_id = pt.project_id 
                        AND pe.transaction_source = b.transaction_source 
                        AND Trunc(pE.creation_date) > '01-OCT-2015')       "RGT" 
                , 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE, 
                        pa_tasks PT 
                 WHERE  PE.task_id = pt.task_id 
                        AND pt.task_number = 'RGN' 
                        AND pe.project_id = a.project_id 
                        AND pe.attribute1 IS NULL 
                        AND pe.attribute2 IS NULL 
                        AND PE.org_id = 170 
                        AND pe.project_id = pt.project_id 
                        AND pe.transaction_source = b.transaction_source 
                        AND Trunc(pE.creation_date) > '01-OCT-2015')       "RGN" 
                , 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE, 
                        pa_tasks PT 
                 WHERE  PE.task_id = pt.task_id 
                        AND pt.task_number NOT IN ( 'RGN', 'RGT', 'RST' ) 
                        AND pe.project_id = a.project_id 
                        AND PE.org_id = 170 
                        AND pe.project_id = pt.project_id 
                        AND pe.transaction_source = b.transaction_source 
                        AND Trunc(pE.creation_date) > '01-OCT-2015') 
                "All Other Tasks", 
                Decode(A.project_status_code, '1020', 'Yes', 
                                              'No') 
                "READY TO LIQUIDATE FLAG Y/N", 
                A.project_type, 
                a.attribute4 
                "IPS#", 
                (SELECT short_text 
                 FROM   fnd_documents_short_text 
                 WHERE  media_id IN (SELECT media_id 
                                     FROM   fnd_attached_docs_form_vl 
                                     WHERE  category_description = 
                                            'Project Country, State and City' 
                                            AND entity_name = 'PA_PROJECTS' 
                                            AND datatype_name = 'Short Text' 
                                            AND pk1_value = A.project_id)) 
                "COUNTRY", 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE 
                 WHERE  pe.project_id = a.project_id 
                        AND Trunc(pE.creation_date) > '01-OCT-2015' 
                        AND PE.org_id = 170) 
                "QTR to Date Total of All Tasks", 
                (SELECT SUM(raw_cost) 
                 FROM   pa_expenditure_items_all PE 
                 WHERE  pe.project_id = a.project_id 
                        AND PE.org_id = 170) 
                "PROJECT TOTAL OF ALL TASKS" 
FROM   pa_projects_all a, 
       pa_expenditure_items_all b 
WHERE  A.project_type IN ( 'PPS_Extended_Scope', 'PPS_EOM_Direct', 
                           'PPS_EOM_Indirect' ) 
       AND a.project_id = b.project_id 
       ---and a.segment1='303117' 
       AND a.org_id = 170 ---and trunc(A.creation_date)>'01-JAN-2015' 
       AND a.project_status_code NOT IN ( 
           'CLOSED', 'PENDING_CLOSE', 'UNAPPROVED' ) 
-- 

Thanks
Nagaraju
*BlackSwan formatted posted code

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[Updated on: Mon, 11 January 2016 14:37] by Moderator

Report message to a moderator

Re: Sql Query Tuning [message #646849 is a reply to message #646848] Mon, 11 January 2016 14:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#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: Sql Query Tuning [message #646850 is a reply to message #646848] Mon, 11 January 2016 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Rewrite the query removing all SELECT in SELECT clause.
2/ Remove DISTINCT, if you have duplicates then most likely something is missing in the query

Re: Sql Query Tuning [message #646859 is a reply to message #646850] Tue, 12 January 2016 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can get rid of all the sum subqueries by joining to pa_expenditure_items_all and pa_tasks in the main query and then using CASE statements to return raw_cost for each column when it matches the criteria. e.g.
SUM(CASE WHEN AND pt.task_number = 'RST' 
              AND pe.attribute1 IS NULL 
              AND pe.attribute2 IS NULL 
              AND pe.transaction_source = b.transaction_source 
              AND Trunc(pE.creation_date) > to_date('01-OCT-2015', 'DD-MON-YYYY') 
         THEN raw_cost
    END) as "RST",
SUM(CASE WHEN AND pt.task_number = 'RGT' 
              AND pe.attribute1 IS NULL 
              AND pe.attribute2 IS NULL 
              AND pe.transaction_source = b.transaction_source 
              AND Trunc(pE.creation_date) > to_date('01-OCT-2015', 'DD-MON-YYYY') 
         THEN raw_cost
    END) as "RGT",


You'll need a group by on the query so that'll render the distinct pointless.
Re: Sql Query Tuning [message #647602 is a reply to message #646859] Wed, 03 February 2016 20:23 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is some free material to help you get started with SQL Tuning, and to help you interact here with the team at ORAFaq better. These materials all are related to my book on SQL Tuning. If after previewing the materials you want the book, it is available on Amazon (now with a cheap Kindle version).

Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.

These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin
Previous Topic: Oracle ROWID RANGE SCAN
Next Topic: Please help for Improving Query Performance
Goto Forum:
  


Current Time: Thu Mar 28 12:41:01 CDT 2024