Message-ID: <3297D21A133141B1BB8AF4FEF931573E@Primary>
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
To: <oracle-l@freelists.org>
References: <OFA00B57EC.FFF00C5B-ON65257998.004A52CF-65257998.004AC1D6@ibsplc.com>
Subject: Re: Child Cursor and Plan
Date: Thu, 2 Feb 2012 13:58:11 -0000
MIME-Version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jonathan@jlcomp.demon.co.uk


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

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


----- Original Message ----- 
From: "Sreejith S Nair" <Sreejith.Sreekantan@ibsplc.com>
To: <oracle-l@freelists.org>
Sent: Thursday, February 02, 2012 1:36 PM
Subject: Child Cursor and Plan


Hi Friends,
Oracle 11.2.0.2 on Solaris 10.

From my understanding a child cursor is generated when a sql statement
will have multiple execution plans. Here is what I see in one of our
database

SYS> select sql_id,child_number,hash_value,PLAN_HASH_VALUE,CHILD_ADDRESS
from v$sql where sql_id='c8gnrhxma4tas';

SQL_ID        CHILD_NUMBER HASH_VALUE PLAN_HASH_VALUE CHILD_ADDRESS
------------- ------------ ---------- --------------- ----------------
c8gnrhxma4tas            0 1721918808      4024720576 00000004481E01A8
c8gnrhxma4tas            1 1721918808      4024720576 0000000456DEF070
c8gnrhxma4tas            2 1721918808      4024720576 0000000448F3C210
c8gnrhxma4tas            3 1721918808      4024720576 000000047CA5BD30
c8gnrhxma4tas            4 1721918808      4024720576 0000000448476B88
c8gnrhxma4tas            5 1721918808      4024720576 000000047C217998
c8gnrhxma4tas            6 1721918808      4024720576 00000004483B0B78
c8gnrhxma4tas            7 1721918808      4024720576 000000047C3F9668

Here I see that CHILD_NUMBER and different CHILD_ADDRESS are generated for
same PLAN_HASH_VALUE. Can someone please give some light on what exactly
is a child cursor ?
This is what I got from Google. Could not find a proper definition in
documentation.May be I missed ?

The parent cursor contains the SQL statement text only, but no execution
plan.
Execution plans are found in child cursors. Child cursors are also called
versions.

With Regards,
Sreejith

--
Sreejith S Nair
Associate Systems Architect | AOS DBA Team
IBS Software Services Private Ltd.
2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
India
((Direct) +91 471 661 4707 ) +91 808 648 5523
*sreejith.sreekantan@ibsplc.com
8 www.ibsplc.com


DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."


--
http://www.freelists.org/webpage/oracle-l


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2112/4780 - Release Date: 02/01/12


--
http://www.freelists.org/webpage/oracle-l