| Tuning my first query [message #528645] |
Wed, 26 October 2011 04:31  |
 |
Kwisatz78
Messages: 24
Registered: October 2011
|
Junior Member |
|
|
Hi all
I am trying to tune a query that is maxing out our CPU. The query is below along with the explain plan:
SELECT count(*)
from UAT_OCS.docmeta dmeta,
UAT_OCS.vw_custdocumentlevel dlevel,
UAT_OCS.vw_custdocumentcategory dcategory,
UAT_OCS.vw_custdocumenttype dtype,
UAT_OCS.vw_customclrnlist dclrn,
UAT_OCS.revisions rev,
UAT_OCS.documents doc
where dmeta.xDocumentCategory = dcategory.id (+)
and dmeta.xDocumentType = dtype.id (+)
and dmeta.xDocumentLevel = dlevel.id (+)
and dmeta.xClrnID = dclrn.clrn_id (+)
and rev.did = dmeta.did
and rev.drevisionid = (select MAX(drevisionid)
FROM UAT_OCS.revisions
where rev.ddocname = rev.ddocname)
and rev.dstatus = 'RELEASED'
and doc.did = dmeta.did
and doc.disprimary = 1
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1197 | 10030 (93)| 00:02:01 | | |
| 1 | SORT AGGREGATE | | 1 | 1197 | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1870M| 2085G| 10030 (93)| 00:02:01 | | |
| 3 | VIEW | VW_CUSTDOCUMENTCATEGORY | 1397 | 350K| 7 (15)| 00:00:01 | | |
| 4 | REMOTE | | | | | | DBCSP~ | R->S |
|* 5 | HASH JOIN RIGHT OUTER | | 22M| 19G| 837 (15)| 00:00:11 | | |
| 6 | VIEW | VW_CUSTOMCLRNLIST | 1397 | 211K| 7 (15)| 00:00:01 | | |
| 7 | REMOTE | | | | | | DBCSP~ | R->S |
|* 8 | HASH JOIN RIGHT OUTER | | 407K| 304M| 718 (2)| 00:00:09 | | |
| 9 | VIEW | VW_CUSTDOCUMENTTYPE | 1397 | 350K| 7 (15)| 00:00:01 | | |
| 10 | REMOTE | | | | | | DBCSP~ | R->S |
|* 11 | HASH JOIN RIGHT OUTER | | 20702 | 10M| 709 (2)| 00:00:09 | | |
| 12 | VIEW | VW_CUSTDOCUMENTLEVEL | 100 | 25700 | 3 (34)| 00:00:01 | | |
| 13 | REMOTE | | | | | | DBCSP~ | R->S |
|* 14 | HASH JOIN | | 1449 | 383K| 706 (1)| 00:00:09 | | |
|* 15 | HASH JOIN | | 1446 | 371K| 569 (2)| 00:00:07 | | |
|* 16 | HASH JOIN | | 1549 | 167K| 310 (2)| 00:00:04 | | |
|* 17 | TABLE ACCESS FULL | REVISIONS | 23231 | 816K| 154 (1)| 00:00:02 | | |
| 18 | VIEW | VW_SQ_1 | 22690 | 1661K| 155 (2)| 00:00:02 | | |
| 19 | HASH GROUP BY | | 22690 | 487K| 155 (2)| 00:00:02 | | |
| 20 | TABLE ACCESS FULL| REVISIONS | 23233 | 499K| 154 (1)| 00:00:02 | | |
| 21 | TABLE ACCESS FULL | DOCMETA | 21691 | 3219K| 258 (1)| 00:00:04 | | |
|* 22 | TABLE ACCESS FULL | DOCUMENTS | 21737 | 169K| 137 (1)| 00:00:02 | | |
-----------------------------------------------------------------------------------------------------------------------
Am I right in saying that the Outer Join to the view VW_CUSTDOCUMENTCATEGORY is where the problem is? It looks to me as though many rows are being returned and then thrown away i.e. 1870M (whatever M stands for?) are read and only 1397 used, is that correct?
I have been trying to alter the order of the tables in the FROM statement and also the WHERE clause but I always end up with the same plan, am I missing something here also?
Thanks in advance.
|
|
|
|
| Re: Tuning my first query [message #528647 is a reply to message #528645] |
Wed, 26 October 2011 04:41  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
M = million, K = thousand, G= billion.
Does the query really do count(*)? Because if it does those outer-joined tables are pointless.
Or did you replace the real select list? If you did, don't, oracle can give a different plan for select columns vs count(*).
Order of tables in from/where makes no difference to the CBO.
|
|
|
|
| Re: Tuning my first query [message #528648 is a reply to message #528647] |
Wed, 26 October 2011 04:51 |
|
Kwisatz78
Messages: 24
Registered: October 2011
|
Junior Member |
|
|
yes I changed the select to count(*) just to make it easier to read. Below is the full query and plan with all the statements
in them.
select dmeta.XPROJECTID iras_project_id,
doc.doriginalname filename,
dlevel.label document_level,
dcategory.label document_category,
NVL(dtype.label, dmeta.xdocumenttype) document_type,
rev.dInDate upload_date,
dmeta.xUserVersionNumber version_number,
decode(dmeta.xSiteId, null, dclrn.name
, NVL2(dclrn.name, dclrn.name || ' / ' || UCM_SVCS.GETSITENAME(dmeta.xSiteId), UCM_SVCS.GETSITENAME(dmeta.xSiteId))
) site,
SUBSTR(dmeta.xCLRNID, 0, INSTR(dmeta.xCLRNID, '@')-1) clrn,
-- '/cs/idcplg?IdcService=GET_FILE'||'&'||'dID='||doc.ddocid||'&'||'dDocName='||rev.ddocname||'&'||'Rendition=web'||'&'||'allowInterrupt=1'||'&'||'noSaveAs=1'||'&'||'fileName='||rev.ddocname||'.'||rev.dwebextension cs_url,
'/webcenter/ShowProperty?nodeId=%2Fucm.connection%2F'||rev.ddocname||'%2F%2FidcPrimaryFile'||'&'||'revision=latestreleased' wc_url,
dclrn.name clrn_name,
rev.drevisionid iras_version_number
from UAT_OCS.docmeta dmeta,
UAT_OCS.vw_custdocumentlevel dlevel,
UAT_OCS.vw_custdocumentcategory dcategory,
UAT_OCS.vw_custdocumenttype dtype,
UAT_OCS.vw_customclrnlist dclrn,
UAT_OCS.revisions rev,
UAT_OCS.documents doc
where dmeta.xDocumentCategory = dcategory.id (+)
and dmeta.xDocumentType = dtype.id (+)
and dmeta.xDocumentLevel = dlevel.id (+)
and dmeta.xClrnID = dclrn.clrn_id (+)
and rev.did = dmeta.did
and rev.drevisionid = (select MAX(drevisionid)
FROM UAT_OCS.revisions
where ddocname = rev.ddocname)
and rev.dstatus = 'RELEASED'
and doc.did = dmeta.did
and doc.disprimary = 1
ORDER BY dcategory.label, dtype.label, rev.dInDate DESC
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1870M| 3018G| | 677M (1)|999:59:59 | | |
| 1 | SORT ORDER BY | | 1870M| 3018G| 3567G| 677M (1)|999:59:59 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1870M| 3018G| | 10031 (93)| 00:02:01 | | |
| 3 | VIEW | VW_CUSTDOCUMENTCATEGORY | 1397 | 526K| | 7 (15)| 00:00:01 | | |
| 4 | REMOTE | | | | | | | DBCSP~ | R->S |
|* 5 | HASH JOIN RIGHT OUTER | | 22M| 28G| | 837 (15)| 00:00:11 | | |
| 6 | VIEW | VW_CUSTOMCLRNLIST | 1397 | 387K| | 7 (15)| 00:00:01 | | |
| 7 | REMOTE | | | | | | | DBCSP~ | R->S |
|* 8 | HASH JOIN RIGHT OUTER | | 407K| 412M| | 718 (2)| 00:00:09 | | |
| 9 | VIEW | VW_CUSTDOCUMENTTYPE | 1397 | 526K| | 7 (15)| 00:00:01 | | |
| 10 | REMOTE | | | | | | | DBCSP~ | R->S |
|* 11 | HASH JOIN RIGHT OUTER | | 20702 | 13M| | 709 (2)| 00:00:09 | | |
| 12 | VIEW | VW_CUSTDOCUMENTLEVEL | 100 | 38600 | | 3 (34)| 00:00:01 | | |
| 13 | REMOTE | | | | | | | DBCSP~ | R->S |
|* 14 | HASH JOIN | | 1449 | 411K| | 706 (1)| 00:00:09 | | |
|* 15 | HASH JOIN | | 1446 | 355K| | 569 (2)| 00:00:07 | | |
|* 16 | HASH JOIN | | 1549 | 119K| | 310 (2)| 00:00:04 | | |
| 17 | VIEW | VW_SQ_1 | 22690 | 709K| | 155 (2)| 00:00:02 | | |
| 18 | HASH GROUP BY | | 22690 | 487K| | 155 (2)| 00:00:02 | | |
| 19 | TABLE ACCESS FULL| REVISIONS | 23233 | 499K| | 154 (1)| 00:00:02 | | |
|* 20 | TABLE ACCESS FULL | REVISIONS | 23231 | 1066K| | 154 (1)| 00:00:02 | | |
| 21 | TABLE ACCESS FULL | DOCMETA | 21691 | 3664K| | 258 (1)| 00:00:04 | | |
|* 22 | TABLE ACCESS FULL | DOCUMENTS | 21737 | 827K| | 137 (1)| 00:00:02 | | |
-------------------------------------------------------------------------------------------------------------------------------
The plan has changed slightly but the main cost still seems to be where that RIGHT JOIN is, how can I tune this area to reduce the number of rows if changing the order of the tables has no affect?
I can see there are some full table scans, however if I am reading the plan right then these are not having an impact.
Thanks
[Updated on: Wed, 26 October 2011 05:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Tuning my first query [message #528668 is a reply to message #528662] |
Wed, 26 October 2011 06:02 |
|
Kwisatz78
Messages: 24
Registered: October 2011
|
Junior Member |
|
|
Ok here we go:
VW_CUSTDOCUMENTLEVEL
SELECT c1.code
||'@'
||oo.oid ID,
cd.designation LABEL,
1 APPLICATIONID
FROM concept@DBCSP_LINK c1
LEFT JOIN conceptrelationship@DBCSP_LINK cr
ON cr.targetconceptid=c1.id
LEFT JOIN conceptdesignation@DBCSP_LINK cd
ON cd.conceptid=c1.id
LEFT JOIN concept@DBCSP_LINK c2
ON c2.id=cr.targetconceptid
LEFT JOIN oidobject@DBCSP_LINK oo
ON oo.id =c1.codesystemid
WHERE c1.codesystemid=2031
AND cr.id IS NULL
VW_CUSTDOCUMENTCATEGORY
SELECT c2.code
||'@'
||oo2.oid ID,
cd.designation LABEL,
c1.code
||'@'
||oo1.oid CUSTDOCUMENTLEVELID
FROM concept@DBCSP_LINK c1
LEFT JOIN conceptrelationship@DBCSP_LINK cr
ON cr.targetconceptid=c1.id
LEFT JOIN conceptrelationship@DBCSP_LINK cr1
ON cr1.sourceconceptid=c1.id
LEFT JOIN concept@DBCSP_LINK c2
ON c2.id=cr1.targetconceptid
LEFT JOIN conceptdesignation@DBCSP_LINK cd
ON cd.conceptid=c2.id
LEFT JOIN oidobject@DBCSP_LINK oo1
ON oo1.id=c1.codesystemid
LEFT JOIN oidobject@DBCSP_LINK oo2
ON oo2.id =c2.codesystemid
WHERE c1.codesystemid=2031
AND cr.id IS NULL
VW_CUSTDOCUMENTTYPE
SELECT c3.code
||'@'
||oo3.oid ID,
cd.designation LABEL,
c2.code
||'@'
||oo2.oid CUSTDOCUMENTCATEGORYID,
c1.code
||'@'
||oo1.oid CUSTDOCUMENTLEVELID
FROM concept@DBCSP_LINK c1
LEFT JOIN conceptrelationship@DBCSP_LINK cr
ON cr.targetconceptid=c1.id
LEFT JOIN conceptrelationship@DBCSP_LINK cr1
ON cr1.sourceconceptid=c1.id
LEFT JOIN conceptrelationship@DBCSP_LINK cr2
ON cr2.sourceconceptid=cr1.targetconceptid
LEFT JOIN concept@DBCSP_LINK c2
ON c2.id=cr2.Sourceconceptid
LEFT JOIN concept@DBCSP_LINK c3
ON c3.id=cr2.targetconceptid
LEFT JOIN conceptdesignation@DBCSP_LINK cd
ON cd.conceptid=c3.id
LEFT JOIN oidobject@DBCSP_LINK oo1
ON oo1.id=c1.codesystemid
LEFT JOIN oidobject@DBCSP_LINK oo2
ON oo2.id=c2.codesystemid
LEFT JOIN oidobject@DBCSP_LINK oo3
ON oo3.id =c3.codesystemid
WHERE c1.codesystemid=2031
AND cr.id IS NULL
VW_CUSTOMCLRNLIST
SELECT ENTITY_II.EXTENSION CLRN_CODE,
ENTITY.NAME,
ENTITY_II.EXTENSION
||'@'
||OIDOBJECT.OID CLRN_ID
FROM ENTITY@DBCSP_LINK
LEFT JOIN ENTITY_II@DBCSP_LINK
ON ENTITY_II.ENTITYID = ENTITY.ID
LEFT JOIN OIDOBJECT@DBCSP_LINK
ON OIDOBJECT.ID =ENTITY_II.ROOTID
WHERE ENTITY.CODE ='CLRN@2.16.840.1.113883.2.1.3.8.5.11.1.106'
AND ENTITY_II.DISPLAYABLE='T'
|
|
|
|
| Re: Tuning my first query [message #528696 is a reply to message #528668] |
Wed, 26 October 2011 08:07 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd start by reviewing those views.
As far as I can tell VW_CUSTDOCUMENTLEVEL has no use for the join to c2.
Past that I'm not sure where to start, you've got a lot of outer-joins and remote tables involved both of which make performance tuning tricky.
I'd suggest looking at replacing the views with materialized views.
Others may have better suggestions.
|
|
|
|
|
|
|
|
| Re: Tuning my first query [message #528803 is a reply to message #528801] |
Thu, 27 October 2011 04:15 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I also suspect some (most?) of your outer-joins shouldn't be.
For example:
VW_CUSTOMCLRNLIST has an outer-join to JOIN ENTITY_II but the where clause specifies a value for ENTITY_II.DISPLAYABLE,
rendering the outer-join pointless.
Outer-joins should be the exception, not the rule.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
