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