Home » RDBMS Server » Performance Tuning » Outer Join killing Performance
| Outer Join killing Performance [message #189905] |
Mon, 28 August 2006 07:28  |
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  |
rleishman
Messages: 3728
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 |
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 |
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 |
|
|
|
| Re: Outer Join killing Performance [message #190260 is a reply to message #190166] |
Tue, 29 August 2006 22:51 |
rleishman
Messages: 3728
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 |
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 |
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 |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 #271953 is a reply to message #192758] |
Wed, 03 October 2007 08:08 |
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 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Tue Jun 25 20:03:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
