How to optimize this query ?

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
deve_thomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Please post the execution plan.

Text please, no images.
0
PortletPaulfreelancerCommented:
text that aligns into columns too please, use the Code feature

Open in new window

0
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
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
sdstuberCommented:
>>> 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
johnsoneSenior Oracle DBACommented:
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
sdstuberCommented:
 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
deve_thomosAuthor Commented:
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
PortletPaulfreelancerCommented:
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
johnsoneSenior Oracle DBACommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.