Re: v$sql_plan object_node strange results

From: Luis <lcarapinha_at_gmail.com>
Date: Fri, 8 Mar 2013 17:15:13 +0000
Message-ID: <CANx=q_YdKx-Lq-V+_kV42_Qh5x-mw-J6hRGfU3kv5317yd_QMg_at_mail.gmail.com>



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> 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

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

Original text of this message