troubleshooting Question

performance of sql query

Avatar of thota198
thota198 asked on
Oracle DatabaseSQL
3 Comments2 Solutions523 ViewsLast Modified:
Hi folks
   SELECT SOA_LOG.REQUEST_TYPE,
         DECODE (SOA_LOG.DEAL_TYPE,
                 2, 'Non Standard',
                 3, 'Partner              
Deal',
                 NULL)
            DEAL_TYPE,
         COUNT (1) HIT_COUNT,
         ROUND (MIN (NVL (SOA_LOG.LATENCY, 0)) / 1000, 2) MIN_LATENCY,
         ROUND (MAX (NVL (SOA_LOG.LATENCY, 0)) / 1000, 2) MAX_LATENCY,
         ROUND (AVG (NVL (SOA_LOG.LATENCY, 0)) / 1000, 2) AVG_LATENCY,
         TO_CHAR (MIN (SOA_LOG.CREATED_ON), 'DY, DD-Mon-YYYY HH24:MI:SS')
            FIRST_ACCESSED,
         TO_CHAR (MAX (SOA_LOG.CREATED_ON), 'DY,                
DD-Mon-YYYY HH24:MI:SS')
            LAST_ACCESSED
    FROM DM_MOBILE_SOA_XML_LOG SOA_LOG, DM_MOBILE_DEVICE_TYPE DEVICE_TYPE
   WHERE     SOA_LOG.DEVICE_TYPE_ID <> 5
         AND DEVICE_TYPE.DEVICE_TYPE_ID = SOA_LOG.DEVICE_TYPE_ID
         AND DEVICE_TYPE.ACTIVE = 'Y'
         AND SOA_LOG.DEVICE_UID NOT IN
                (SELECT DISTINCT DEVICE_UID
                   FROM DM_MOBILE_SOA_XML_LOG A, DM_MOBILE_PROXY_ADMIN B
                  WHERE UPPER (A.USER_ID) = UPPER (B.USER_ID))
GROUP BY SOA_LOG.REQUEST_TYPE, SOA_LOG.DEAL_TYPE
ORDER BY SOA_LOG.REQUEST_TYPE,
         SOA_LOG.DEAL_TYPE,
         HIT_COUNT DESC,
         TO_DATE (
            TO_CHAR (MAX (SOA_LOG.CREATED_ON), 'DY, DD-Mon-YYYY HH24:MI:SS'),
            'DY, DD-Mon-YYYY HH24:MI:SS') DESC,
         REQUEST_TYPE    

In the above sql there r 3 tables and each of them have an index
Tables                                           Index             Columns
 DM_MOBILE_SOA_XML_LOG     INDEX           REQUEST_TYPE
 DM_MOBILE_DEVICE_TYPE        INDEX           DEVICE_TYPE
 DM_MOBILE_PROXY_ADMIN      INDEX           USER_ID

the columns of REQUEST_TYPE , USER_ID are primary key columns
Is there a way i can improve the performance with a different sql like by using hints ya any other way
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros