Home -> Mailing Lists -> Oracle-L -> RE: v$sql_plan object_node strange results

RE: v$sql_plan object_node strange results

From: Wisniewski, Brian S <brian.s.wisniewski_at_jpmchase.com>
Date: Fri, 8 Mar 2013 17:21:46 +0000
Message-ID: <071E25C262A88E4D8117C560596C2E13304AA379_at_SCACMX055.exchad.jpmchase.net> 





But it's not the name of the link used to reference the object.  I'm selecting against the v$sql_plan view across the database link, I'm not selecting the actual underlying objects across the links.  The v$sql_plan view is returning different results depending upon whether I access it remotely or locally.  And it's the instance_name being populated in that column when selected remotely, not the database link I'm using.




    
  
  •   Brian






From: Luis [mailto:lcarapinha_at_gmail.com]
Sent: Friday, March 08, 2013 12:15 PM


To: Wisniewski, Brian S


Cc: oracle-l_at_freelists.org


Subject: Re: v$sql_plan object_node strange results



Hi Brian,



If you go to description here: http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2120.htm



"Name of the database link used to reference the object (a table name or view name). For local queries that use parallel execution, this column describes the order in which output from operations is consumed."



Resumed:

1) remote query: name of database link
2) local with parallel: order of output
3) local with no parallel: null?





Thanks,


Luis




On Fri, Mar 8, 2013 at 5:05 PM, Wisniewski, Brian S <brian.s.wisniewski_at_jpmchase.com<mailto:brian.s.wisniewski_at_jpmchase.com>> wrote:
11.1.0.7 or 11.2.0.2 database


I don't understand why the object_node changes from v$sql_plan when you're selecting against the view remotely or locally.  Can anyone shed light on this?



I have to write more logic to account for this now because  that I understand what it's doing but it was quite maddening for a while getting different results.



When selecting locally  object_node is null:



11.1.0.7



SQL> select object_name,object_node, sql_id  from v$sql_plan where sql_id = '9u0hx34qg80uv' and object_name is not null;


OBJECT_NAME                    OBJECT_NODE                              SQL_ID


------------------------------ ---------------------------------------- -------------

TBL_ADVDB_APPEAL                                                        9u0hx34qg80uv
TBL_ADVDB_ASSIGN_STATUS                                                 9u0hx34qg80uv
TBL_ADVDB_BLOCK_TYPE                                                    9u0hx34qg80uv





11.2



select object_name, object_node, sql_id from v$sql_plan where sql_id = 'gzxjptnb43n1k' and object_name is not null;


OBJECT_NAME                    OBJECT_NODE                              SQL_ID


------------------------------ ---------------------------------------- -------------

ADW_AUCTION_DETAIL                                                      gzxjptnb43n1k






When selecting against the same database across a database link OBJECT_NODE gets populated with the instance_name



11.1.0.7



SQL> select object_name,object_node, sql_id  from v$sql_plan_at_busdev where sql_id = '9u0hx34qg80uv' and object_name is not null;


OBJECT_NAME                    OBJECT_NODE                              SQL_ID


------------------------------ ---------------------------------------- -------------

TBL_ADVDB_APPEAL               RISK                                     9u0hx34qg80uv
TBL_ADVDB_ASSIGN_STATUS        RISK                                     9u0hx34qg80uv
TBL_ADVDB_BLOCK_TYPE           RISK                                     9u0hx34qg80uv





11.2



select object_name, object_node, sql_id from v$sql_plan_at_busg05d where sql_id = 'gzxjptnb43n1k' and object_name is not null;


OBJECT_NAME                    OBJECT_NODE                              SQL_ID


------------------------------ ---------------------------------------- -------------

ADW_AUCTION_DETAIL             BUSG05D                                  gzxjptnb43n1k






This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.



This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.



Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.


--



http://www.freelists.org/webpage/oracle-l






--



Cumprimentos,


Luís Marques




This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.



This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.



Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.


--



http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 08 2013 - 18:21:46 CET












Original text of this message