Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thota198
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial