Hi All,
I have the following query, which is taking around 2 minutes, while the expected time is 15 secs. The Explain shows that it does a scan of 2 tables and an index which i feel are not related to my query.
The tables are WF_LOCAL_USER_ROLES,WF_USER_ROLE_ASSIGNMENTS and Index is WF_USER_ROLE_ASSIGNMENTS_N2.
Can anybody let me know is this an expected thing, if yes how? And also provide me with few tuning tips.
Query
SELECT DISTINCT 'GROUP', RESPONSIBILITY_NAME
FROM
FND_USER ,FND_USER_RESP_GROUPS, FND_RESPONSIBILITY_VL
WHERE
FND_USER.user_id=FND_USER_RESP_GROUPS.user_id
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY_VL.APPLICATION_ID
AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE
AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE
ELSE TO_DATE(FND_USER_RESP_GROUPS.end_Date) END) >= SYSDATE
AND FND_USER.USER_GUID = '78cccbec84c3afa0e040bc0a41086474'
Explain Plan
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 292 | 3762 (2)| | |
| 1 | HASH UNIQUE | | 1 | 292 | 3762 (2)| | |
|* 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS | | 1 | 292 | 3761 (2)| | |
| 4 | NESTED LOOPS | | 1 | 283 | 3761 (2)| | |
| 5 | NESTED LOOPS | | 1 | 275 | 3759 (2)| | |
| 6 | NESTED LOOPS | | 1 | 233 | 3758 (2)| | |
| 7 | NESTED LOOPS | | 1 | 224 | 3757 (2)| | |
| 8 | NESTED LOOPS | | 1 | 215 | 3742 (2)| | |
| 9 | NESTED LOOPS | | 1 | 189 | 3740 (2)| | |
| 10 | PARTITION RANGE SINGLE | | 23 | 2599 | 3073 (2)| 3 | 3 |
|* 11 | TABLE ACCESS FULL | WF_LOCAL_USER_ROLES | 23 | 2599 | 3073 (2)| 3 | 3 |
| 12 | PARTITION RANGE ALL | | 1 | 76 | 29 (0)| 1 | 14 |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| WF_USER_ROLE_ASSIGNMENTS | 1 | 76 | 29 (0)| 1 | 14 |
|* 14 | INDEX RANGE SCAN | WF_USER_ROLE_ASSIGNMENTS_N2 | 1 | | 28 (0)| 1 | 14 |
| 15 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 26 | 2 (0)| | |
|* 16 | INDEX UNIQUE SCAN | FND_USER_U2 | 1 | | 1 (0)| | |
|* 17 | INDEX FULL SCAN | FND_RESPONSIBILITY_U1 | 1 | 9 | 15 (0)| | |
|* 18 | TABLE ACCESS BY INDEX ROWID | FND_APPLICATION | 1 | 9 | 1 (0)| | |
|* 19 | INDEX UNIQUE SCAN | FND_APPLICATION_U1 | 1 | | 0 (0)| | |
| 20 | TABLE ACCESS BY INDEX ROWID | FND_RESPONSIBILITY_TL | 1 | 42 | 1 (0)| | |
|* 21 | INDEX UNIQUE SCAN | FND_RESPONSIBILITY_TL_U1 | 1 | | 0 (0)| | |
|* 22 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 8 | 2 (0)| | |
|* 23 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 1 (0)| | |
|* 24 | INDEX UNIQUE SCAN | FND_SECURITY_GROUPS_U2 | 1 | 9 | 0 (0)| | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(CASE WHEN NULL IS NULL THEN SYSDATE@! ELSE TO_DATE(TO_CHAR(NULL)) END >=SYSDATE@! AND
"FND_DATE"."CANONICAL_TO_DATE"('1000/01/01')<SYSDATE@!)
11 - filter("WUR"."ROLE_ORIG_SYSTEM"='FND_RESP' AND (TRUNC(INTERNAL_FUNCTION("WUR"."EFFECTIVE_START_DATE"))<=TRUNC(SYS
DATE@!) OR "WUR"."EFFECTIVE_START_DATE" IS NULL AND (TRUNC(INTERNAL_FUNCTION("WUR"."START_DATE"))<=TRUNC(SYSDATE@!) OR
"WUR"."START_DATE" IS NULL) AND (TRUNC(INTERNAL_FUNCTION("WUR"."USER_START_DATE"))<=TRUNC(SYSDATE@!) OR
"WUR"."USER_START_DATE" IS NULL) AND (TRUNC(INTERNAL_FUNCTION("WUR"."ROLE_START_DATE"))<=TRUNC(SYSDATE@!) OR
"WUR"."ROLE_START_DATE" IS NULL)) AND (TRUNC(INTERNAL_FUNCTION("WUR"."EFFECTIVE_END_DATE"))>TRUNC(SYSDATE@!) OR
"WUR"."EFFECTIVE_END_DATE" IS NULL AND ("WUR"."USER_END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WUR"."USER_END_DATE"))>TRUNC(SYSDATE@!)) AND ("WUR"."EXPIRATION_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WUR"."EXPIRATION_DATE"))>TRUNC(SYSDATE@!)) AND ("WUR"."ROLE_END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WUR"."ROLE_END_DATE"))>TRUNC(SYSDATE@!))) AND "WUR"."PARTITION_ID"=2)
13 - filter(("WURA"."EFFECTIVE_END_DATE" IS NULL AND ("WURA"."END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WURA"."END_DATE"))>TRUNC(SYSDATE@!)) AND ("WURA"."USER_END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WURA"."USER_END_DATE"))>TRUNC(SYSDATE@!)) AND ("WURA"."ROLE_END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WURA"."ROLE_END_DATE"))>TRUNC(SYSDATE@!)) AND ("WURA"."ASSIGNING_ROLE_END_DATE" IS NULL OR
TRUNC(INTERNAL_FUNCTION("WURA"."ASSIGNING_ROLE_END_DATE"))>TRUNC(SYSDATE@!)) OR
TRUNC(INTERNAL_FUNCTION("WURA"."EFFECTIVE_END_DATE"))>TRUNC(SYSDATE@!)) AND ("WURA"."EFFECTIVE_START_DATE" IS NULL AND
("WURA"."START_DATE" IS NULL OR TRUNC(INTERNAL_FUNCTION("WURA"."START_DATE"))<=TRUNC(SYSDATE@!)) AND
("WURA"."USER_START_DATE" IS NULL OR TRUNC(INTERNAL_FUNCTION("WURA"."USER_START_DATE"))<=TRUNC(SYSDATE@!)) AND
("WURA"."ROLE_START_DATE" IS NULL OR TRUNC(INTERNAL_FUNCTION("WURA"."ROLE_START_DATE"))<=TRUNC(SYSDATE@!)) AND
("WURA"."ASSIGNING_ROLE_START_DATE" IS NULL OR TRUNC(INTERNAL_FUNCTION("WURA"."ASSIGNING_ROLE_START_DATE"))<=TRUNC(SYSDA
TE@!)) OR TRUNC(INTERNAL_FUNCTION("WURA"."EFFECTIVE_START_DATE"))<=TRUNC(SYSDATE@!)))
14 - access("WURA"."USER_NAME"="WUR"."USER_NAME" AND "WURA"."ROLE_NAME"="WUR"."ROLE_NAME")
16 - access("WURA"."USER_NAME"="U"."USER_NAME")
17 - access("WUR"."ROLE_ORIG_SYSTEM_ID"="B"."RESPONSIBILITY_ID")
filter("WUR"."ROLE_ORIG_SYSTEM_ID"="B"."RESPONSIBILITY_ID")
18 - filter("FA"."APPLICATION_SHORT_NAME"=REPLACE(SUBSTR("WURA"."ROLE_NAME",INSTR("WURA"."ROLE_NAME",'|',1,1)+1,INSTR(
"WURA"."ROLE_NAME",'|',1,2)-INSTR("WURA"."ROLE_NAME",'|',1,1)-1),'%col',':'))
19 - access("FA"."APPLICATION_ID"="B"."APPLICATION_ID")
21 - access("B"."APPLICATION_ID"="T"."APPLICATION_ID" AND "B"."RESPONSIBILITY_ID"="T"."RESPONSIBILITY_ID" AND
"T"."LANGUAGE"=USERENV('LANG'))
22 - filter(RAWTOHEX("FND_USER"."USER_GUID")='78cccbec84c3afa0e040bc0a41086474')
23 - access("FND_USER"."USER_ID"="U"."USER_ID")
24 - access("FSG"."SECURITY_GROUP_KEY"=REPLACE(SUBSTR("WURA"."ROLE_NAME",INSTR("WURA"."ROLE_NAME",'|',1,3)+1),'%col','
:'))
Note
-----
- 'PLAN_TABLE' is old version
73 rows selected.
Regards,
Sam