| Query slow [message #472333] |
Fri, 20 August 2010 00:19  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
One of my query is taking 17 seconds to execute, can we reduce the timing..
Query is
select count(1)
FROM ((HCLT_PARKING_LOT IST JOIN EFormDynamicFieldInstance EDFI ON
IST.ITEMTYPE = EDFI.ITEMTYPE AND IST.ITEMID = EDFI.ITEMID AND
ist.ownertype=edfi.ownertype and ist.ownerid=edfi.ownerid and
IST.OwnerType = 'Prj' AND IST.OwnerID = 76129 AND IST.ItemType = 'TCK_f' left
outer join EnterpriseUser EU2 on
EU2.userID = IST.CM_CHECKINCHECKOUTBY) left outer
join((WorkflowItemData WFID left outer join WorkflowStage WFS on
WFID.currentStageID = WFS.StageID) left outer join
ENTERPRISEUSER EU on WFID.currentParty = eu.userID)
on(IST.ItemType = WFID.itemtype AND IST.ItemId = WFID.itemid ))
left outer join ITEMTRACEBILITY_VIEW itv ON EDFI.OwnerType =
itv.FromOwnerType
and EDFI.OwnerID =
itv.FromOwnerID
and EDFI.ItemType =
itv.FromChildItemType
and EDFI.ITemID = itv.FromItemID,
ENTERPRISEUSER EU1
WHERE EU1.USERID = IST.CM_CREATEDBY
AND (WFID.CURRENTSTAGEID = -1 OR -1 = -1)
AND (IST.CM_CREATEDBY = 0 OR 0 = 0)
AND (IST.CM_ACCESSCONTROL <= 9 OR IST.CM_CREATEDBY = 1)
AND IST.CM_OVERALLSTATUS in ('Open', 'PastDue')
Explain plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15
| 1 | SORT AGGREGATE | | 1 | 15
| 2 | NESTED LOOPS OUTER | | 2 | 30
|* 3 | HASH JOIN OUTER | | 2 | 26
| 4 | NESTED LOOPS OUTER | | 2 | 16
| 5 | NESTED LOOPS | | 2 | 15
| 6 | NESTED LOOPS | | 2 | 14
|* 7 | INDEX RANGE SCAN | IND_EFORM_DYNA_FIELD | 2 | 4
|* 8 | TABLE ACCESS BY INDEX ROWID | HCLT_PARKING_LOT | 1 | 5
|* 9 | INDEX RANGE SCAN | II1 | 1 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0037381 | 1 |
|* 11 | INDEX UNIQUE SCAN | SYS_C0037381 | 1 |
| 12 | VIEW | ITEMTRACEBILITY_VIEW | 2 | 10
| 13 | SORT UNIQUE | | 2 | 4
| 14 | UNION-ALL | | |
| 15 | HASH GROUP BY | | 1 | 2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 16 | INDEX RANGE SCAN | SDT11 | 3 | 6
| 17 | HASH GROUP BY | | 1 | 2
|* 18 | TABLE ACCESS BY INDEX ROWID| ITEMTRACEABILITY | 4 | 8
|* 19 | INDEX RANGE SCAN | ITEMTREACE_IDX2 | 164 |
| 20 | VIEW PUSHED PREDICATE | | 1 | 2
|* 21 | FILTER | | |
| 22 | NESTED LOOPS OUTER | | 1 | 3
| 23 | NESTED LOOPS OUTER | | 1 | 2
| 24 | TABLE ACCESS BY INDEX ROWID | WORKFLOWITEMDATA | 1 | 1
|* 25 | INDEX RANGE SCAN | WFITEMDATA_IDX2 | 1 |
|* 26 | INDEX UNIQUE SCAN | SYS_C0037381 | 1 |
|* 27 | INDEX UNIQUE SCAN | SYS_C0037320 | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EDFI"."ITEMID"="ITV"."FROMITEMID"(+) AND
"EDFI"."ITEMTYPE"="ITV"."FROMCHILDITEMTYPE"(+) AND
"EDFI"."OWNERID"="ITV"."FROMOWNERID"(+) AND "EDFI"."OWNERTYPE"="IT
7 - access("EDFI"."OWNERTYPE"='Prj' AND "EDFI"."OWNERID"=76129 AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EDFI"."ITEMTYPE"='TCK_f')
8 - filter((TO_NUMBER("IST"."CM_ACCESSCONTROL")<=9 OR "IST"."CM_CREATEDBY"=1)
("IST"."CM_OVERALLSTATUS"='Open' OR "IST"."CM_OVERALLSTATUS"='Past
"IST"."OWNERTYPE"='Prj' AND "IST"."OWNERID"=76129)
9 - access("IST"."ITEMTYPE"='TCK_f' AND "IST"."ITEMID"="EDFI"."ITEMID")
10 - access("EU1"."USERID"="IST"."CM_CREATEDBY")
11 - access("EU2"."USERID"(+)="IST"."CM_CHECKINCHECKOUTBY")
16 - access("FROMOWNERTYPE"='Prj' AND "FROMOWNERID"=76129 AND
"FROMCHILDITEMTYPE"='TCK_f')
18 - filter("TOCHILDITEMTYPE"='TCK_f')
19 - access("TOOWNERTYPE"='Prj' AND "TOOWNERID"=76129)
21 - filter('TCK_f'="IST"."ITEMTYPE")
25 - access("WFID"."ITEMTYPE"='TCK_f' AND "WFID"."ITEMID"="IST"."ITEMID")
26 - access("WFID"."CURRENTPARTY"="EU"."USERID"(+))
27 - access("WFID"."CURRENTSTAGEID"="WFS"."STAGEID"(+))
Records in tables
select count(*) from HCLT_PARKING_LOT--73106
select count(*) from eformdynamicfieldinstance--5867567
select count(*) from EnterpriseUser --83922
select count(*) from ITEMTRACEBILITY_VIEW--872810
Index in tables HCLT_PARKING_LOT
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
HCLT_IDX_PARKING_ITEMTYPE ITEMTYPE 1
HCLT_IDX_PARKING_OWNER OWNERTYPE 1
HCLT_IDX_PARKING_OWNER OWNERID 2
HCLT_IDX_PARKING_OWNER ENTERPRISEID 3
HCLT_IDX_PARKING_OWNER_ITEM OWNERTYPE 1
HCLT_IDX_PARKING_OWNER_ITEM OWNERID 2
HCLT_IDX_PARKING_OWNER_ITEM ITEMTYPE 3
HCLT_IDX_PARKING_OWNER_ITEM CM_OVERALLSTATUS 4
HCLT_IDX_PARKING_OWNER_ITEM ENTERPRISEID 5
HCLT_PARKINGLOT ITEMID 1
IDC ITEMTYPE 1
IDC ITEMID 2
IDC CM_CHECKINCHECKOUTBY 3
IDC1 CM_CREATEDBY 1
II1 ITEMTYPE 1
II1 ITEMID 2
II11 ITEMTYPE 1
II11 ITEMID 2
II11 CM_CREATEDBY 3
SDT1 CM_OVERALLSTATUS 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
SYS_C0037390 OWNERTYPE 1
SYS_C0037390 OWNERID 2
SYS_C0037390 ITEMTYPE 3
SYS_C0037390 ITEMID 4
SYS_C0037390 ENTERPRISEID 5
Index for eformdynamicfieldinstance
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
EFORMDYNAMICFIE_ENTERP_FK_IDX1 ENTERPRISEID 1
EFORM_COMPO_INDEX COMPOKEY 1
IDX_EDFI_CM_REL CM_RELEASE 1
IDX_EDFI_ITEMTYPE ITEMTYPE 1
IDX_EDFI_MODIFIEDDATE MODIFIEDDATE 1
IND_EFORM_DYNA_FIELD OWNERTYPE 1
IND_EFORM_DYNA_FIELD OWNERID 2
IND_EFORM_DYNA_FIELD ITEMTYPE 3
IND_EFORM_DYNA_FIELD ITEMID 4
IND_EFORM_DYNFL_ITEM ITEMTYPE 1
IND_EFORM_DYNFL_ITEM ITEMID 2
SYS_C0037374 OWNERTYPE 1
[code][/code]SYS_C0037374 OWNERID 2
SYS_C0037374 ITEMTYPE 3
SYS_C0037374 ITEMID 4
SYS_C0037374 ENTERPRISEID 5
T1X OWNERTYPE 1
T1X OWNERID 2
T1X ITEMTYPE 3
T1X CM_PRIORITY 4
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
T1X CM_OVERALLSTATUS 5
Index for enterpriseuser
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
ENTERPRISEUSER_ORGANI_FK_IDX ORGANIZATIONID 1
ENTUSER_IDX3 ENTERPRISEID 1
ENTUSER_IDX3 USERID 2
IDX_ENT_USER_SYNCSTATUS SYNCSTATUS 1
SYS_C0037381 USERID 1
XAK1ENTERPRISEUSER LOGINID 1
View defnition
CREATE OR REPLACE VIEW ITEMTRACEBILITY_VIEW AS
(SELECT FROMOWNERTYPE,
FROMOWNERID,
fromchilditemtype,
FROMITEMID
FROM itemtraceability
GROUP BY FROMOWNERTYPE,
FROMOWNERID,
fromchilditemtype,
FROMITEMID
UNION
SELECT TOOWNERTYPE,
TOOWNERID,
TOchilditemTYPE,
TOITEMID
FROM itemtraceability
GROUP BY TOOWNERTYPE,
TOOWNERID,
TOchilditemTYPE,
TOITEMID
)
INDEX ON ITEMTRACEBILITY
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
ITEMTRACEABILIT_ENTERP_FK_IDX1 ENTERPRISEID 1
ITEMTREACE_IDX2 TOOWNERTYPE 1
ITEMTREACE_IDX2 TOOWNERID 2
SDT11 FROMOWNERTYPE 1
SDT11 FROMOWNERID 2
SDT11 FROMCHILDITEMTYPE 3
SDT11 FROMITEMID 4
SYS_C0036920 FROMOWNERTYPE 1
SYS_C0036920 FROMOWNERID 2
SYS_C0036920 FROMITEMTYPE 3
SYS_C0036920 FROMITEMID 4
SYS_C0036920 TOOWNERTYPE 5
SYS_C0036920 TOOWNERID 6
SYS_C0036920 TOITEMTYPE 7
SYS_C0036920 TOITEMID 8
|
|
|
|
|
|
|
|
| Re: Query slow [message #472401 is a reply to message #472333] |
Fri, 20 August 2010 07:15  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
prashant_ora wrote on Fri, 20 August 2010 06:19Hi,
One of my query is taking 17 seconds to execute, can we reduce the timing..
Is that a timing taken after multiple prior runs or a first run timing?
|
|
|
|
|
|
| Re: Query slow [message #472405 is a reply to message #472400] |
Fri, 20 August 2010 07:26 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Couple of points:
1) Count(1) gets rewritten to Count(*) by oracle so you should use count(*)
2) Do you really need all those outer-joins - it's a fairly unusual requirement to need to a count over that many outer-joins.
3) If the original query uses binds (which this probably does) it helps to show them - and use them to generate the explain plan. Oracle can give a different plan for:
AND (WFID.CURRENTSTAGEID = -1 OR -1 = -1)
AND (WFID.CURRENTSTAGEID = :bind OR :bind = -1)
4) If you really do need all those joins then you would be better using the WITH clause, it would make it more readable if nothing else. I'm struggling to work out what joins to what.
5) Why the group by's in the view? If you're not selecting an aggregate you should probably use distinct instead.
|
|
|
|
| Re: Query slow [message #472406 is a reply to message #472403] |
Fri, 20 August 2010 07:27 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
prashant_ora wrote on Fri, 20 August 2010 13:20In First run it took time and then 2 ,3 seconds
That's caching at work. Unless this a query that's going to be run once a month always run it multiple times and get the average time of the runs excluding the first.
Is the new time fast enough?
|
|
|
|
|
|
| Re: Query slow [message #472408 is a reply to message #472407] |
Fri, 20 August 2010 07:33 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Is it really worth worrying about?
First time you run the query oracle parses the query and loads the data into the buffer cache from disk, this takes time.
Subsequent executions don't need the expensive parse and read the data from the buffer cache - which is why it's quicker.
How often is it actually running slow on production?
|
|
|
|
| Re: Query slow [message #472409 is a reply to message #472405] |
Fri, 20 August 2010 07:35 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Yes, it use the bind variable, but when i get the explain plan i changed with hard coded value for which actually the query executed.
Yes i require all the joins,How can i convert it into the with clause?
|
|
|
|
| Re: Query slow [message #472411 is a reply to message #472409] |
Fri, 20 August 2010 07:43 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
prashant_ora wrote on Fri, 20 August 2010 13:35Yes, it use the bind variable, but when i get the explain plan i changed with hard coded value for which actually the query executed.
Well don't do that, like I say you can get a different plan that way. Explain will take binds just fine.
prashant_ora wrote on Fri, 20 August 2010 13:35
Yes i require all the joins,How can i convert it into the with clause?
Are you absolutely sure you need all the joins?
What is the point of counting optional rows?
As for converting into with, pass. I can't follow what it's doing. Read the documentation, it has examples, and work out what you need. You will want to use more than one WITH clause in the query.
|
|
|
|
| Re: Query slow [message #472415 is a reply to message #472398] |
Fri, 20 August 2010 08:02 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This is the trace output
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.21 0.20 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.82 2.96 524 314678 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.03 3.17 524 314678 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=314678 pr=524 pw=0 time=2966055 us)
118 HASH JOIN OUTER (cr=314678 pr=524 pw=0 time=3621210 us)
118 NESTED LOOPS OUTER (cr=246274 pr=328 pw=0 time=16024917 us)
118 NESTED LOOPS OUTER (cr=245665 pr=328 pw=0 time=16013724 us)
118 NESTED LOOPS (cr=245545 pr=328 pw=0 time=16012309 us)
118 NESTED LOOPS (cr=245425 pr=328 pw=0 time=16008887 us)
74325 INDEX RANGE SCAN IND_EFORM_DYNA_FIELD (cr=332 pr=328 pw=0 time=74379 us)(object id 224945)
118 TABLE ACCESS BY INDEX ROWID HCLT_PARKING_LOT (cr=245093 pr=0 pw=0 time=1946003 us)
74319 INDEX RANGE SCAN HCLT_PARKINGLOT (cr=74500 pr=0 pw=0 time=491617 us)(object id 220632)
118 INDEX UNIQUE SCAN SYS_C00466164 (cr=120 pr=0 pw=0 time=606 us)(object id 223730)
118 INDEX UNIQUE SCAN SYS_C00466164 (cr=120 pr=0 pw=0 time=506 us)(object id 223730)
118 VIEW PUSHED PREDICATE (cr=609 pr=0 pw=0 time=4085 us)
118 FILTER (cr=609 pr=0 pw=0 time=3876 us)
118 NESTED LOOPS OUTER (cr=609 pr=0 pw=0 time=3634 us)
118 NESTED LOOPS OUTER (cr=371 pr=0 pw=0 time=2647 us)
118 TABLE ACCESS BY INDEX ROWID WORKFLOWITEMDATA (cr=358 pr=0 pw=0 time=2121 us)
118 INDEX RANGE SCAN WFITEMDATA_IDX2 (cr=239 pr=0 pw=0 time=1361 us)(object id 220500)
9 INDEX UNIQUE SCAN SYS_C00466164 (cr=13 pr=0 pw=0 time=245 us)(object id 223730)
116 INDEX UNIQUE SCAN SYS_C00466103 (cr=238 pr=0 pw=0 time=691 us)(object id 223728)
53056 VIEW ITEMTRACEBILITY_VIEW (cr=68404 pr=196 pw=0 time=852337 us)
53056 SORT UNIQUE (cr=68404 pr=196 pw=0 time=799278 us)
53058 UNION-ALL (cr=68404 pr=196 pw=0 time=484207 us)
53056 HASH GROUP BY (cr=62899 pr=0 pw=0 time=378085 us)
55669 TABLE ACCESS BY INDEX ROWID ITEMTRACEABILITY (cr=62899 pr=0 pw=0 time=283524 us)
68815 INDEX RANGE SCAN SYS_C00465695 (cr=504 pr=0 pw=0 time=302 us)(object id 223584)
2 HASH GROUP BY (cr=5505 pr=196 pw=0 time=240309 us)
3 TABLE ACCESS BY INDEX ROWID ITEMTRACEABILITY (cr=5505 pr=196 pw=0 time=138931 us)
68830 INDEX RANGE SCAN ITEMTREACE_IDX2 (cr=196 pr=196 pw=0 time=22947 us)(object id 219616)
********************************************************************************
|
|
|
|
|
|
|
|
| Re: Query slow [message #472436 is a reply to message #472434] |
Fri, 20 August 2010 09:37 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
Your complaint/issue seems to be around the first run time, in which case faster discs might help, but as cookiemonster said if its a frequently run query its not worth worrying about/upgrading hardware for a slow 'first' run.
|
|
|
|
| Re: Query slow [message #472474 is a reply to message #472436] |
Fri, 20 August 2010 17:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Looking at the line
74319 INDEX RANGE SCAN HCLT_PARKINGLOT (cr=74500 pr=0 pw=0 time=491617 us)(object id 220632)
You are scanning 74319 rows from the index. But looking at the line:
118 TABLE ACCESS BY INDEX ROWID HCLT_PARKING_LOT (cr=245093 pr=0 pw=0 time=1946003 us)
we see that only 118 of those 74319 rows are actually matched in HCLT_PARKING_LOT.
Explain Plan tell you that the predicates used to filter 74319 rows down to 118 is:
8 - filter((TO_NUMBER("IST"."CM_ACCESSCONTROL")<=9 OR "IST"."CM_CREATEDBY"=1)
("IST"."CM_OVERALLSTATUS"='Open' OR "IST"."CM_OVERALLSTATUS"='Past
"IST"."OWNERTYPE"='Prj' AND "IST"."OWNERID"=76129)
Now one or more of these conditions is responsible for MOST of the reduction in rows. If you add the columns from the most selective clause(s) to the II1 index, then you will avoid a heap of table lookups.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
| Re: Query slow [message #472547 is a reply to message #472479] |
Sat, 21 August 2010 20:47 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
prashant_ora wrote on Sat, 21 August 2010 14:20so which column should i add in ii1 index, please advice.
Which one is responsible for 74K rows being filtered out of the query?
Ross Leishman
|
|
|
|
| Re: Query slow [message #472551 is a reply to message #472547] |
Sun, 22 August 2010 00:33 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I think all the filter which are part of where clause
like ownertype,ownerid,itemtype,cm_createdby,cm_accesscontrol,cm_overallstatus
|
|
|
|
| Re: Query slow [message #472556 is a reply to message #472551] |
Sun, 22 August 2010 02:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Are you saying each one of the 6 columns is responsible for an equal filtering of the 74K rows? ie. each predicate filters about 12,000 rows?
That would be an enormous coincidence.
Tell you what: why don't you humour us and MEASURE how many of the rows in the table match each of the predicates:
- TO_NUMBER(CM_ACCESSCONTROL)<=9 OR CM_CREATEDBY=1)
- CM_OVERALLSTATUS IN ('Open', 'PastDue')
- OWNERTYPE = 'Prj'
- OWNERID = 76129
Ross Leishman
|
|
|
|
| Re: Query slow [message #472560 is a reply to message #472556] |
Sun, 22 August 2010 03:13 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
This CM_OVERALLSTATUS IN ('Open', 'PastDue') condition if i put only select count(*) from hclt_parking_lot where CM_OVERALLSTATUS IN ('Open', 'PastDue') returns 111 records out of all.While i have created one index(sdt1) on this column alreday.
|
|
|
|
|
|
| Re: Query slow [message #472820 is a reply to message #472333] |
Tue, 24 August 2010 01:51 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try the following index?
HCLT_PARKING_LOT(ItemType, ItemId, OwnerType, OwnerID, CM_OVERALLSTATUS)
Ross Leishman
|
|
|
|
| Re: Query slow [message #473659 is a reply to message #472333] |
Tue, 31 August 2010 01:28 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
1. Try Ross's suggestion (create an index).
2. IMHO you can remove
left outer join ITEMTRACEBILITY_VIEW itv
ON EDFI.OwnerType = itv.FromOwnerType
and EDFI.OwnerID = itv.FromOwnerID
and EDFI.ItemType = itv.FromChildItemType
and EDFI.ITemID = itv.FromItemID,
You dont select any data from it and it does NOT changes the
resultset size.
HTH.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
