11 Replies Latest reply: Jun 1, 2011 1:15 AM by user8779435 RSS

    Parsing sql - parent and child cursor

    user8779435
      dear all

      What is the parent and child cursor while parsing sql?

      thanks

      John
        • 1. Re: Parsing sql - parent and child cursor
          sb92075
          Handle:      user8779435
          Status Level:      Newbie
          Registered:      Aug 31, 2009
          Total Posts:      135
          Total Questions:      50 (37 unresolved)
          so many questions & so few answers.
          :-(
          What is the parent and child cursor while parsing sql?
          Do you have a parent?
          • 2. Re: Parsing sql - parent and child cursor
            user8779435
            sb92075 wrote:
            Handle:      user8779435
            Status Level:      Newbie
            Registered:      Aug 31, 2009
            Total Posts:      135
            Total Questions:      50 (37 unresolved)
            so many questions & so few answers.
            :-(
            What is the parent and child cursor while parsing sql?
            Do you have a parent?
            Because they are not answered!

            Don't know answer? then shut up please. you just check "who give marks" and answer them to gain some mark ... Foolish
            • 3. Re: Parsing sql - parent and child cursor
              anand prakash - oracle
              The Parent cursor stores the sql text of the cursor.When two statements are identical textually, they will share a same Parent Cursor

              The Child Cursor holds other required information, like: the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used..etc.


              http://www.ixora.com.au/q+a/0104/19005414.htm

              Anand
              • 4. Re: Parsing sql - parent and child cursor
                user8779435
                Anand.. wrote:
                The Parent cursor stores the sql text of the cursor.When two statements are identical textually, they will share a same Parent Cursor

                The Child Cursor holds other required information, like: the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used..etc.


                http://www.ixora.com.au/q+a/0104/19005414.htm

                Anand
                Thank you Anand for the answer. could you show a little example on parent and shared cursors using v$ views?

                thanks in advance

                john
                • 5. Re: Parsing sql - parent and child cursor
                  Chinar
                  What is the parent and child cursor while parsing sql?
                  This appear based on re optimization mechanism of oracle.For example you have a query which contain bind variable(or you set CURSOR_SHARING parameter to SIMILAR or FORCE ,in this case oracle will replace all literals with bind variable).And consider that this query actually executed many times ,now again we executed that but different bind values,in this case oracle think using this bind variable need different execution plan(more efficient) and that all happen in parse/optimization time.So in this case oracle will generate second cursor and that is called child cursor.And you can monitor this from v$sql

                  Edited by: Chinar on May 31, 2011 10:02 PM
                  • 6. Re: Parsing sql - parent and child cursor
                    user8779435
                    which view shows parent cursor and which child? please show an example

                    thanks

                    john
                    • 7. Re: Parsing sql - parent and child cursor
                      anand prakash - oracle
                      v$sqlarea maintains single row per parent cursor, and v$sql maintains each row for a Child Cursor.


                      Anand
                      • 9. Re: Parsing sql - parent and child cursor
                        anand prakash - oracle
                        Hi,

                        You know the views now so why not now dig yourself and check :)

                        Anand
                        • 10. Re: Parsing sql - parent and child cursor
                          Aman....
                          Well, as Anand mentioned you could it your self. Here is a workaround based demo. Based on the environment change, the cursors would be created and won't be shared which I did using the parameter change of optimizer_mode. This is done in 11201 with the parameter optimizer_features_enable set to 10201.
                          SQL> drop table t purge;
                          
                          Table dropped.
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%';
                          
                          SQL_TEXT
                          --------------------------------------------------------------------------------
                          select * from t
                          
                          SQL> alter system flush shared_pool;
                          
                          System altered.
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%';
                          
                          no rows selected
                          
                          SQL> save a
                          Created file a.sql
                          SQL> select * from t;
                          select * from t
                                        *
                          ERROR at line 1:
                          ORA-00942: table or view does not exist
                          
                          
                          SQL> create table t(a char);
                          
                          Table created.
                          
                          SQL> select * from t;
                          
                          no rows selected
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%';
                          
                          SQL_TEXT
                          --------------------------------------------------------------------------------
                          select * from t
                          
                          SQL> select * from T;
                          
                          no rows selected
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%';
                          
                          SQL_TEXT
                          --------------------------------------------------------------------------------
                          select * from t
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT
                          --------------------------------------------------------------------------------
                          select * from t
                          select * from T
                          
                          SQL> select sql_text,version_count, executions from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT
                          --------------------------------------------------------------------------------
                          VERSION_COUNT EXECUTIONS
                          ------------- ----------
                          select * from t
                                      1          1
                          
                          select * from T
                                      1          1
                          
                          
                          SQL> column sql_text format a40
                          SQL> /
                          
                          SQL_TEXT                                 VERSION_COUNT EXECUTIONS
                          ---------------------------------------- ------------- ----------
                          select * from t                                      1          1
                          select * from T                                      1          1
                          
                          SQL> select * from T;
                          
                          no rows selected
                          
                          SQL> select sql_text from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT
                          ----------------------------------------
                          select * from t
                          select * from T
                          
                          SQL> select sql_text,version_count, executions from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT                                 VERSION_COUNT EXECUTIONS
                          ---------------------------------------- ------------- ----------
                          select * from t                                      1          1
                          select * from T                                      1          2
                          
                          SQL> alter session set optimizer_mode=first_rows;
                          
                          Session altered.
                          
                          SQL> select * from T;
                          
                          no rows selected
                          
                          SQL> select * from t;
                          
                          no rows selected
                          
                          SQL> select sql_text,version_count, executions from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT                                 VERSION_COUNT EXECUTIONS
                          ---------------------------------------- ------------- ----------
                          select * from t                                      1          1
                          select * from test_sharing where id=:a               1          3
                          select * from test_sharing where id=1                1          0
                          select * from test_sharing where id=99               1          0
                          select * from T                                      2          3
                          
                          SQL> select sql_text, child_number, optimizer_mode, plan_hash_value from V$sql where sql_text like 'select * from t%' or sql_text like 'select * fro
                          
                          SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
                          ---------------------------------------- ------------ ---------- ---------------
                          select * from t                                     0 FIRST_ROWS      1601196873
                          select * from test_sharing where id=:a              0 ALL_ROWS        3492249339
                          select * from test_sharing where id=1               0 ALL_ROWS        3492249339
                          select * from test_sharing where id=99              0 ALL_ROWS        2354865636
                          select * from T                                     0 ALL_ROWS        1601196873
                          select * from T                                     1 FIRST_ROWS      1601196873
                          
                          6 rows selected.
                          
                          SQL> alter session set optimizer_mode=first_rows_1;
                          
                          Session altered.
                          
                          SQL> select * from t;
                          
                          no rows selected
                          
                          SQL> select * from T;
                          
                          no rows selected
                          
                          SQL> select sql_text, child_number, optimizer_mode, plan_hash_value from V$sql where sql_text like 'select * from t%' or sql_text like 'select * fro
                          
                          SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
                          ---------------------------------------- ------------ ---------- ---------------
                          select * from t                                     0 FIRST_ROWS      1601196873
                          select * from test_sharing where id=:a              0 ALL_ROWS        3492249339
                          select * from test_sharing where id=1               0 ALL_ROWS        3492249339
                          select * from test_sharing where id=99              0 ALL_ROWS        2354865636
                          select * from T                                     0 ALL_ROWS        1601196873
                          select * from T                                     1 FIRST_ROWS      1601196873
                          
                          6 rows selected.
                          
                          SQL> alter session set sql_trace=true;
                          
                          Session altered.
                          
                          SQL> alter session set optimizer_mode=first_rows_1;
                          
                          Session altered.
                          
                          SQL> select * from t;
                          
                          no rows selected
                          
                          SQL> select * from T;
                          
                          no rows selected
                          
                          SQL> select sql_text,version_count, executions from V$sqlarea where sql_text like 'select * from t%' or sql_text like 'select * from T%';
                          
                          SQL_TEXT                                 VERSION_COUNT EXECUTIONS
                          ---------------------------------------- ------------- ----------
                          select * from t                                      2          3
                          select * from test_sharing where id=:a               1          3
                          select * from test_sharing where id=1                1          0
                          select * from test_sharing where id=99               1          0
                          select * from T                                      3          5
                          
                          
                          SQL> select sql_text, child_number, optimizer_mode, plan_hash_value from V$sql where sql_text like 'select * from t%' or sql_text like 'select * fro
                          
                          SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
                          ---------------------------------------- ------------ ---------- ---------------
                          select * from t                                     0 FIRST_ROWS      1601196873
                          select * from t                                     1 FIRST_ROWS      1601196873
                          select * from test_sharing where id=:a              0 ALL_ROWS        3492249339
                          select * from test_sharing where id=1               0 ALL_ROWS        3492249339
                          select * from test_sharing where id=99              0 ALL_ROWS        2354865636
                          select * from T                                     0 ALL_ROWS        1601196873
                          select * from T                                     1 FIRST_ROWS      1601196873
                          select * from T                                     2 FIRST_ROWS      1601196873
                          
                          8 rows selected.
                          
                          SQL> select sql_id,sql_text, child_number, optimizer_mode, plan_hash_value from V$sql where sql_text like 'select * from t%' or sql_text like 'selec
                          
                          SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_
                          ------------- ---------------------------------------- ------------ ----------
                          PLAN_HASH_VALUE
                          ---------------
                          89km4qj1thh13 select * from t                                     0 FIRST_ROWS
                               1601196873
                          
                          89km4qj1thh13 select * from t                                     1 FIRST_ROWS
                               1601196873
                          
                          7gbgb5nzcdcf3 select * from test_sharing where id=:a              0 ALL_ROWS
                               3492249339
                          
                          
                          SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_
                          ------------- ---------------------------------------- ------------ ----------
                          PLAN_HASH_VALUE
                          ---------------
                          0890tcnrf5jsv select * from test_sharing where id=1               0 ALL_ROWS
                               3492249339
                          
                          7hg3cujy0ya0r select * from test_sharing where id=99              0 ALL_ROWS
                               2354865636
                          
                          ahgbnyrbh7bp1 select * from T                                     0 ALL_ROWS
                               1601196873
                          
                          
                          SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_
                          ------------- ---------------------------------------- ------------ ----------
                          PLAN_HASH_VALUE
                          ---------------
                          ahgbnyrbh7bp1 select * from T                                     1 FIRST_ROWS
                               1601196873
                          
                          ahgbnyrbh7bp1 select * from T                                     2 FIRST_ROWS
                               1601196873
                          
                          
                          8 rows selected.
                          
                          SQL> set pagesize 9999
                          SQL> /
                          
                          SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_
                          ------------- ---------------------------------------- ------------ ----------
                          PLAN_HASH_VALUE
                          ---------------
                          89km4qj1thh13 select * from t                                     0 FIRST_ROWS
                               1601196873
                          
                          89km4qj1thh13 select * from t                                     1 FIRST_ROWS
                               1601196873
                          
                          7gbgb5nzcdcf3 select * from test_sharing where id=:a              0 ALL_ROWS
                               3492249339
                          
                          0890tcnrf5jsv select * from test_sharing where id=1               0 ALL_ROWS
                               3492249339
                          
                          7hg3cujy0ya0r select * from test_sharing where id=99              0 ALL_ROWS
                               2354865636
                          
                          ahgbnyrbh7bp1 select * from T                                     0 ALL_ROWS
                               1601196873
                          
                          ahgbnyrbh7bp1 select * from T                                     1 FIRST_ROWS
                               1601196873
                          
                          ahgbnyrbh7bp1 select * from T                                     2 FIRST_ROWS
                               1601196873
                          
                          
                          8 rows selected.
                          
                          SQL> set linesize 200
                          SQL> /
                          
                          SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
                          ------------- ---------------------------------------- ------------ ---------- ---------------
                          89km4qj1thh13 select * from t                                     0 FIRST_ROWS      1601196873
                          89km4qj1thh13 select * from t                                     1 FIRST_ROWS      1601196873
                          7gbgb5nzcdcf3 select * from test_sharing where id=:a              0 ALL_ROWS        3492249339
                          0890tcnrf5jsv select * from test_sharing where id=1               0 ALL_ROWS        3492249339
                          7hg3cujy0ya0r select * from test_sharing where id=99              0 ALL_ROWS        2354865636
                          ahgbnyrbh7bp1 select * from T                                     0 ALL_ROWS        1601196873
                          ahgbnyrbh7bp1 select * from T                                     1 FIRST_ROWS      1601196873
                          ahgbnyrbh7bp1 select * from T                                     2 FIRST_ROWS      1601196873
                          
                          8 rows selected.
                          
                          SQL> select child_number, child_address, stats_row_mismatch, optimizer_mode_mismatch
                            2  from v$sql_shared_cursor where sql_id='ahgbnyrbh7bp1';
                          
                          CHILD_NUMBER CHILD_AD S O
                          ------------ -------- - -
                                     0 1A610050 N N
                                     1 1F148DA4 N Y
                                     2 1A630C90 Y N
                          
                          SQL>
                          You can see a mismatch in the optimizer_mode resulting into another child cursor creation. You can try using the cursor_sharing parameter set to similar and bind variables which would also cause multiple child cursors to be created. For the view V$sql_shared_cursor, check the docs.

                          HTH
                          Aman....

                          PS: Please don't bump up the thread. Its not support so people are not forced to update it immediately. All are volunteers so assume that they would update the thread as and when they would have/get time to do so.
                          • 11. Re: Parsing sql - parent and child cursor
                            user8779435
                            thanks Aman

                            the demo was good

                            john