Re: same sql_id with different plan_hash_value
Date: Tue, 27 Mar 2012 10:25:56 -0400
Message-ID: <CAE-dsOKMnfmc4OTZWuN8s+bk02Edj1ZLFJV==RtdpmJELO984w_at_mail.gmail.com>
i figured that out. it was monday morning confusion....ignore my last email.
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?
When I look at the definition of v$sql it doesnt 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.
I have a 3rd question. 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.
One interesting thing is that 2 of the 3 plans look 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.
Here is the definition on v$sql
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
O
>
> On Sun, Mar 25, 2012 at 7:31 AM, Sreejith S Nair <sreejithsna_at_gmail.com>wrote:
>
>>
>> Please see few comments for a similar question posted sometime back
>>
>> There are many reasons why a child cursor is created.
>> It could be that the optimizer environment differs or because the bind
>> variablen had a different length, ... .
>> But, this does not have to mean that a different sql plan will be used.
>>
>> You can check the reasons why a certain child exists for a cursor in the
>> v$sql_shared_cursor view.
>>
>> Apart from a new 11.2 scalability option, things like adaptive cursor
>> sharing and cardinality feedback, and anomalies caused by bugs, a new
>> child
>> cursor is generated when a session tries to use a statement that is in
>> the
>> library cache and finds that every child cursors for that statement
>> already
>> in the cache has a different optimizer environment from the session's
>> optimizer environment. It is possible that different optimizer
>> environments
>> will, however, still produce the same execution plan.
>>
>> A common example of changing the optimizer environment is to enable
>> SQL_trace; other options include local changes to workarea_size_policy,
>> sort_area_size, db_file_multiblock_read_count, and so on. If you check
>> v$sql.optimizer_env_hash_value for the statements you may find that they
>> differ.
>>
>> Regards,
>> Sreejith
>> -- Sent from my iPhone
>>
>> On 24-Mar-2012, at 5:08 AM, GovindanK <gkatteri_at_gmail.com> wrote:
>>
>> A child number will be generated if AUTH_CHECK_MISMATCH occurs too for the
>> same sql_id / query.
>> HTH
>> GovindanK
>>
>> On Fri, Mar 23, 2012 at 12:49 PM, Dba DBA <oracledbaquestions_at_gmail.com
>> >wrote:
>>
>> When I query v$sql_plan, I see some SQL_IDs with different
>> plan_hash_value.
>>
>> I thought if the plan changed while the query was cached, it would
>> generate
>>
>> a child_number.
>>
>> When would a query generate a new child number, vs. a new
>> plan_hash_value?
>>
>> I have one query with
>>
>>
>> 2 plan_hash_value
>>
>>
>> first plan_hash_value has 3 child_numbers
>>
>> second one has 2.
>>
>>
>>
>> --
>>
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 27 2012 - 09:25:56 CDT