If you see
performance issue during responsibility changing after launching form then
perform following .
1. Check the explain plan which query is picking
using (connect as apps ) :
Set pages 200
Set lines 200
Explain plan for
select distinct furg.responsibility_id,
furg.responsibility_application_id, furg.security_group_id, furg.start_date,
furg.end_date from fnd_user_resp_groups furg, fnd_responsibility fr where
furg.user_id = :1 and furg.responsibility_id = fr.responsibility_id and furg.responsibility_application_id
= fr.application_id and furg.start_date <= sysdate and (furg.end_date is
null or furg.end_date >= sysdate) and fr.start_date <= sysdate and (fr.end_date is null or fr.end_date >=
sysdate) order by furg.responsibility_id, furg.responsibility_application_id,
furg.security_group_id;
@?/rdbms/admin/utlxpls
/
2. For the bad performance it will be using
WF_LOCAL_USER_ROLES_N7 . for the query to perform good it should pick
WF_LOCAL_USER_ROLES_U1
3. We can either fix it by rebuilding the index or by
just doing gather stats on index WF_LOCAL_USER_ROLES_N7
Option a : For rebuilding index use : connect as apps and run create_WF_LOCAL_USER_ROLES_N7.sql
Option a : For rebuilding index use : connect as apps and run create_WF_LOCAL_USER_ROLES_N7.sql
drop index applsys.WF_LOCAL_USER_ROLES_N7;
CREATE INDEX APPLSYS.WF_LOCAL_USER_ROLES_N7 ON
APPLSYS.WF_LOCAL_USER_ROLES
(ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM)
INITRANS 11
MAXTRANS 255
LOGGING
LOCAL (
PARTITION FND_USR
LOGGING
NOCOMPRESS
TABLESPACE
APPLSYSX
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 40K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION FND_RESP
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION POS
LOGGING
NOCOMPRESS
TABLESPACE
APPLSYSD
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 40K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION AMV_APPR
LOGGING
NOCOMPRESS
TABLESPACE
APPLSYSX
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION ENG_LIST
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION HZ_PARTY
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION GBX
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION PQH_ROLE
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION
WF_LOCAL_ROLES
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION PER_ROLE
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION AMV_CHN
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION HZ_GROUP
LOGGING
NOCOMPRESS
TABLESPACE
APPLSYSX
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION HTB_SEC
LOGGING
NOCOMPRESS
TABLESPACE
APPS_TS_TX_DATA
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION UMX
LOGGING
NOCOMPRESS
TABLESPACE
APPLSYSX
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
4. >takes 10 mins
Option b: for gather stats for index : Connect as apps and run EXEC DBMS_STATS.gather_index_stats('APPLSYS', 'WF_LOCAL_USER_ROLES_N7', estimate_percent => 15); >less than 1 min
Option b: for gather stats for index : Connect as apps and run EXEC DBMS_STATS.gather_index_stats('APPLSYS', 'WF_LOCAL_USER_ROLES_N7', estimate_percent => 15); >less than 1 min
5. Please run step1 to verify the plan
