[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to optimize this query ?

Posted on 2014-08-27
12
Medium Priority
?
151 Views
Last Modified: 2014-11-17
Hi expert,

From Below query is fetching only 500 records but it is taking  around 70 seconds. index is cretad for every column which columns are used in where condition except one view.
Can you please how can i optimize this query?
Select  S_NAME, S_CODE, Max(NOF) As NOF , Max(LA) As LA, Max(LB) As LB   
     ,  IS_SEC, SELF,
     max(QAID_A) as QAID_A  
     ,  max(QAID_B) as QAID_B  
from (   
select sat.S_NAME, sat.S_CODE , qqr.NOF,    
         (select status_name   
            from ms_qsm_status   
            where status_id = decode(sat.ASN_NAME,  'A', qqr.resp_status  )   
          )  LA,   
     (select status_name   
            from ms_qsm_status   
            where status_id = decode(sat.ASN_NAME,  'B', qqr.resp_status  )   
          )   LB   
          ,DECODE(MS_TPR_GET_RISK_AREA_STATUS(sat.S_CODE,2), '1','C',   
                                                           '2','D',   
                                                           '3','E',   
                                                           '4','F',   
                                                           '5','G',   
                                                           '6','H',   
                                                           '','',  
                                                           'G','G' )  
         AS   IS_SEC ,  
          DECODE(MS_TPR_GET_RISK_AREA_STATUS(sat.S_CODE,3),  '1','C',   
                                                           '2','D',  
                                                           '3','E',  
                                                           '4','F',  
                                                           '5','G',  
                                                           '6','H',  
                                                           '','',  
                                                           'G','G')  
          AS   SELF       ,  
          decode(sat.ASN_NAME,  'A', sat.QAID  )  as  QAID_A ,  
          decode(sat.ASN_NAME,  'B', sat.QAID  )  as  QAID_B  
FROM RPT_V QQR,   
     MST Sat,  
     MGO Gco
     ,
     ms_sbm_service_diligence_group sdg
WHERE QQR.QUE_REQUEST_ID  = SAT.QAID   
And  Gco.Object_Id        = Sat.Supplier_Id
and SAT.DILIGENCE_PROCESS_ID  = SDG.DILIGENCE_PROCESS_ID  
and sdg.current_status not in('4')
AND SAT.SERVICE_NAME = 'SELF'   
and (NVL(TRUNC(GCO.VALID_UNTIL),TRUNC(sysdate))>=TRUNC(sysdate))    
and SAT.ASN_NAME in ( 'A' , 'B' )

)   
group by S_NAME, S_CODE , IS_SEC, SELF;

Open in new window


Regards
Thomos
0
Comment
Question by:deve_thomos
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40287998
Please post the execution plan.

Text please, no images.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40288006
text that aligns into columns too please, use the Code feature

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40288007
This may or may not be an issue based on the other indexed columns but TRUNC(GCO.VALID_UNTIL) would not be able to use an index on GCO.VALID_UNTIL unless it is a function based index.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 total points
ID: 40288031
I would change this:
            FROM RPT_V QQR
               , MST Sat
               , MGO Gco
               , ms_sbm_service_diligence_group sdg
            WHERE QQR.QUE_REQUEST_ID = Sat.QAID
                  AND Gco.Object_Id = Sat.Supplier_Id
                  AND SAT.DILIGENCE_PROCESS_ID = SDG.DILIGENCE_PROCESS_ID
                  AND sdg.current_status NOT IN ('4')
                  AND SAT.SERVICE_NAME = 'SELF'
                  AND (NVL(TRUNC(GCO.VALID_UNTIL), TRUNC(sysdate)) >= TRUNC(sysdate))
                  AND SAT.ASN_NAME IN ('A', 'B')

Open in new window

To this:
FROM RPT_V QQR
INNER JOIN MST Sat                            ON QQR.QUE_REQUEST_ID = Sat.QAID
INNER JOIN MGO Gco                            ON Sat.Supplier_Id = Gco.Object_Id
INNER JOIN ms_sbm_service_diligence_group sdg ON Sat.DILIGENCE_PROCESS_ID = Sdg.DILIGENCE_PROCESS_ID

WHERE sdg.current_status NOT IN ('4')
      AND SAT.SERVICE_NAME = 'SELF'
      AND SAT.ASN_NAME IN ('A', 'B')

        --AND (NVL(TRUNC(GCO.VALID_UNTIL), TRUNC(sysdate)) >= TRUNC(sysdate))
      AND (  GCO.VALID_UNTIL  >= TRUNC(sysdate) OR GCO.VALID_UNTIL IS NULL  )
 

Open in new window

I think the last line above in particular would make better use of indexes (if any)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40288074
The IS NULL is not going to help.  That negates an index as well because NULL values aren't stored in the index, a full scan is needed to satisfy the date condition either way.

Based on the where conditions I would say a composite index on MST (SERVICE_NAME, ASN_NAME) would be the most helpful, but we need to see the plan.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40288165
>>> The IS NULL is not going to help.  That negates an index as well

but, the OR gives the optimizer the option of evaluating the conditions independently (usually this means dividing the query and using a union) and the other part can use an index


if there was an index on mgo.object_id and mgo.valid_until - you can get the nulls indexed through the object_id
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40288190
The OR allows it to be split up, but the NULL still would require a scan on its own to get that information.  Or it would require some other completely different access path.

Why not a function based index on:

NVL(VALID_UNTIL, TO_DATE('01019999','mmddyyyy'))

That would get you the date part within an index.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40288204
 index on mgo.object_id and mgo.valid_until 

Open in new window


or
function based index on:

NVL(VALID_UNTIL, TO_DATE('01019999','mmddyyyy'))

Open in new window


try both with corresponding query adjustments

don't trust us, trust your results
0
 

Author Comment

by:deve_thomos
ID: 40289743
Hello expert,

I have cretaed functional index on
 NVL(VALID_UNTIL, TO_DATE('01019999','mmddyyyy'))
but it is again taking 70 seconds.

Regards
Thomos
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40289773
please provide the explain plan and the most recent query. Please use text for the explain plan (not an image) but retain it so it is clearly readable (using code tags helps do this).
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40289939
If you have NVL(VALID_UNTIL, TO_DATE('01019999','mmddyyyy')) as an index, then you need to change this:

and (NVL(TRUNC(GCO.VALID_UNTIL),TRUNC(sysdate))>=TRUNC(sysdate))

to this:

and NVL(VALID_UNTIL, TO_DATE('01019999','mmddyyyy')) >=TRUNC(sysdate))

If you aren't using the exact function from the function based index in the where clause then the index cannot be used.

Also, as suggested may times, we need to see the plan.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40291396
If you want Oracle queries to execute faster, here are some things to avoid:
1. Nested selects
2. Using operators like; NVL, TRUNC, DECODE, etc on database column values
2. Using "NOT IN" or "<>" comparisons.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

872 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