Home » RDBMS Server » Performance Tuning » Outer Join killing Performance
Outer Join killing Performance [message #189905] Mon, 28 August 2006 07:28 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
i am having folloing query which is taking good amount of time.
also it has cost=2861 in Explain Plan {with 87 rows as result}

If i remove the outer join in "cgm.lchannelseqnbr = cchm.lchannelseqnbr(+) " the query is running fast also the cost in explain plan goes down to 162 {with 18 rows as result}


SELECT DISTINCT cgm.strclientcd strclientcd, cgm.strgroupid strgroupid,
cgm.strgroupname grpname, cgm2.strgroupid grpid,
cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd,
cpm1.strcddesc grpstat, cca.straddrline1 grpaddr1,
cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3,
cca.strpostalcd postalcd, cgm1.strgroupname primegrpname,
cgm.strprimegroupid primegrpid, cchm.lchannelseqnbr channelid,
cchm.strchannelname strchannelname, cgm3.strgroupname parentgrpname,
cgm.strparentgroupid parentgrpid, cfd.nfsarefno fsano, cfd.strfsafirmname fsafirmname
FROM com_group_m cgm, com_group_m cgm1, com_group_m cgm2, com_group_m cgm3,
com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1, com_channel_m cchm, chm_fsa_dtl cfd
WHERE cgm.laddrseq = cca.laddrseq AND cgm.strclientcd = cgm2.strclientcd
AND cgm.strprimegroupid = cgm1.strgroupid AND cgm.strparentgroupid = cgm3.strgroupid(+)
AND cpm.iparamtypecd = 3004 AND cpm.nparamcd = cgm2.ngrouptyp
AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND cpm1.nparamcd = cgm2.nstatcd
AND cgm.lchannelseqnbr = cchm.lchannelseqnbr(+) AND cgm.strgroupid = cfd.strgroupid(+)
AND ( UPPER(NVL(cgm.strgroupname,'~')) like 'TEST%' OR UPPER(NVL(cca.straddrline1,'~')) like 'TEST%' OR UPPER(NVL(cca.straddrline2,'~')) like 'TEST%' OR UPPER(NVL(cca.straddrline3,'~')) like 'TEST%' ) ORDER BY grpname, primegrpname, grpaddr1


can anybody suggest me some ways to get same result avoiding Outer Join?

Regards,
Pratap
Re: Outer Join killing Performance [message #189909 is a reply to message #189905] Mon, 28 August 2006 07:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the EXPLAIN PLAN output for both the outer join query and the inner join query.

Make sure the queries AND the plan are enclosed in [code] [/code] tags so that we can read them.

Ross Leishman
Re: Outer Join killing Performance [message #189994 is a reply to message #189909] Mon, 28 August 2006 23:58 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
Please find the queries (with Outer Join and without Outer Join) and its' explain plan.

With Outer Join(cgm.lchannelseqnbr = cchm.lchannelseqnbr(+) )
SELECT DISTINCT cgm.strclientcd strclientcd, cgm.strgroupid strgroupid, cgm.strgroupname grpname, cgm2.strgroupid grpid, cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd, cpm1.strcddesc grpstat, cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3, cca.strpostalcd postalcd, cgm1.strgroupname primegrpname, cgm.strprimegroupid primegrpid, cchm.lchannelseqnbr channelid, cchm.strchannelname strchannelname, cgm3.strgroupname parentgrpname, cgm.strparentgroupid parentgrpid, cfd.nfsarefno fsano, cfd.strfsafirmname fsafirmname FROM com_group_m cgm, com_group_m cgm1, com_group_m cgm2, com_group_m cgm3, com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1, com_channel_m cchm, chm_fsa_dtl cfd WHERE cgm.laddrseq = cca.laddrseq AND cgm.strclientcd = cgm2.strclientcd AND cgm.strprimegroupid = cgm1.strgroupid AND cgm.strparentgroupid = cgm3.strgroupid(+) AND cpm.iparamtypecd = 3004 AND cpm.nparamcd = cgm2.ngrouptyp AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND cpm1.nparamcd = cgm2.nstatcd AND cgm.lchannelseqnbr = cchm.lchannelseqnbr(+) AND cgm.strgroupid = cfd.strgroupid(+) AND cgm.ngrouptyp = 4 ORDER BY grpname, primegrpname, grpaddr1

SELECT STATEMENT
SORT UNIQUE
HASH JOIN
INDEX FAST FULL SCAN CPSM_ID1
NESTED LOOPS
HASH JOIN
INDEX RANGE SCAN CPSM_ID1
HASH JOIN OUTER
HASH JOIN
HASH JOIN
HASH JOIN OUTER
HASH JOIN OUTER
TABLE ACCESS FULL COM_GROUP_M
TABLE ACCESS FULL COM_CHANNEL_M
TABLE ACCESS FULL COM_GROUP_M
TABLE ACCESS FULL COM_GROUP_M
TABLE ACCESS FULL COM_GROUP_M
TABLE ACCESS FULL CHM_FSA_DTL
TABLE ACCESS BY INDEX ROWID COM_CLIENT_ADDRESS
INDEX UNIQUE SCAN XPKCOM_CLIENT_ADDRESS


Without Outer Join(cgm.lchannelseqnbr = cchm.lchannelseqnbr )
SELECT DISTINCT cgm.strclientcd strclientcd, cgm.strgroupid strgroupid, cgm.strgroupname grpname, cgm2.strgroupid grpid, cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd, cpm1.strcddesc grpstat, cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3, cca.strpostalcd postalcd, cgm1.strgroupname primegrpname, cgm.strprimegroupid primegrpid, cchm.lchannelseqnbr channelid, cchm.strchannelname strchannelname, cgm3.strgroupname parentgrpname, cgm.strparentgroupid parentgrpid, cfd.nfsarefno fsano, cfd.strfsafirmname fsafirmname FROM com_group_m cgm, com_group_m cgm1, com_group_m cgm2, com_group_m cgm3, com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1, com_channel_m cchm, chm_fsa_dtl cfd WHERE cgm.laddrseq = cca.laddrseq AND cgm.strclientcd = cgm2.strclientcd AND cgm.strprimegroupid = cgm1.strgroupid AND cgm.strparentgroupid = cgm3.strgroupid(+) AND cpm.iparamtypecd = 3004 AND cpm.nparamcd = cgm2.ngrouptyp AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND cpm1.nparamcd = cgm2.nstatcd AND cgm.lchannelseqnbr = cchm.lchannelseqnbr AND cgm.strgroupid = cfd.strgroupid(+) AND cgm.ngrouptyp = 4 ORDER BY grpname, primegrpname, grpaddr1;

SELECT STATEMENT
SORT UNIQUE
HASH JOIN OUTER
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
HASH JOIN
HASH JOIN
HASH JOIN
HASH JOIN
TABLE ACCESS FULL COM_GROUP_M
TABLE ACCESS FULL COM_GROUP_M
INDEX FAST FULL SCAN CPSM_ID1
INDEX RANGE SCAN CPSM_ID1
TABLE ACCESS FULL COM_CHANNEL_M
TABLE ACCESS BY INDEX ROWID COM_CLIENT_ADDRESS
INDEX UNIQUE SCAN XPKCOM_CLIENT_ADDRESS
TABLE ACCESS BY INDEX ROWID COM_GROUP_M
INDEX UNIQUE SCAN XPKCOM_GROUP_M
TABLE ACCESS BY INDEX ROWID COM_GROUP_M
INDEX UNIQUE SCAN XPKCOM_GROUP_M
TABLE ACCESS FULL CHM_FSA_DTL

Thanks and Regards,
Pratap
Re: Outer Join killing Performance [message #190166 is a reply to message #189905] Tue, 29 August 2006 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could replace these two lines in the SELECT list
 cchm.lchannelseqnbr channelid
,cchm.strchannelname strchannelname

with this
(SELECT cchm.lchannelseqnbr FROM com_channel_m cchm WHERE AND cgm.lchannelseqnbr = cchm.lchannelseqnbr) channelid
,(SELECT cchm.strchannelname FROM com_channel_m cchm WHERE AND cgm.lchannelseqnbr = cchm.lchannelseqnbr) strchannelname
and remove CCHM from the list of tables and the where clause entirely.
Re: Outer Join killing Performance [message #190260 is a reply to message #190166] Tue, 29 August 2006 22:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I can't read your unformatted post. If JR's advice doesn't help, then go back and edit your post to format both the SQL's and Plans using [code] [/code] tags as I said earlier.
Re: Outer Join killing Performance [message #190355 is a reply to message #190260] Wed, 30 August 2006 06:02 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Rleishman,

Sorry, for my earlier unformatted post!!
JRowbottom's idea worked to some performance benefit over the existing one.


However, in case to getter beeter one please find the formatted code and plan.
Query using Outer Join (cost 2861; rows returned =87 )
SELECT DISTINCT cgm.strclientcd strclientcd, cgm.strgroupid strgroupid, cgm.strgroupname grpname, cgm2.strgroupid grpid, cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd, cpm1.strcddesc grpstat, cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3, cca.strpostalcd postalcd, cgm1.strgroupname primegrpname, cgm.strprimegroupid primegrpid, cchm.lchannelseqnbr channelid, cchm.strchannelname strchannelname, cgm3.strgroupname parentgrpname, cgm.strparentgroupid parentgrpid, cfd.nfsarefno fsano, cfd.strfsafirmname fsafirmname FROM com_group_m cgm, com_group_m cgm1, com_group_m cgm2, com_group_m cgm3, com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1, com_channel_m cchm, chm_fsa_dtl cfd WHERE cgm.laddrseq = cca.laddrseq AND cgm.strclientcd = cgm2.strclientcd AND cgm.strprimegroupid = cgm1.strgroupid AND cgm.strparentgroupid = cgm3.strgroupid(+) AND cpm.iparamtypecd = 3004 AND cpm.nparamcd = cgm2.ngrouptyp AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND cpm1.nparamcd = cgm2.nstatcd AND cgm.lchannelseqnbr = cchm.lchannelseqnbr(+) AND cgm.strgroupid = cfd.strgroupid(+) AND cgm.ngrouptyp = 4 ORDER BY grpname, primegrpname, grpaddr1;

SELECT STATEMENT
 SORT UNIQUE
  HASH JOIN
   INDEX FAST FULL SCAN CPSM_ID1
   NESTED LOOPS
    HASH JOIN OUTER
     HASH JOIN
      HASH JOIN OUTER
       HASH JOIN OUTER
        HASH JOIN
         TABLE ACCESS FULL COM_GROUP_M
         HASH JOIN
          INDEX RANGE SCAN CPSM_ID1
          TABLE ACCESS FULL COM_GROUP_M
        TABLE ACCESS FULL COM_CHANNEL_M
       TABLE ACCESS FULL CHM_FSA_DTL
      TABLE ACCESS FULL COM_GROUP_M
     TABLE ACCESS FULL COM_GROUP_M
    TABLE ACCESS BY INDEX ROWID COM_CLIENT_ADDRESS
     INDEX UNIQUE SCAN XPKCOM_CLIENT_ADDRESS

SELECT DISTINCT cgm.strclientcd strclientcd, cgm.strgroupid strgroupid, cgm.strgroupname grpname, cgm2.strgroupid grpid, cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd, cpm1.strcddesc grpstat, cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3, cca.strpostalcd postalcd, cgm1.strgroupname primegrpname, cgm.strprimegroupid primegrpid, cchm.lchannelseqnbr channelid, cchm.strchannelname strchannelname, cgm3.strgroupname parentgrpname, cgm.strparentgroupid parentgrpid, cfd.nfsarefno fsano, cfd.strfsafirmname fsafirmname FROM com_group_m cgm, com_group_m cgm1, com_group_m cgm2, com_group_m cgm3, com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1, com_channel_m cchm, chm_fsa_dtl cfd WHERE cgm.laddrseq = cca.laddrseq AND cgm.strclientcd = cgm2.strclientcd AND cgm.strprimegroupid = cgm1.strgroupid AND cgm.strparentgroupid = cgm3.strgroupid(+) AND cpm.iparamtypecd = 3004 AND cpm.nparamcd = cgm2.ngrouptyp AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND cpm1.nparamcd = cgm2.nstatcd AND cgm.lchannelseqnbr = cchm.lchannelseqnbr AND cgm.strgroupid = cfd.strgroupid(+) AND cgm.ngrouptyp = 4 ORDER BY grpname, primegrpname, grpaddr1;

SELECT STATEMENT
 SORT UNIQUE
  HASH JOIN OUTER
   NESTED LOOPS
    NESTED LOOPS OUTER
     NESTED LOOPS
      HASH JOIN
       HASH JOIN
        HASH JOIN
         HASH JOIN
          TABLE ACCESS FULL COM_GROUP_M
          TABLE ACCESS FULL COM_GROUP_M
         INDEX FAST FULL SCAN CPSM_ID1
        INDEX RANGE SCAN CPSM_ID1
       TABLE ACCESS FULL COM_CHANNEL_M
      TABLE ACCESS BY INDEX ROWID COM_CLIENT_ADDRESS
       INDEX UNIQUE SCAN XPKCOM_CLIENT_ADDRESS
     TABLE ACCESS BY INDEX ROWID COM_GROUP_M
      INDEX UNIQUE SCAN XPKCOM_GROUP_M
    TABLE ACCESS BY INDEX ROWID COM_GROUP_M
     INDEX UNIQUE SCAN XPKCOM_GROUP_M
   TABLE ACCESS FULL CHM_FSA_DTL


Please Suggest

Thanks and Regards,
Pratap
Re: Outer Join killing Performance [message #190528 is a reply to message #190355] Thu, 31 August 2006 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can repeat the same trick I showed on with the other two tables that you have outer joined to.
That should get rid of all the OJs, and free the optimizer up a bit.

Give it a try and let un know the result.
Re: Outer Join killing Performance [message #190608 is a reply to message #190528] Thu, 31 August 2006 06:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The primary difference between these two plans is that the faster one is performing Indexed Nested Loops joins for the last two joins to COM_GROUP_M, whereas the slow one is performing FTS and Hash Joins.

It is not the fact that it is an outer join that causes this, it is because CBO thinks that the Outer Join query will return (lots) more rows. Hash joins are efficient for large data volumes, so Oracle must be thinking that the inner-join will filter the large number down to a smaller result set - something that would not happen with the outer join.

If Nested Loops are faster, then it means one of two things:
1. You have a small result set
2. You have a large result set, but you are basing performance on the time taken to return the FIRST row (fast for Nested Loops) instead of EVERY row (slow for Nested Loops).

If the SQL returns only a small data set (say, <10000 rows) then do as JRowbottom suggested. This effectively "Nested" the retrieval of the OJ tables' columns. A similar result could be achieved by adding USE_NL hints to the SQL.

If the SQL returns a large result set, then do your timings again but make sure you work out how long it takes to return EVERY row.

Ross Leishman
Re: Outer Join killing Performance [message #190737 is a reply to message #190528] Fri, 01 September 2006 01:32 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi JRowbottom,Ross
Many thanks for your quick replies and help.
As suggested i have changed original code including Outer Joins
with that with Select Queries for each required field in Outer Join Table. The query is mentioned at the bottom of the message marked BLUE.
That has broght down the cost in Explain Plan drastically low.
Since i am not from the same development team i am just matching to see if the number of records are the same in each case.

However, this have posed some question in my mind. i Know this can not be online training, still i request You Seniors to help me understand the logic behind avoiding Outer Join this way.

Scenario :
In case of standard EMP and DEPT table, when i will (also) need records of DEPT for which there are no corresponding records in EMP,
i will use following
select dname,ename from emp,dept where emp.deptno(+)=dept.deptno
suppose in this case, just for sake of testing i use following queries, i am not getting correct results
select dept.dname,(select ename from emp where dept.deptno=emp.deptno) ename from dept - will produce error
select emp.ename,(select dname from dept where emp.deptno=dept.deptno) dname from emp - will give only matching records for both tables

so which other condition in the query, i have uploaded is avoiding this situation inspite of by-passing the Outer Join

Please suggest and Help.

Thanks and Regards,
Pratap

SELECT DISTINCT cgm.lchannelseqnbr,cgm.strclientcd strclientcd, cgm.strgroupid strgroupid, cgm.strgroupname grpname, 
cgm2.strgroupid grpid, cpm.strcddesc grptype, cgm2.ngrouptyp grptypecd, cpm1.strcddesc grpstat, 
cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.straddrline3 grpaddr3, cca.strpostalcd postalcd, 
cgm1.strgroupname primegrpname, cgm.strprimegroupid primegrpid, 
 cgm3.strgroupname parentgrpname, cgm.strparentgroupid parentgrpid, 
 [COLOR=blue](SELECT cchm.lchannelseqnbr FROM com_channel_m cchm WHERE cgm.lchannelseqnbr = cchm.lchannelseqnbr) channelid,
(SELECT cchm.strchannelname FROM com_channel_m cchm WHERE cgm.lchannelseqnbr = cchm.lchannelseqnbr) strchannelname,
(select cfd.nfsarefno from chm_fsa_dtl cfd where cgm.strgroupid = cfd.strgroupid) fsano,
(select cfd.strfsafirmname from chm_fsa_dtl cfd where cgm.strgroupid = cfd.strgroupid) fsafirmname[/COLOR]
FROM com_group_m cgm, com_group_m cgm1, 
com_group_m cgm2, com_group_m cgm3, com_client_address cca, com_param_system_m cpm, com_param_system_m cpm1 
WHERE          cgm.laddrseq = cca.laddrseq AND      
cgm.strclientcd = cgm2.strclientcd AND      cgm.strprimegroupid = cgm1.strgroupid AND      
cgm.strparentgroupid = cgm3.strgroupid(+) AND      cpm.iparamtypecd = 3004 AND      
cpm.nparamcd = cgm2.ngrouptyp AND      cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017,6314) AND      
cpm1.nparamcd = cgm2.nstatcd AND      cgm.ngrouptyp = 4  ORDER BY grpname, primegrpname, grpaddr1 

Re: Outer Join killing Performance [message #190894 is a reply to message #190737] Fri, 01 September 2006 22:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JR's technique does not replace Outer Joins. It only works when you can guarantee only ZERO or ONE row to match in the outer table. In the EMP/DEPT table, there are DEPTs with 2 or more EMP rows, so the technique fails.

JR took an educated guess that nesting was making the inner-join version faster and showed you the sub-query-expression technique, which is just a simple way to nest lookups on outer tables.

As I said earlier, you could get a similar result using USE_NL hints. This is the technique you should use if the outer tables can return two or more rows for each row in the inner table.

SELECT /*+ USE_NL(cgm3, cchm, cfd) */  
           DISTINCT cgm.strclientcd strclientcd
                  , cgm.strgroupid strgroupid
                  , cgm.strgroupname grpname
                  , cgm2.strgroupid grpid
                  , cpm.strcddesc grptype
                  , cgm2.ngrouptyp grptypecd
                  , cpm1.strcddesc grpstat
                  , cca.straddrline1 grpaddr1
                  , cca.straddrline2 grpaddr2
                  , cca.straddrline3 grpaddr3
                  , cca.strpostalcd postalcd
                  , cgm1.strgroupname primegrpname
                  , cgm.strprimegroupid primegrpid
                  , cchm.lchannelseqnbr channelid
                  , cchm.strchannelname strchannelname
                  , cgm3.strgroupname parentgrpname
                  , cgm.strparentgroupid parentgrpid
                  , cfd.nfsarefno fsano
                  , cfd.strfsafirmname fsafirmname
FROM     com_group_m cgm
         , com_group_m cgm1
         , com_group_m cgm2
         , com_group_m cgm3
         , com_client_address cca
         , com_param_system_m cpm
         , com_param_system_m cpm1
         , com_channel_m cchm
         , chm_fsa_dtl cfd
WHERE    cgm.laddrseq = cca.laddrseq
  AND cgm.strclientcd = cgm2.strclientcd
  AND cgm.strprimegroupid = cgm1.strgroupid
  AND cgm.strparentgroupid = cgm3.strgroupid (+) 
  AND cpm.iparamtypecd = 3004
  AND cpm.nparamcd = cgm2.ngrouptyp
  AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017
                                               , 6314)
  AND cpm1.nparamcd = cgm2.nstatcd
  AND cgm.lchannelseqnbr = cchm.lchannelseqnbr (+) 
  AND cgm.strgroupid = cfd.strgroupid (+) 
  AND (UPPER(NVL(cgm.strgroupname,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline1,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline2,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline3,'~')) LIKE 'TEST%')
ORDER BY grpname
         , primegrpname
         , grpaddr1


Ross Leishman
Re: Outer Join killing Performance [message #192470 is a reply to message #190894] Tue, 12 September 2006 09:13 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member


Hi Ross,JRowbottom
Really sorry for delayed reply (was away from place)

i tried using /*+ USE_NL(cgm3, cchm, cfd) */ hint, however it is giving even poorer response.
below are the statistics for the tables and the Explain plan after using the above Hint.

Note that Cost has increased upto 10455.
Statistics
select count(*) FROM com_group_m cgm
3819
select count(*) FROM com_client_address cca
442437
select count(*) FROM com_param_system_m cpm
1798
select count(*) FROM com_channel_m cchm
2
select count(*) FROM chm_fsa_dtl cfd
38


SELECT /*+ USE_NL(cgm3, cchm, cfd) */  
           DISTINCT cgm.strclientcd strclientcd
                  , cgm.strgroupid strgroupid
                  , cgm.strgroupname grpname
                  , cgm2.strgroupid grpid
                  , cpm.strcddesc grptype
                  , cgm2.ngrouptyp grptypecd
                  , cpm1.strcddesc grpstat
                  , cca.straddrline1 grpaddr1
                  , cca.straddrline2 grpaddr2
                  , cca.straddrline3 grpaddr3
                  , cca.strpostalcd postalcd
                  , cgm1.strgroupname primegrpname
                  , cgm.strprimegroupid primegrpid
                  , cchm.lchannelseqnbr channelid
                  , cchm.strchannelname strchannelname
                  , cgm3.strgroupname parentgrpname
                  , cgm.strparentgroupid parentgrpid
                  , cfd.nfsarefno fsano
                  , cfd.strfsafirmname fsafirmname
FROM     com_group_m cgm
         , com_group_m cgm1
         , com_group_m cgm2
         , com_group_m cgm3
         , com_client_address cca
         , com_param_system_m cpm
         , com_param_system_m cpm1
         , com_channel_m cchm
         , chm_fsa_dtl cfd
WHERE    cgm.laddrseq = cca.laddrseq
  AND cgm.strclientcd = cgm2.strclientcd
  AND cgm.strprimegroupid = cgm1.strgroupid
  AND cgm.strparentgroupid = cgm3.strgroupid (+) 
  AND cpm.iparamtypecd = 3004
  AND cpm.nparamcd = cgm2.ngrouptyp
  AND cpm1.iparamtypecd = DECODE(cgm2.ngrouptyp,5,9017
                                               , 6314)
  AND cpm1.nparamcd = cgm2.nstatcd
  AND cgm.lchannelseqnbr = cchm.lchannelseqnbr (+) 
  AND cgm.strgroupid = cfd.strgroupid (+) 
  AND (UPPER(NVL(cgm.strgroupname,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline1,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline2,'~')) LIKE 'TEST%'
        OR UPPER(NVL(cca.straddrline3,'~')) LIKE 'TEST%')
ORDER BY grpname
         , primegrpname
         , grpaddr1;

SELECT STATEMENT
 SORT UNIQUE
  NESTED LOOPS OUTER
   HASH JOIN
    INDEX FAST FULL SCAN CPSM_ID1
    NESTED LOOPS OUTER
     NESTED LOOPS OUTER
      NESTED LOOPS
       HASH JOIN
        TABLE ACCESS FULL COM_GROUP_M
        HASH JOIN
         HASH JOIN
          INDEX RANGE SCAN CPSM_ID1
          TABLE ACCESS FULL COM_GROUP_M
         TABLE ACCESS FULL COM_GROUP_M
       TABLE ACCESS BY INDEX ROWID COM_CLIENT_ADDRESS
        INDEX UNIQUE SCAN XPKCOM_CLIENT_ADDRESS
      TABLE ACCESS BY INDEX ROWID COM_CHANNEL_M
       INDEX UNIQUE SCAN SYS_C0011475
     TABLE ACCESS FULL CHM_FSA_DTL
   TABLE ACCESS BY INDEX ROWID COM_GROUP_M
    INDEX UNIQUE SCAN XPKCOM_GROUP_M

i have tried putting different combinations (in different Order too) in the USE_NL hint, however could not get better performance.

Please suggest and help.

Regards,
Pratap
Re: Outer Join killing Performance [message #192673 is a reply to message #192470] Wed, 13 September 2006 04:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The problem with this plan is the FULL scan on CHM_FSA_DTL. If there is no index on cfd.strgroupid, then you want to omit it from the USE_NL hint.

I may have mis-led you earlier. USE_NL really has nothing to do with outer-joins per-se. It is just a join method that is efficient when you want to perform an indexed lookup on a table a small number of times.

For example, consider a join involving tables A, B, and C, each with 1M rows. The join of A and B returns 1M rows, so we are going to lookup table C one million times (lots!). Nested Loops would be a bad join method - HASH would be much better.

But what if the join of table A and B returned only 5000 rows. Then we would only have to lookup C 5000 times. Nested Loops would be appropriate here.

The problem is that Oracle cannot always easily tell how many rows a join will yield. In my example, if the join-key was unique, Oracle would probably assume that every row in table A had exactly one match in table B, and that a join would yield 1M rows. If this assumption is incorrect (ie. matches are uncommon) then Oracle will incorrectly use a HASH join to retrieve table C.

Only YOU know the size of the tables, the available indexes, and the cardinality of the join keys. Your SQL is sufficiently complex that it would take hours for me to optimize it for you without being there. But here are two very simple join rules you can follow:

- Ensure that restrictive filters and highly-selective joins are performed early. eg. The example above where A and B had 1M rows but only 5000 that joined would probably be a better place to start than (say) joining B to C, which may return 1M rows. Use the ORDERED or LEADING hint if Oracle is choosing a poor starting point for a join.

- To lookup a small number of rows in a large table, use a Nested Loop join with an Index (or a sub-query expression as described in earlier posts). Most other joins are generally best to use a hash join. You can use the USE_NL and USE_HASH hints to control join methods if the optimizer is getting it wrong.

If you are interested, you can achieve most tuning goals by gathering statistics, and using the LEADING and CARDINALITY hints to give Oracle additional information not available in the statistics. This is a more subtle and future-proof way of tuning than hints like ORDERED, INDEX, and USE_* that are considered a bit heavy-handed these days.

Ross Leishman
Re: Outer Join killing Performance [message #192758 is a reply to message #192673] Wed, 13 September 2006 08:22 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Ross,
Many thanks for your detailed analysis and advice.
I will work on these guidelines and will let you know the results.

Thanks and Regards,
Pratap
Re: Outer Join killing Performance [message #192917 is a reply to message #192758] Thu, 14 September 2006 04:03 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
One other suggestion. What is the functional need for the distinct? Any chance you can get rid of it?

Regards,
Sabine
Re: Outer Join killing Performance [message #271953 is a reply to message #192758] Wed, 03 October 2007 08:08 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

Any results?

pratapsz wrote on Wed, 13 September 2006 10:22
Hi Ross,
Many thanks for your detailed analysis and advice.
I will work on these guidelines and will let you know the results.

Thanks and Regards,
Pratap


Regards,



mson77
Re: Outer Join killing Performance [message #271988 is a reply to message #189905] Wed, 03 October 2007 10:24 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hello Mson77,

I appreciate the fact that i should have updated the latest status, however ,it's really diificult to update the information soon

It has crossed yearly anniversary for the post

I will try my best to update it.

Thanks for your participation in the thread.

Regards,
Pratap
Re: Outer Join killing Performance [message #271992 is a reply to message #271988] Wed, 03 October 2007 10:37 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

First.. thank you for your kind attention.

I started studying oracle since 2 months... and besides oracle official documentation... orafaq is my source of knowledge.
Every spare time that I have... I get into orafaq and read old topics. Old in age but fresh in content.

Then I took the liberty to reply this thread as you wrote that you would update the results to the orafaq community.
Obviously it ages anniversary... but only if you can.


Also I would appreciate a lot if you could update also your other thread :
http://www.orafaq.com/forum/m/270319/110238/#msg_num_21

pratapsz wrote on Thu, 27 September 2007 10:03
Many Thanks for suggesting the changes.

I will see to complete the remaining exercise and share the results in this thread.

Thanks and Regards,
Pratap


Thank you again,


mson77
Previous Topic: Tuning query (insert using select)
Next Topic: sql performance tuning
Goto Forum:
  


Current Time: Wed Dec 07 22:15:24 CST 2016

Total time taken to generate the page: 0.13977 seconds