RE: v$sql_plan object_node strange results
- From: "Wisniewski, Brian S" <brian.s.wisniewski@xxxxxxxxxxxx>
- To: Luis <lcarapinha@xxxxxxxxx>
- Date: Fri, 8 Mar 2013 17:21:46 +0000
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@xxxxxxxxx]
Sent: Friday, March 08, 2013 12:15 PM
To: Wisniewski, Brian S
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:brian.s.wisniewski@xxxxxxxxxxxx>> 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@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@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.
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
