v$sql and v$sqlarea

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 27 Mar 2012 10:38:54 -0400
Message-ID: <CAE-dsO+5ewUwusKrUzxCumsYMM4wfKcTqShLQ8md8BRvz4Zi9A_at_mail.gmail.com>

Please ignore my email from yesterday morning. It was monday morning confusion. Starting a new thread because this question is slightly different.
1. if v$sqlarea is a rollup of v$sql to the sql_id level, how does oracle decide which plan_hash_value to have in v$sqlarea? If you have multiple child cursors you can have multiple plan_hash_values, but only 1 is chosen for v$sqlarea?

2. I have 1 query with 3 child cursors and 3 different plan_hash_values. When I look in v$sql_shared_cursor, all of the flags are N. How can I have multiple child cursors if none of the reasons in v$sql_shared_cursor are Y? It is a very simple query. queries a single record on a unique key and joins to another table. End result is 1 record back.

QUERY FORMAT: select cols...
from tab1, tab2
where tab1.key_value = myval
and tab1.key_value = tab2.key_value

3. One interesting thing is that 2 of the 3 plans are exactly the same when I query them from dbms_xplan.display_cursor. I find it interesting that I can get a different plan hash value, but the path looks to be identical. so it has to be something else in v$sql_plan that is leading to a plan_hash_value change. I have not compared all the fields. I just found this interesting.
Anyone else ever see this?

4. When I look at the definition of v$sql it doesn't make sense? This is from
It is querying directly from v$sql. v$sql is a synonym that points to v_$sql.
I ran this from sysdba. See definition below. My understanding is they hit the x$tables. How come I am not seeing that?

select text from dba_views where view_name = 'V_$SQL'; -- run from sys as sysdba




ATA","TYPECHECK_MEM" from v$sql
Received on Tue Mar 27 2012 - 09:38:54 CDT

Original text of this message