Performance tuning or alternate to distinct clause

Question

I have this query with a distinct clause:

SELECT      --  /*+ first_rows  */
     distinct   a.sub_id, b.status,
            pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type,
            c.svc_provider_nm, fn_sp_get_svc_plan (a.sub_id) svc_plan,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                    'contact.name.salutation'
                                    ) salutation,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                        'first_name'
                                        ) first_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'contact.name.middle'
                                    ) middle_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                            'last_name'
                                        ) last_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'company_name') company_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'itc_account') itc_accout,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'phones.home.number'
                                    ) phone_number,
            pkg_sp_subbrief.get_location_parm
                                        (a.sub_id,
                                            'address_1'
                                        ) addr_home_address,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'city'
                                                ) addr_home_city,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'prov'
                                                ) addr_home_prov,
            pkg_sp_subbrief.get_location_parm
                                    (a.sub_id,
                                        'postal_cd'
                                    ) addr_home_postal_code,
            pkg_sp_subbrief.get_location_parm
                                            (a.sub_id,
                                            'country'
                                            ) addr_home_country,
            e.val cm_mta_mac, ' ' telephone_number,
            pkg_sp_subbrief.get_subsvc_parm_first (a.sub_id,
                                                    3374
                                                    ) alpha_tag,
            NVL (j.voipdn1, ' ') voip_dn1,
            NVL (j.mtavoiceport, ' ') mta_voice_port
        FROM svc_provider c,
            ref_status b,
            sub a,
            sub_svc d,
            sub_svc_parm e,
            (SELECT f.sub_id, g.val voipdn1, i.val mtavoiceport
                FROM sub_svc f, sub_svc_parm g, sub_svc_parm i
                WHERE f.svc_id = SvcId('smp_dial_tone_access')
                AND f.sub_svc_status_id ! = 29
                AND f.sub_svc_id = g.sub_svc_id
                AND g.parm_id = get_cgo_parm_id('voip_dn1', GET_CLASS_ID('SubSvcSpec'), SvcID('smp_dial_tone_access'))
                AND f.sub_svc_id = i.sub_svc_id
                AND i.parm_id = 20410) j
        WHERE a.svc_provider_id = c.svc_provider_id
        AND a.sub_status_id = b.status_id
        AND a.sub_id = d.sub_id
        AND d.sub_svc_id = e.sub_svc_id
        AND (e.parm_id = 1254 OR e.parm_id = 20249)
        AND d.sub_svc_status_id != 29
        AND a.sub_status_id != 9
        AND a.sub_id = j.sub_id(+);

This query returns me 10,146 rows and time to execute is almost 135 secs

The explain plan for this is:

Execution Plan
----------------------------------------------------------
Plan hash value: 2622307916

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  |     Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  | 10409 |      1118K|       |  2540   (4)| 00:00:31 |
|   1 |  HASH UNIQUE                     |                  | 10409 |      1118K|  1240K|  2540   (4)| 00:00:31 |
|*  2 |   HASH JOIN RIGHT OUTER          |                  | 10409 |      1118K|       |  2279   (4)| 00:00:28 |
|   3 |    VIEW                          |                  |   899 |     23374 |       |  1340   (6)| 00:00:17 |
|*  4 |     HASH JOIN                    |                  |   899 |     53940 |       |  1340   (6)| 00:00:17 |
|*  5 |      HASH JOIN                   |                  |   885 |     35400 |       |   918   (8)| 00:00:12 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| SUB_SVC          |   877 |     17540 |       |   434   (0)| 00:00:06 |
|*  7 |        INDEX RANGE SCAN          | SUB_SVC_IX2      |   951 |           |       |     3   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL          | SUB_SVC_PARM     |  1770 |     35400 |       |   483  (14)| 00:00:06 |
|*  9 |      TABLE ACCESS FULL           | SUB_SVC_PARM     |  2632 |     52640 |       |   422   (2)| 00:00:06 |
|* 10 |    HASH JOIN                     |                  | 10409 |       853K|       |   939   (2)| 00:00:12 |
|* 11 |     TABLE ACCESS FULL            | REF_STATUS       |    95 |      2280 |       |     3   (0)| 00:00:01 |
|* 12 |     HASH JOIN                    |                  | 10409 |       609K|       |   935   (2)| 00:00:12 |
|  13 |      VIEW                        | index$_join$_001 |    49 |       588 |       |     3  (34)| 00:00:01 |
|* 14 |       HASH JOIN                  |                  |       |           |       |            |          |
|  15 |        INDEX FAST FULL SCAN      | SVC_PROVIDER_PK  |    49 |       588 |       |     1   (0)| 00:00:01 |
|  16 |        INDEX FAST FULL SCAN      | SVC_PROVIDER_UK1 |    49 |       588 |       |     1   (0)| 00:00:01 |
|* 17 |      HASH JOIN                   |                  | 10409 |       487K|       |   932   (2)| 00:00:12 |
|* 18 |       TABLE ACCESS FULL          | SUB              |  8777 |       111K|       |    53   (0)| 00:00:01 |
|* 19 |       HASH JOIN                  |                  | 10607 |       362K|       |   878   (2)| 00:00:11 |
|* 20 |        TABLE ACCESS FULL         | SUB_SVC_PARM     | 10607 |       207K|       |   423   (2)| 00:00:06 |
|* 21 |        TABLE ACCESS FULL         | SUB_SVC          | 90284 |      1322K|       |   454   (1)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."SUB_ID"="J"."SUB_ID"(+))
4 - access("F"."SUB_SVC_ID"="I"."SUB_SVC_ID")
5 - access("F"."SUB_SVC_ID"="G"."SUB_SVC_ID")
6 - filter("F"."SUB_SVC_STATUS_ID"<>29)
7 - access("F"."SVC_ID"="SVCID"('smp_dial_tone_access'))
8 - filter("G"."PARM_ID"="GET_CGO_PARM_ID"('voip_dn1',"GET_CLASS_ID"  ('SubSvcSpec'),"SVCID"('smp_dial_tone_access')))
9 - filter("I"."PARM_ID"=20410)
10 - access("A"."SUB_STATUS_ID"="B"."STATUS_ID")
11 - filter("B"."STATUS_ID"<>9)
12 - access("A"."SVC_PROVIDER_ID"="C"."SVC_PROVIDER_ID")
14 - access(ROWID=ROWID)
17 - access("A"."SUB_ID"="D"."SUB_ID")
18 - filter("A"."SUB_STATUS_ID"<>9)
19 - access("D"."SUB_SVC_ID"="E"."SUB_SVC_ID")
20 - filter("E"."PARM_ID"=1254 OR "E"."PARM_ID"=20249)
21 - filter("D"."SUB_SVC_STATUS_ID"<>29)

Statistics
----------------------------------------------------------

   470461  recursive calls
        0  db block gets
 13591783  consistent gets
        0  physical reads
        0  redo size
  1272441  bytes sent via SQL*Net to client
     7960  bytes received via SQL*Net from client
      678  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
    10146  rows processed

When I remove the distinct clause from the query it execute in 4 secs BUT QUERY RETURNS 10163 rows which means it returns duplicate rows as well.

This is execution plan without distinct clause:

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 3514824003

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  | 10409 |  3608K|  2279   (4)| 00:00:28 |
|*  1 |  HASH JOIN RIGHT OUTER          |                  | 10409 |  3608K|  2279   (4)| 00:00:28 |
|   2 |   VIEW                          |                  |   899 |   237K|  1340   (6)| 00:00:17 |
|*  3 |    HASH JOIN                    |                  |   899 | 53940 |  1340   (6)| 00:00:17 |
|*  4 |     HASH JOIN                   |                  |   885 | 35400 |   918   (8)| 00:00:12 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| SUB_SVC          |   877 | 17540 |   434   (0)| 00:00:06 |
|*  6 |       INDEX RANGE SCAN          | SUB_SVC_IX2      |   951 |       |     3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL          | SUB_SVC_PARM     |  1770 | 35400 |   483  (14)| 00:00:06 |
|*  8 |     TABLE ACCESS FULL           | SUB_SVC_PARM     |  2632 | 52640 |   422   (2)| 00:00:06 |
|*  9 |   HASH JOIN                     |                  | 10409 |   853K|   939   (2)| 00:00:12 |
|* 10 |    TABLE ACCESS FULL            | REF_STATUS       |    95 |  2280 |     3   (0)| 00:00:01 |
|* 11 |    HASH JOIN                    |                  | 10409 |   609K|   935   (2)| 00:00:12 |
|  12 |     VIEW                        | index$_join$_001 |    49 |   588 |     3  (34)| 00:00:01 |
|* 13 |      HASH JOIN                  |                  |       |       |            |          |
|  14 |       INDEX FAST FULL SCAN      | SVC_PROVIDER_PK  |    49 |   588 |     1   (0)| 00:00:01 |
|  15 |       INDEX FAST FULL SCAN      | SVC_PROVIDER_UK1 |    49 |   588 |     1   (0)| 00:00:01 |
|* 16 |     HASH JOIN                   |                  | 10409 |   487K|   932   (2)| 00:00:12 |
|* 17 |      TABLE ACCESS FULL          | SUB              |  8777 |   111K|    53   (0)| 00:00:01 |
|* 18 |      HASH JOIN                  |                  | 10607 |   362K|   878   (2)| 00:00:11 |
|* 19 |       TABLE ACCESS FULL         | SUB_SVC_PARM     | 10607 |   207K|   423   (2)| 00:00:06 |
|* 20 |       TABLE ACCESS FULL         | SUB_SVC          | 90284 |  1322K|   454   (1)| 00:00:06 |
----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("A"."SUB_ID"="J"."SUB_ID"(+))
 3 - access("F"."SUB_SVC_ID"="I"."SUB_SVC_ID")
 4 - access("F"."SUB_SVC_ID"="G"."SUB_SVC_ID")
 5 - filter("F"."SUB_SVC_STATUS_ID"<>29)
 6 - access("F"."SVC_ID"="SVCID"('smp_dial_tone_access'))
 7 - filter("G"."PARM_ID"="GET_CGO_PARM_ID"('voip_dn1',"GET_CLASS_ID"  ('SubSvcSpec'),"SVCID"('smp_dial_tone_access')))
 8 - filter("I"."PARM_ID"=20410)
 9 - access("A"."SUB_STATUS_ID"="B"."STATUS_ID")
10 - filter("B"."STATUS_ID"<>9)
11 - access("A"."SVC_PROVIDER_ID"="C"."SVC_PROVIDER_ID")
13 - access(ROWID=ROWID) 
16 - access("A"."SUB_ID"="D"."SUB_ID")
17 - filter("A"."SUB_STATUS_ID"<>9)  
18 - access("D"."SUB_SVC_ID"="E"."SUB_SVC_ID")
19 - filter("E"."PARM_ID"=1254 OR "E"."PARM_ID"=20249)
20 - filter("D"."SUB_SVC_STATUS_ID"<>29)


Statistics
----------------------------------------------------------
   470461  recursive calls
        0  db block gets
 13592455  consistent gets
        0  physical reads
        0  redo size
  1168343  bytes sent via SQL*Net to client
     7971  bytes received via SQL*Net from client
      679  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
    10163  rows processed

So I am looking for any alternative to distinct clause to tune this query.

My DB version is:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

`4 secs` after full fetch? please, could you provide plan for query without `distinct`? — , Jul 20 '15 at 08:58
How long does it take if you put the distinct back in - the 4 second result might be because you now have all the relevant data in the buffer cache, so you're doing much less physical I/O. Try running the distinct query a few times to see if the speed varies because of that. — Alex Poole, Jul 20 '15 at 10:16
@Dmitry Agibov -- i have edited the question now it includes the execution plan without distinct clause — mradul, Jul 20 '15 at 11:32
@ Alex Poole -- i have done it many times with distinct clause and without distinct clause but it doesn't make any much difference. I have also tried to remove the function calls from the select statement but gaian it is not making any impact on query execution time. — mradul, Jul 20 '15 at 11:33
Find that actual duplicate records and work out why you are getting them so that distinct is no longer necessary. Are you joining incorrectly to a table? Is there some dirty data that would benefit from a unique constraint? — Nick.Mc, Jul 20 '15 at 11:40
@ Nick.McDermaid -- can you please suggest me what could be the reasons for the duplication and how to avoid it. because this query is written by someone else and i have to understand the logic before re-writing the query. so if you can please tell me some points on which i look to remove these duplications — mradul, Jul 20 '15 at 11:56
refer this link if it helps https://community.oracle.com/thread/860908?start=0&tstart=0 — Shravan Yadav, Jul 20 '15 at 16:05
Yes that's exactly it - you have to understand the logic. Have you found the duplicates yet? That is your first task. — Nick.Mc, Jul 21 '15 at 02:37
The main thing to look for is your join columns. If one record joins to another table which has two matching records then you get two records out. But the only way to find this is _find the actual duplicate rows_ — Nick.Mc, Jul 21 '15 at 02:40
@nick-mcdermaid -- I will check for duplicates but i think it is going to take my whole. ain't no there is a way in SQL which will give output of two SQL's where data in not present in one SQL but present in the second SQL. I was trying do this by using MINUS clause one query with DISTINCT and one without DISTINCT but it returns no rows — mradul, Jul 21 '15 at 06:17
You will find the duplicate rows when you group by all output columns and add 'having count(*) > 1' — Frank Ockenfuss, Jul 21 '15 at 08:48
OK so now why are they duplicates? You need to dig back into your source query and find out why these duplicates are appearing. Is it because a table you are joining to creating a duplicate or is it be cause the data is incorrect? — Nick.Mc, Jul 21 '15 at 22:53

Engin · Accepted Answer · 2015-07-21T14:34:26.430

I'd wrap the whole distinct query by taking the functions out and then selecting those columns on the outer query. Number of function calls should be reduced and the distinct also should be faster this way.

SELECT *, pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                    'contact.name.salutation'
                                    ) salutation,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                        'first_name'
                                        ) first_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'contact.name.middle'
                                    ) middle_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                            'last_name'
                                        ) last_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'company_name') company_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'itc_account') itc_accout,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'phones.home.number'
                                    ) phone_number,
            pkg_sp_subbrief.get_location_parm
                                        (a.sub_id,
                                            'address_1'
                                        ) addr_home_address,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'city'
                                                ) addr_home_city,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'prov'
                                                ) addr_home_prov,
            pkg_sp_subbrief.get_location_parm
                                    (a.sub_id,
                                        'postal_cd'
                                    ) addr_home_postal_code,
            pkg_sp_subbrief.get_location_parm
                                            (a.sub_id,
                                            'country'
                                            ) addr_home_country,\
            pkg_sp_subbrief.get_subsvc_parm_first (a.sub_id,
                                                    3374
                                                    ) alpha_tag,
            fn_sp_get_svc_plan (a.sub_id) svc_plan,
            pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type
FROM (                                          
    SELECT      --  /*+ first_rows  */
         distinct   a.sub_id, b.status,
                c.svc_provider_nm,
                e.val cm_mta_mac, ' ' telephone_number,
                NVL (j.voipdn1, ' ') voip_dn1,
                NVL (j.mtavoiceport, ' ') mta_voice_port
            FROM svc_provider c,
                ref_status b,
                sub a,
                sub_svc d,
                sub_svc_parm e,
                (SELECT f.sub_id, g.val voipdn1, i.val mtavoiceport
                    FROM sub_svc f, sub_svc_parm g, sub_svc_parm i
                    WHERE f.svc_id = SvcId('smp_dial_tone_access')
                    AND f.sub_svc_status_id ! = 29
                    AND f.sub_svc_id = g.sub_svc_id
                    AND g.parm_id = get_cgo_parm_id('voip_dn1', GET_CLASS_ID('SubSvcSpec'), SvcID('smp_dial_tone_access'))
                    AND f.sub_svc_id = i.sub_svc_id
                    AND i.parm_id = 20410) j
            WHERE a.svc_provider_id = c.svc_provider_id
            AND a.sub_status_id = b.status_id
            AND a.sub_id = d.sub_id
            AND d.sub_svc_id = e.sub_svc_id
            AND (e.parm_id = 1254 OR e.parm_id = 20249)
            AND d.sub_svc_status_id != 29
            AND a.sub_status_id != 9
            AND a.sub_id = j.sub_id(+)
    )

@Engin-- i have removed the functions already and by removing the functions it helps in significant performance increase but after removing the functions it still taking time 115 secs to execute.So as nick stated i need to figure out the faulty join condition which i am finding to figure out is difficult. — mradul, Jul 22 '15 at 07:13
@Engin-- Very well this is very awesome the query is taking only about 8-9 secs and giving me the exact result.you re-arrange the query awesomely simple.Thanks and you really are good ! — mradul, Jul 23 '15 at 09:25
@mradul thank you, I'm really happy to help but I want to point out that this is intended to be a quick fix for the issue, though I never support using functions before you're done with querying. I'm on the same page with Nick.McDermaid about finding out the root cause of duplicate values. It could just be bad data or a design fault but it has to be figured either way. — Engin, Jul 23 '15 at 19:57
@Engin-- I will try to figure out the cause of duplication and i also learned that functions should not be used as they lead the query to recursive calls. — mradul, Jul 24 '15 at 05:31

Performance tuning or alternate to distinct clause

1 Answers1