RE: Child Cursor and Plan
Date: Thu, 2 Feb 2012 14:56:25 +0100
Message-ID: <4814386347E41145AAE79139EAA398981B3BEC1566_at_ws03-exch07.iconos.be>
Hi,
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.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sreejith S Nair
Sent: donderdag 2 februari 2012 14:37
To: oracle-l_at_freelists.org
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_at_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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 02 2012 - 07:56:25 CST