| v$sql_plan.object_owner incorrect? [message #583078] |
Thu, 25 April 2013 09:42  |
 |
DKATyler
Messages: 2
Registered: April 2013
|
Junior Member |
|
|
Howdy,
I am looking to understand why V$SQL_PLAN.object_owner doesn't always match the actual object owner for queries.
Some background:
I have 50 schema sets all identical to each-other for the purposes of this question, a set is comprised of an object_owner and an object_user where the owner owns all of the tables/functions/packages etc. and grants the minimum needed permissions to the object_user. The object_user has synonyms to the object_owner's objects. Example of a typical table create is:
object_owner: CREATE TABLE SALES (col_1 NUMBER); GRANT SELECT,INSERT,UPDATE ON sales TO object_user;
object_user: CREATE SYNONYM sales FOR object_owner.sales;
Applications logged in have the object_user that they are logged in as stored in the module via an oracle trigger. Sometimes while querying V$SQL_PLAN,V$SQL I see that the object_owner in V$SQL_PLAN doesn't line up with the appropriate object_owner for the given user.
E.g:
SELECT vs.module,vsp.object_owner
FROM V$SQL vs,V$SQL_PLAN vsp
WHERE vs.plan_hash_value=vsp.plan_hash_value;
Module object_owner
---------------- ------------
STORE1,APP1_USER APP2_OWNER
I did reverify that all of the grants/synonyms and are correct and there is nothing silly like a synonym for app1_user pointing to app2_owner.
Edit: Apologies if this is the incorrect forum, I guessed that questions on V$SQL_PLAN would probably only be asked in relation to performance.
Edit2: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production on OS: RHEL5.9
Accessed via Windows7, Sql Developer
[Updated on: Thu, 25 April 2013 09:55] Report message to a moderator |
|
|
|
|
|
| Re: v$sql_plan.object_owner incorrect? [message #583090 is a reply to message #583083] |
Thu, 25 April 2013 11:56  |
|
DKATyler
Messages: 2
Registered: April 2013
|
Junior Member |
|
|
Cookie,
Thanks for the note and that cleans up most of the cases, however, not all.
SELECT vs.module,vsp.object_owner FROM V$SQL vs,V$SQL_PLAN vsp
WHERE vs.plan_hash_value=vsp.plan_hash_value
AND vs.address=vsp.address
AND vs.hash_value=vsp.hash_value
AND vs.child_number=vsp.child_number
AND MODULE IS NOT NULL
AND OBJECT_OWNER IS NOT NULL
AND MODULE NOT LIKE '%'||SUBSTR(OBJECT_OWNER,1,INSTR(OBJECT_OWNER,'_',-1))||'USER'
AND vsp.object_owner NOT IN ('SYS','SYSTEM');
Module object_owner
---------------- ------------
STORE1,APP1_USER APP2_OWNER
Additional detail: I first identified this curious case by reading through the statistics gathered by statspack. The relevant portion of the oracle written snap that obtains the plan is below (truncated the top part of the query to save space):
SELECT s.module,sp.object_owner FROM --The selected columns altered for this post.
(select /*+ index(spu) */
spu.plan_hash_value
, spu.hash_value hash_value
, spu.address address
, spu.text_subset text_subset
, spu.snap_id snap_id
from stats$sql_plan_usage spu
) new_plan
, v$sql s -- join reqd to filter already known plans
, v$sql_plan sp
where s.address = new_plan.address
and s.plan_hash_value = new_plan.plan_hash_value
and s.hash_value = new_plan.hash_value
and sp.hash_value = new_plan.hash_value
and sp.address = new_plan.address
and sp.hash_value = s.hash_value
and sp.address = s.address
and sp.child_number = s.child_number;
The query I was using that piqued my interest:
SELECT *
FROM stats$sql_plan ssp,stats$sql_plan_usage sspu,stats$sql_summary sss
WHERE ssp.plan_hash_value=sspu.plan_hash_value
AND sspu.old_hash_value=sss.old_hash_value
AND sss.module NOT LIKE '%'||SUBSTR(ssp.object_owner,1,INSTR(ssp.object_owner,'_',-1))||'USER';
Edit: Typo in select
[Updated on: Thu, 25 April 2013 12:32] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
