v$sql and v$sqlarea
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 10.2.0.5
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
select "SQL_TEXT","SQL_FULLTEXT","
SQL_ID","SHARABLE_MEM","PERSISTENT_MEM","RUNTI
ME_MEM","SORTS","LOADED_VERSIONS","OPEN_VERSIONS","USERS_OPENING","FETCHES","EXE
CUTIONS","PX_SERVERS_EXECUTIONS","END_OF_FETCH_COUNT","USERS_EXECUTING","LOADS",
"FIRST_LOAD_TIME","INVALIDATIONS","PARSE_CALLS","DISK_READS","DIRECT_WRITES","BU
FFER_GETS","APPLICATION_WAIT_TIME","CONCURRENCY_WAIT_TIME","CLUSTER_WAIT_TIME","
USER_IO_WAIT_TIME","PLSQL_EXEC_TIME","JAVA_EXEC_TIME","ROWS_PROCESSED","COMMAND_
TYPE","OPTIMIZER_MODE","OPTIMIZER_COST","OPTIMIZER_ENV","OPTIMIZER_ENV_HASH_VALU
E","PARSING_USER_ID","PARSING_SCHEMA_ID","PARSING_SCHEMA_NAME","KEPT_VERSIONS","
ADDRESS","TYPE_CHK_HEAP","HASH_VALUE","OLD_HASH_VALUE","PLAN_HASH_VALUE","CHILD_
NUMBER","SERVICE","SERVICE_HASH","MODULE","MODULE_HASH","ACTION","ACTION_HASH","
SERIALIZABLE_ABORTS","OUTLINE_CATEGORY","CPU_TIME","ELAPSED_TIME","OUTLINE_SID",
"CHILD_ADDRESS","SQLTYPE","REMOTE","OBJECT_STATUS","LITERAL_HASH_VALUE","LAST_LO
AD_TIME","IS_OBSOLETE","CHILD_LATCH","SQL_PROFILE","PROGRAM_ID","PROGRAM_LINE#",
"EXACT_MATCHING_SIGNATURE","FORCE_MATCHING_SIGNATURE","LAST_ACTIVE_TIME","BIND_D
ATA","TYPECHECK_MEM" from v$sql
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 27 2012 - 09:38:54 CDT