Re: same sql_id with different plan_hash_value
- From: Sreejith S Nair <sreejithsna@xxxxxxxxx>
- To: "gkatteri@xxxxxxxxx" <gkatteri@xxxxxxxxx>
- Date: Sun, 25 Mar 2012 17:01:15 +0530
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@xxxxxxxxx> 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@xxxxxxxxx>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.
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
