Hi everyone.
I've encountered one of the strangest things I've ever seen with Oracle. I'm hoping that someone else here has seen something like this before and solved it! On an 11g database I have a query that runs differently depending on which user runs it. If the owner of the tables or someone with the DBA role runs the query I get a perfect execution plan. If someone else runs it, I get a really bad execution plan - though the query still executes. So it almost seems like depending on who is running the query, the optimizer might not have access to the same statistics?? I'm really grasping at straws here - any help would be greatfully accepted!!!
Here is the query and the two plans for it...
On TASD as a General User (Bad execution plan) - CA17062 is USER
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as ca17062
SQL> explain plan for
select w.worker_id, w.worker_name
from worker_v w,
worker_cost_centre_v c
where w.worker_id = c.worker_id
and c.effective_date <= trunc(sysdate)
and c.expiration_date >= trunc(sysdate)
and c.cost_centre = '100033'
and pkg_taw_security.user_worker_access('CA17062',
'TIMEKEEPER',
w.worker_id,
trunc(sysdate)) = 1
order by w.worker_name;
Explained
Executed in 0.234 seconds
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1726112176
---------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | | 8 | SELECT STATEMENT | | 18 | 1800 | 606 (1)| 00:00:01 |
| 1 | 0 | 7 | SORT ORDER BY | | 18 | 1800 | 606 (1)| 00:00:01 |
|* 2 | 1 | 6 | HASH JOIN | | 18 | 1800 | 605 (1)| 00:00:01 |
| 3 | 2 | 3 | VIEW | WORKER_COST_CENTRE_V | 18 | 558 | 19 (0)| 00:00:01 |
|* 4 | 3 | 2 | TABLE ACCESS BY INDEX ROWID| WORKER_COST_CENTRE_TBL | 18 | 522 | 19 (0)| 00:00:01 |
|* 5 | 4 | 1 | INDEX RANGE SCAN | WORKER_CC_CC_IDX | 29 | | 3 (0)| 00:00:01 |
|* 6 | 2 | 5 | VIEW | WORKER_V | 161K| 10M| 584 (1)| 00:00:01 |
| 7 | 6 | 4 | TABLE ACCESS FULL | WORKER_TBL | 161K| 3466K| 584 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"."WORKER_ID"="C"."WORKER_ID")
4 - filter("X"."EXPIRATION_DATE">=TRUNC(SYSDATE@!))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("X"."COST_CENTRE"='100033' AND "X"."EFFECTIVE_DATE"<=TRUNC(SYSDATE@!))
6 - filter("PKG_TAW_SECURITY"."USER_WORKER_ACCESS"('CA17062','TIMEKEEPER',"W"."WORKER_ID",TRUN
C(SYSDATE@!))=1)
About
------
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
23 rows selected
Executed in 0.577 seconds
WORKER_ID WORKER_NAME
--------- --------------------------------------------------------------------------------
123703 FADDEN, CLAYTON
11131 HAHN, BRAD
33811 HALL, MAUREEN
53934 JANES, CATHERINE
Executed in 35.241 seconds
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
On TASD as the owner of the tables or as someone with the DBA role (Good Execution) - TAS is USER:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as tas
SQL> explain plan for
select w.worker_id, w.worker_name
from worker_v w,
worker_cost_centre_v c
where w.worker_id = c.worker_id
and c.effective_date <= trunc(sysdate)
and c.expiration_date >= trunc(sysdate)
and c.cost_centre = '100033'
and pkg_taw_security.user_worker_access('CA17062',
'TIMEKEEPER',
w.worker_id,
trunc(sysdate)) = 1
order by w.worker_name;
Explained
Executed in 0.203 seconds
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3435904055
---------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | | 8 | SELECT STATEMENT | | 18 | 918 | 38 (3)| 00:00:01 |
| 1 | 0 | 7 | SORT ORDER BY | | 18 | 918 | 38 (3)| 00:00:01 |
| 2 | 1 | 6 | NESTED LOOPS | | | | | |
| 3 | 2 | 4 | NESTED LOOPS | | 18 | 918 | 37 (0)| 00:00:01 |
|* 4 | 3 | 2 | TABLE ACCESS BY INDEX ROWID| WORKER_COST_CENTRE_TBL | 18 | 522 | 19 (0)| 00:00:01 |
|* 5 | 4 | 1 | INDEX RANGE SCAN | WORKER_CC_CC_IDX | 29 | | 3 (0)| 00:00:01 |
|* 6 | 3 | 3 | INDEX UNIQUE SCAN | WORKER_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | 2 | 5 | TABLE ACCESS BY INDEX ROWID | WORKER_TBL | 1 | 22 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("X"."EXPIRATION_DATE">=TRUNC(SYSDATE@!))
5 - access("X"."COST_CENTRE"='100033' AND "X"."EFFECTIVE_DATE"<=TRUNC(SYSDATE@!))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
6 - access("X"."WORKER_ID"="X"."WORKER_ID")
filter("PKG_TAW_SECURITY"."USER_WORKER_ACCESS"('CA17062','TIMEKEEPER',"X"."WORKER_ID",TRUN
C(SYSDATE@!))=1)
About
------
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
23 rows selected
Executed in 0.624 seconds
WORKER_ID WORKER_NAME
--------- --------------------------------------------------------------------------------
123703 FADDEN, CLAYTON
11131 HAHN, BRAD
33811 HALL, MAUREEN
53934 JANES, CATHERINE
Executed in 1.307 seconds
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
THANKS!!!
Cory Aston