Home » RDBMS Server » Performance Tuning » v$sql_plan.object_owner incorrect?
v$sql_plan.object_owner incorrect? [message #583078] Thu, 25 April 2013 09:42 Go to next message
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 #583083 is a reply to message #583078] Thu, 25 April 2013 10:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looking at the view definitions I believe your join is wrong.
Try on address, hash_value and child_number.

plan_hash_value is not unique to a cursor.
Re: v$sql_plan.object_owner incorrect? [message #583090 is a reply to message #583083] Thu, 25 April 2013 11:56 Go to previous message
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

Previous Topic: checking the delete performance in the package
Next Topic: Bind variable issue
Goto Forum:
  


Current Time: Thu Mar 28 05:24:33 CDT 2024