| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> perforamance issue
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
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 01 2006 - 20:45:48 CDT
 |
 |