Avatar of thota198
thota198
 asked on

performance of sql query

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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
thota198

ASKER
the execution plan is

Plan
SELECT STATEMENT ALL_ROWS Cost: 302 K Bytes: 2 K Cardinality: 26
       9 SORT ORDER BY Cost: 302 K Bytes: 2 K Cardinality: 26
              8 HASH GROUP BY Cost: 302 K Bytes: 2 K Cardinality: 26
                     7 FILTER
                            3 HASH JOIN Cost: 9 K Bytes: 8 M Cardinality: 100 K
                                   1 TABLE ACCESS FULL TABLE MOBILEADM.DM_MOBILE_DEVICE_TYPE Cost: 5 Bytes: 20 Cardinality: 4
                                   2 TABLE ACCESS FULL TABLE MOBILEADM.DM_MOBILE_SOA_XML_LOG Cost: 9 K Bytes: 10 M Cardinality: 125 K
                            6 HASH JOIN Cost: 335 Bytes: 3 K Cardinality: 27
                                   4 INDEX FULL SCAN INDEX (UNIQUE) MOBILEADM.DM_MOBILE_PRXYADM_PK Cost: 1 Bytes: 135 Cardinality: 15
                                   5 TABLE ACCESS FULL TABLE MOBILEADM.DM_MOBILE_SOA_XML_LOG Cost: 334 Bytes: 350 Cardinality: 7

Regarding the performance this sql is from a standalone proc which is compiling successfully but when i look for debug option in toad to run the proc its not working
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes