Home » RDBMS Server » Performance Tuning » Performance worse for given workflow query (11i)
Performance worse for given workflow query [message #652918] Wed, 22 June 2016 05:59 Go to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Hi,

Below is the query which is performing badly in production and consuming high LIO also.

SELECT s.item_type, 
       s.item_key, 
       p.process_name, 
       p.activity_name, 
       p.process_version, 
       p.activity_item_type 
FROM   wf_item_activity_statuses S, 
       wf_process_activities P 
WHERE  p.instance_id = s.process_activity 
AND    s.item_type = :B3 
AND    p.activity_item_type = s.item_type 
AND    p.process_name = Nvl (:B2 , p.process_name) 
AND    p.activity_name = 'LINE_SCHEDULING' 
AND    s.item_key = Nvl (:B1 , s.item_key) 
AND    s.activity_status = 'COMPLETE' 
AND    EXISTS 
       ( 
              SELECT 1 
              FROM   wf_item_activity_statuses_h S1, 
                     wf_process_activities P1 
              WHERE  p1.instance_id = s1.process_activity 
              AND    s1.item_type = :B3 
              AND    s1.item_type = s.item_type 
              AND    s1.item_key = s.item_key 
              AND    p1.activity_item_type = s1.item_type 
              AND    p1.process_name = Nvl (:B2 , p1.process_name) 
              AND    s1.begin_date <= (sysdate - :B6 ) 
              AND    p1.activity_name = :B5 
              AND    p1.process_version = p.process_version 
              AND    s1.item_key = Nvl (:B1 , s1.item_key) 
              AND    s1.activity_status = :B4 )

I tried scanning wf_item_activity_statuses_h first but it didn't work.
i am attaching trace file also.

Report message to a moderator

Re: Performance worse for given workflow query [message #652919 is a reply to message #652918] Wed, 22 June 2016 06:07 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
What is the execution plan?

Have tried re-writing it with a join to wf_item_activity_statuses_h rather than using the EXISTS subquery?

Report message to a moderator

Re: Performance worse for given workflow query [message #652920 is a reply to message #652919] Wed, 22 June 2016 06:15 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Hi John,

You mean to say we need to remove exist condition and put that wf_item_activity_statuses_h S1, wf_process_activities P1 in upper select parts.
I have attached trace file in the forum

Report message to a moderator

Re: Performance worse for given workflow query [message #652922 is a reply to message #652920] Wed, 22 June 2016 06:28 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
[code]

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 1 7 0 0
Fetch 425 214.72 383.94 17411 50875857 0 42442
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 427 214.73 383.96 17412 50875864 0 42442

[\code]

Report message to a moderator

Re: Performance worse for given workflow query [message #652925 is a reply to message #652922] Wed, 22 June 2016 06:50 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
If you do not post the execution plan, I do not see how anyone can help. Have you tried my suggestion for a query re-write yet?

Report message to a moderator

Re: Performance worse for given workflow query [message #652932 is a reply to message #652925] Wed, 22 June 2016 08:39 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          1          7          0           0
Fetch      425    214.72     383.94      17411   50875857          0       42442
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      427    214.73     383.96      17412   50875864          0       42442

Report message to a moderator

Re: Performance worse for given workflow query [message #652933 is a reply to message #652932] Wed, 22 June 2016 08:40 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Do you know what an execution plan is?

Report message to a moderator

Re: Performance worse for given workflow query [message #652934 is a reply to message #652932] Wed, 22 June 2016 08:41 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     42442      42442      42442  NESTED LOOPS SEMI (cr=50875857 pr=17411 pw=0 time=222569710 us cost=14335 size=126635 card=1333)
  13603735   13603735   13603735   NESTED LOOPS  (cr=3899147 pr=15177 pw=0 time=234637774 us cost=6332 size=118637 card=1333)
       330        330        330    TABLE ACCESS BY INDEX ROWID WF_PROCESS_ACTIVITIES (cr=310 pr=0 pw=0 time=1903 us cost=9 size=627 card=11)
       330        330        330     INDEX RANGE SCAN WF_PROCESS_ACTIVITIES_N1 (cr=20 pr=0 pw=0 time=243 us cost=3 size=0 card=11)(object id 42753934)
  13603735   13603735   13603735    TABLE ACCESS BY INDEX ROWID WF_ITEM_ACTIVITY_STATUSES (cr=3898837 pr=15177 pw=0 time=232368126 us cost=4071 size=3744 card=117)
  13603735   13603735   13603735     INDEX RANGE SCAN WF_ITEM_ACTIVITY_STATUSES_N1 (cr=265086 pr=5184 pw=0 time=61199508 us cost=225 size=0 card=12567)(object id 42909699)
     42442      42442      42442   VIEW PUSHED PREDICATE  VW_SQ_1 (cr=46976710 pr=2234 pw=0 time=138341584 us cost=6 size=6 card=1)
     42442      42442      42442    FILTER  (cr=46976710 pr=2234 pw=0 time=134407171 us)
     42442      42442      42442     NESTED LOOPS  (cr=46976710 pr=2234 pw=0 time=130404506 us cost=6 size=96 card=1)
   1159338    1159338    1159338      TABLE ACCESS BY INDEX ROWID WF_ITEM_ACTIVITY_STATUSES_H (cr=45549227 pr=2234 pw=0 time=114676801 us cost=5 size=39 card=1)
   1159338    1159338    1159338       INDEX RANGE SCAN WF_ITEM_ACT_STAT_H_X99 (cr=44596108 pr=2232 pw=0 time=93532521 us cost=4 size=0 card=1)(object id 42787416)
     42442      42442      42442      TABLE ACCESS BY INDEX ROWID WF_PROCESS_ACTIVITIES (cr=1427483 pr=0 pw=0 time=2166356 us cost=1 size=57 card=1)
   1154920    1154920    1154920       INDEX UNIQUE SCAN WF_PROCESS_ACTIVITIES_PK (cr=272563 pr=0 pw=0 time=1037760 us cost=0 size=0 card=1)(object id 42758862)

Report message to a moderator

Re: Performance worse for given workflow query [message #652935 is a reply to message #652934] Wed, 22 June 2016 08:42 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Hi John,

You mean to say we need to remove exist condition and put that wf_item_activity_statuses_h S1, wf_process_activities P1 in upper select parts.

I have paste execution plan and the query time,LIO

Report message to a moderator

Re: Performance worse for given workflow query [message #652936 is a reply to message #652935] Wed, 22 June 2016 08:48 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Quote:
You mean to say we need to remove exist condition and put that wf_item_activity_statuses_h S1, wf_process_activities P1 in upper select parts.
I suppose you could put it that way.

Report message to a moderator

Re: Performance worse for given workflow query [message #652937 is a reply to message #652936] Wed, 22 June 2016 09:13 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
i am afraid it will impact the rows returned of the query also.

Report message to a moderator

Re: Performance worse for given workflow query [message #652939 is a reply to message #652937] Wed, 22 June 2016 09:19 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. You wrote the query. You should know what it is doing. How do you intend to tune the SQL if you don't understand what you want to achieve?

Report message to a moderator

Re: Performance worse for given workflow query [message #652944 is a reply to message #652939] Wed, 22 June 2016 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
John Watson wrote on Wed, 22 June 2016 07:19
Come on, man. You wrote the query. You should know what it is doing. How do you intend to tune the SQL if you don't understand what you want to achieve?


why do you assume that OP wrote the SQL?
why do you assume OP can even spell S-Q-L?

Report message to a moderator

Re: Performance worse for given workflow query [message #652946 is a reply to message #652944] Wed, 22 June 2016 09:38 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
I assume he pronounces it "sequel". I do Smile

Report message to a moderator

Re: Performance worse for given workflow query [message #652947 is a reply to message #652946] Wed, 22 June 2016 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
OP may well have not written the SQL but if he doesn't understand what it does and what the relationships between the underlying tables are, he shouldn't be trying to tune it.
And unless someone tells the rest of us what the table structures are and what the relationships between them are, the amount of help we can give will be limited.

Report message to a moderator

Re: Performance worse for given workflow query [message #652948 is a reply to message #652946] Wed, 22 June 2016 09:44 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
i was thinking that wf_item_activity_statuses_h is a history table of wf_item_activity_statuses and history table has more data so scanning
wf_item_activity_statuses_h would be beneficial.
Meanwhile i am re-writing the sql also.

Report message to a moderator

Re: Performance worse for given workflow query [message #652949 is a reply to message #652947] Wed, 22 June 2016 09:48 Go to previous message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
The table structures and relationships are all on etrm.oracle.com, for example,
Table: WF_ITEM_ACTIVITY_STATUSES

Product:	FND - Application Object Library
Description:	Runtime table for a work item
Implementation/DBA Data:	TableAPPLSYS.WF_ITEM_ACTIVITY_STATUSES
Primary Key: WF_ITEM_ACTIVITY_STATUSES_PK

ColumnITEM_TYPE
ColumnITEM_KEY
ColumnPROCESS_ACTIVITY
Foreign Keys

Table	
Foreign Table	Foreign Key Column
WF_ITEM_ACTIVITY_STATUSES	FK Joins to	TableWF_ITEMS	WF_ITEM_ACTIVITY_STATUSES.ITEM_TYPE 
WF_ITEM_ACTIVITY_STATUSES.ITEM_KEY
WF_ITEM_ACTIVITY_STATUSES	FK Joins to	TableWF_PROCESS_ACTIVITIES	WF_ITEM_ACTIVITY_STATUSES.PROCESS_ACTIVITY
WF_ITEM_ACTIVITY_STATUSES	FK Joins to	TableWF_NOTIFICATIONS	WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID

Report message to a moderator

Previous Topic: How to make sure no archivelog being generated for a particular table
Next Topic: Whast is connect$_by$_pump$ in explain plan
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 09 01:00:58 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.