perforamance issue
- From: edwin devadanam <edwin_kodamala@xxxxxxxxx>
- To: Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>, edwin.kodamala@xxxxxxxxx
- Date: Sun, 1 Oct 2006 18:45:48 -0700 (PDT)
Hi gurus,
we are having oracle applications instance(11.5.10.) running with 9.2.0.5
database version.
After applying oracle applications patch(not database patch),the whole system
turned upside down.
All the quieries are taking 20 times more time than before patching.
we have done some workarounds but invain.
please have a look at the active users sql run on the database before and after
applying patch.
active users before patching : time taken to complete 10sec (please see
attachement)
active users after patch : time taken to complete 180sec (please see
attachment)
Any help would be appreciated.
Thanks,
Edwin.K
---------------------------------
Want to be your own boss? Learn how on Yahoo! Small Business. SQL Statement from editor:
select user_name, application_name, responsibility_name, security_group_name,
greatest(to_date(u.start_date), to_date(ur.start_date), to_date(r.start_date))
start_date, decode( least(nvl(u.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(ur.end_date, to_date('01/01/4712','DD/MM/YYYY')), nvl(r.end_date,
to_date('01/01/4712','DD/MM/YYYY'))), to_date('01/01/4712','DD/MM/YYYY'), '',
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)), nvl(ur.end_date,
nvl(u.end_date, r.end_date)), nvl(r.end_date, nvl(u.end_date, ur.end_date))))
end_date
from fnd_user u,
apps.fnd_user_resp_groups_all ur,
apps.fnd_responsibility_vl r,
apps.fnd_application_vl a,
apps.fnd_security_groups_vl s
where a.application_id = r.application_id and
u.user_id = ur.user_id and
r.application_id = ur.responsibility_application_id and
r.responsibility_id = ur.responsibility_id and
ur.start_date <= sysdate and
nvl ( ur.end_date , sysdate + 1 ) > sysdate and
u.start_date <= sysdate and
nvl ( u.end_date , sysdate + 1 ) > sysdate and
r.start_date <= sysdate and
nvl ( r.end_date , sysdate + 1 ) > sysdate and
ur.security_group_id = s.security_group_id and
r.version in ( '4' , 'W' , 'M' )
ORDER BY 1 ASC,4 ASC , user_name , application_name , responsibility_name ,
security_group_name
------------------------------------------------------------
Statement Id=19 Type=PARTITION RANGE
Cost=0 TimeStamp=25-08-06::12::16:12
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 143
(34) SORT ORDER BY
Est. Rows: 1 Cost: 142
(33) FILTER
(30) NESTED LOOPS
Est. Rows: 1 Cost: 129
(27) NESTED LOOPS
Est. Rows: 2 Cost: 73
(24) NESTED LOOPS
Est. Rows: 2 Cost: 71
(21) NESTED LOOPS
Est. Rows: 2 Cost: 69
(19) NESTED LOOPS
Est. Rows: 2 Cost: 69
(14) HASH JOIN
Est. Rows: 5 Cost: 44
(11) NESTED LOOPS
Est. Rows: 20 Cost: 27
(8) MERGE JOIN CARTESIAN
Est. Rows: 20 Cost: 7
(5) NESTED LOOPS
Est. Rows: 1 Cost: 2
(3) TABLE ACCESS BY INDEX
ROWID APPLSYS.FND_SECURITY_GROUPS_TL [Analyzed]
(3) Blocks: 1 Est. Rows: 1
of 1 Cost: 2
Tablespace: APPLSYSD
(2) UNIQUE INDEX SKIP
SCAN APPLSYS.FND_SECURITY_GROUPS_TL_U1 [Analyzed]
Est. Rows: 1 Cost:
1
(4) UNIQUE INDEX UNIQUE
SCAN APPLSYS.FND_SECURITY_GROUPS_U1 [Analyzed]
Est. Rows: 1
(7) BUFFER SORT
Est. Rows: 20 Cost: 7
(6) TABLE ACCESS FULL
APPLSYS.FND_USER [Analyzed]
(6) Blocks: 24 Est. Rows:
20 of 400 Cost: 5
Tablespace: APPLSYSD
(10) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_USER [Analyzed]
(10) Blocks: 24 Est. Rows: 1 of
400 Cost: 1
Tablespace: APPLSYSD
(9) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_USER_U1 [Analyzed]
Est. Rows: 1
(13) PARTITION RANGE ALL
(12) TABLE ACCESS FULL
APPLSYS.WF_USER_ROLE_ASSIGNMENTS [Analyzed]
Blocks: 97 Est. Rows: 91 of
1,998 Cost: 16
(18) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_RESPONSIBILITY [Analyzed]
(18) Blocks: 21 Est. Rows: 1 of 1,461
Cost: 5
Tablespace: APPLSYSD
(17) UNIQUE INDEX RANGE SCAN
APPLSYS.FND_RESPONSIBILITY_U1 [Analyzed]
Est. Rows: 9 Cost: 1
(16) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_APPLICATION [Analyzed]
(16) Blocks: 4 Est. Rows: 1 of 257
Cost: 1
Tablespace: APPLSYSD
(15) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_APPLICATION_U3 [Analyzed]
Est. Rows: 1
(20) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_APPLICATION_U1 [Analyzed]
Est. Rows: 1
(23) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_APPLICATION_TL [Analyzed]
(23) Blocks: 5 Est. Rows: 1 of 257 Cost: 1
Tablespace: APPLSYSD
(22) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_APPLICATION_TL_U1 [Analyzed]
Est. Rows: 1
(26) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_RESPONSIBILITY_TL [Analyzed]
(26) Blocks: 23 Est. Rows: 1 of 1,461 Cost: 1
Tablespace: APPLSYSD
(25) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_RESPONSIBILITY_TL_U1 [Analyzed]
Est. Rows: 1
(29) PARTITION RANGE ALL
(28) UNIQUE INDEX RANGE SCAN APPLSYS.WF_LOCAL_ROLES_U1
[Analyzed]
Est. Rows: 1 Cost: 28
(32) TABLE ACCESS BY INDEX ROWID APPLSYS.FND_SECURITY_GROUPS
[Analyzed]
(32) Blocks: 1 Est. Rows: 1 of 1 Cost: 1
Tablespace: APPLSYSD
(31) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_SECURITY_GROUPS_U2 [Analyzed]
Est. Rows: 1
SQL Statement from editor:
select user_name, application_name, responsibility_name, security_group_name,
greatest(to_date(u.start_date), to_date(ur.start_date), to_date(r.start_date))
start_date, decode( least(nvl(u.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(ur.end_date, to_date('01/01/4712','DD/MM/YYYY')), nvl(r.end_date,
to_date('01/01/4712','DD/MM/YYYY'))), to_date('01/01/4712','DD/MM/YYYY'), '',
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)), nvl(ur.end_date,
nvl(u.end_date, r.end_date)), nvl(r.end_date, nvl(u.end_date, ur.end_date))))
end_date
from fnd_user u,
apps.fnd_user_resp_groups_all ur,
apps.fnd_responsibility_vl r,
apps.fnd_application_vl a,
apps.fnd_security_groups_vl s
where a.application_id = r.application_id and
u.user_id = ur.user_id and
r.application_id = ur.responsibility_application_id and
r.responsibility_id = ur.responsibility_id and
ur.start_date <= sysdate and
nvl ( ur.end_date , sysdate + 1 ) > sysdate and
u.start_date <= sysdate and
nvl ( u.end_date , sysdate + 1 ) > sysdate and
r.start_date <= sysdate and
nvl ( r.end_date , sysdate + 1 ) > sysdate and
ur.security_group_id = s.security_group_id and
r.version in ( '4' , 'W' , 'M' )
ORDER BY 1 ASC,4 ASC , user_name , application_name , responsibility_name ,
security_group_name
------------------------------------------------------------
Statement Id=19 Type=INDEX
Cost=0 TimeStamp=07-09-06::16::49:14
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 376
(34) SORT ORDER BY
Est. Rows: 1 Cost: 374
(33) FILTER
(28) NESTED LOOPS
Est. Rows: 1 Cost: 361
(25) NESTED LOOPS
Est. Rows: 2 Cost: 305
(22) NESTED LOOPS
Est. Rows: 2 Cost: 303
(19) HASH JOIN
Est. Rows: 2 Cost: 301
(2) TABLE ACCESS FULL
APPLSYS.FND_RESPONSIBILITY [Analyzed]
(2) Blocks: 21 Est. Rows: 67 of 1,473 Cost: 5
Tablespace: APPLSYSD
(18) MERGE JOIN CARTESIAN
Est. Rows: 1,413 Cost: 295
(15) HASH JOIN
Est. Rows: 5 Cost: 290
(12) NESTED LOOPS
Est. Rows: 20 Cost: 28
(9) MERGE JOIN CARTESIAN
Est. Rows: 20 Cost: 8
(6) NESTED LOOPS
Est. Rows: 1 Cost: 2
(4) TABLE ACCESS BY INDEX
ROWID APPLSYS.FND_SECURITY_GROUPS_TL [Analyzed]
(4) Blocks: 1 Est. Rows: 1
of 1 Cost: 2
Tablespace: APPLSYSD
(3) UNIQUE INDEX SKIP
SCAN APPLSYS.FND_SECURITY_GROUPS_TL_U1 [Analyzed]
Est. Rows: 1 Cost:
1
(5) UNIQUE INDEX UNIQUE
SCAN APPLSYS.FND_SECURITY_GROUPS_U1 [Analyzed]
Est. Rows: 1
(8) BUFFER SORT
Est. Rows: 20 Cost: 8
(7) TABLE ACCESS FULL
APPLSYS.FND_USER [Analyzed]
(7) Blocks: 29 Est. Rows:
20 of 402 Cost: 6
Tablespace: APPLSYSD
(11) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_USER [Analyzed]
(11) Blocks: 29 Est. Rows: 1 of
402 Cost: 1
Tablespace: APPLSYSD
(10) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_USER_U1 [Analyzed]
Est. Rows: 1
(14) PARTITION RANGE ALL
(13) TABLE ACCESS FULL
APPLSYS.WF_USER_ROLE_ASSIGNMENTS [Analyzed]
Blocks: 1,712 Est. Rows: 110 of
81,380 Cost: 261
(17) BUFFER SORT
Est. Rows: 257 Cost: 34
(16) UNIQUE INDEX FULL SCAN
APPLSYS.FND_APPLICATION_U1 [Analyzed]
Est. Rows: 257 Cost: 1
(21) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_APPLICATION_TL [Analyzed]
(21) Blocks: 5 Est. Rows: 1 of 257 Cost: 1
Tablespace: APPLSYSD
(20) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_APPLICATION_TL_U1 [Analyzed]
Est. Rows: 1
(24) TABLE ACCESS BY INDEX ROWID
APPLSYS.FND_RESPONSIBILITY_TL [Analyzed]
(24) Blocks: 23 Est. Rows: 1 of 1,473 Cost: 1
Tablespace: APPLSYSD
(23) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_RESPONSIBILITY_TL_U1 [Analyzed]
Est. Rows: 1
(27) PARTITION RANGE ALL
(26) UNIQUE INDEX RANGE SCAN APPLSYS.WF_LOCAL_ROLES_U1
[Analyzed]
Est. Rows: 1 Cost: 28
(30) TABLE ACCESS BY INDEX ROWID APPLSYS.FND_APPLICATION
[Analyzed]
(30) Blocks: 4 Est. Rows: 1 of 257 Cost: 1
Tablespace: APPLSYSD
(29) UNIQUE INDEX UNIQUE SCAN APPLSYS.FND_APPLICATION_U3
[Analyzed]
Est. Rows: 1
(32) TABLE ACCESS BY INDEX ROWID APPLSYS.FND_SECURITY_GROUPS
[Analyzed]
(32) Blocks: 1 Est. Rows: 1 of 1 Cost: 1
Tablespace: APPLSYSD
(31) UNIQUE INDEX UNIQUE SCAN
APPLSYS.FND_SECURITY_GROUPS_U2 [Analyzed]
Est. Rows: 1
Other related posts:
