Tuning SQL statement performance

Question

I have two queries that return same results:

1.

SELECT DISTINCT 
         cvc.object_id
        , cvc.object_name  
 FROM ems.ibo_sm_cvc_rfs cvc,  
      ems.ibo_alcatel_mse_locale poi, 
      ems.ibo_nbn_csa csa, 
      ems.ibo_sm_ean_service_sites_rfs sites, 
      ems.ibo_sm_ean_service_site_rfs site  
WHERE poi.object_name ='testPoi'  
  AND csa.parent_id = poi.object_id  
  AND cvc.csa_id = csa.csa_id  
  AND sites.parent_id = cvc.object_id  
  AND site.service_site_type = 'testSite'  
  AND site.object_name IN (SELECT mse_chassis.object_name  
                             FROM ems.ibo_alcatel_mse_chassis mse_chassis  
                            WHERE mse_chassis.parent_id = poi.object_id);

2. :

SELECT cvc.object_id,
       cvc.object_name
  FROM ems.ibo_sm_cvc_rfs cvc
  JOIN ems.ibo_nbn_csa csa
    ON cvc.csa_id = csa.csa_id
  JOIN ems.ibo_sm_ean_service_sites_rfs sites
    ON sites.parent_id   = cvc.object_id
 WHERE csa.parent_id IN (SELECT poi.object_id
                           FROM ems.ibo_alcatel_mse_locale poi
                          WHERE poi.object_id IN (SELECT csa.parent_id FROM ems.ibo_nbn_csa csa)
                             AND poi.object_name = 'testPoi'
                             AND poi.object_id  IN 
                                 (SELECT mse_chassis.parent_id
                                    FROM EMS.ibo_alcatel_mse_chassis mse_chassis
                                   WHERE mse_chassis.object_name IN
                                         (SELECT site.object_name
                                            FROM EMS.ibo_sm_ean_service_site_rfs site
                                           WHERE site.service_site_type = 'testSite')
                                 )
                         );

Odd for me is that first statement finish in 0.156 seconds while the second one in 0.624 seconds. Need to mention that all ems.* objects are views and I looked that basically every column in where clause is indexed. If necesary I could put the views schema, but I rather not because is company information. Maybe a pair of experience eyes may see an improvment of both of these statements

You have a lot more subqueries in your second query,so it\`s no wonder it`s slower. — Mihai, Jul 31 '15 at 07:59
it's not just the number of subqueries, each of those is used via IN ( subquery ) and IN can be awful for performance. — Paul Maxwell, Jul 31 '15 at 08:13
AFAIK oracle behind the scenes shoud transform IN to JOINs but I guess it`s confused by IN ..WHERE .. IN — Mihai, Jul 31 '15 at 08:15
@Used_By_Already I don't know other way to expose exection plans here — Andrei Maieras, Jul 31 '15 at 08:18
@Mihai thanks, yeah I was trying to eliminate distinct, but it seems subqueries in this case are worse — Andrei Maieras, Jul 31 '15 at 08:21

score 1 · Accepted Answer · answered Jul 31 '15 at 08:07

As @Mihai pointed, your second query is full of subqueries and again I would go one step further to fine tune your first posted query like below

SELECT DISTINCT 
    cvc.object_id
    , cvc.object_name  
FROM ems.ibo_sm_cvc_rfs cvc 
    JOIN ems.ibo_nbn_csa csa ON cvc.csa_id = csa.csa_id
    JOIN ems.ibo_alcatel_mse_locale poi ON csa.parent_id = poi.object_id 
    JOIN ems.ibo_sm_ean_service_sites_rfs sites ON sites.parent_id = cvc.object_id 
    JOIN EMS.ibo_alcatel_mse_chassis mse_chassis ON mse_chassis.parent_id = poi.object_id
    JOIN EMS.ibo_sm_ean_service_site_rfs site ON site.object_name = mse_chassis.object_name
WHERE poi.object_name ='testPoi' 
AND site.service_site_type = 'testSite';

Notice two things:

I have modified your implicit join syntax to explicit join syntax
I have modified your last subquery with IN clause to a JOIN statement

I think this would be the best approach, my concern was to eliminate the overhead brought by `Distinct` — Andrei Maieras, Jul 31 '15 at 08:15
If you run the posted query in answer, what time frame does it fetches? Is it more than *0.156 seconds* — Rahul, Jul 31 '15 at 08:17

score 1 · Answer 2 · answered Jul 31 '15 at 08:28

SELECT 
    cvc.object_id
    , cvc.object_name  
FROM ems.ibo_sm_cvc_rfs cvc,  
    ems.ibo_alcatel_mse_locale poi, 
    ems.ibo_nbn_csa csa, 
    ems.ibo_sm_ean_service_sites_rfs sites, 
    EMS.ibo_sm_ean_service_site_rfs site,
    EMS.ibo_alcatel_mse_chassis mse_chassis  
WHERE poi.object_name ='testPoi'  
    AND csa.parent_id = poi.object_id  
    AND cvc.csa_id = csa.csa_id  
    AND sites.parent_id = cvc.object_id  
    AND site.service_site_type = 'testSite'  
    AND site.object_name = mse_chassis.object_name  
    AND mse_chassis.parent_id = poi.object_id 
GROUP BY cvc.object_id, cvc.object_name

Aim for a loose index scan which is extremely efficent,so add this index

CREATE INDEX objects_idx
  ON ibo_sm_cvc_rfs (object_id, object_name);

Need to check with dba then to see what will be the impact of creating additional index — Andrei Maieras, Jul 31 '15 at 08:35

Tuning SQL statement performance

2 Answers2