Notes about Parsing in Oracle

This is just some casual notes about parsing. For more information regarding parsing and how to find out/resolve parsing issues, please refer to chapter 2 and chapter 8 of <> by Christian Antognini.

------------------------------------------------------------------------------------------------------------

When shareable parent and child cursors are available and, consequently, only the first
two operations are carried out, the parse is called a soft parse. When all operations are carried
out, it is called a hard parse.

The result of a parse operation is a parent cursor and a child cursor stored in the library cache.

Once stored in the library cache, parent and child cursors are externalized through the
views v$sqlarea and v$sql, respectively.

Parent Cursor: v$sqlarea
Child Cursor: v$sql

/***************************************************************************************
***********  Unsharable Parent Cursor  *********************************************
***************************************************************************************/

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from math;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    1                            35
1                    2                            50
1                    3                            80
2                    1                            89
2                    2                            78
2                    3                            45
3                    1                            74
3                    2                            88
3                    3                            88

9 rows selected.

SQL> select * from math where class_id=1234567;

no rows selected

SQL> select * from math where class_id=1234567;

no rows selected

SQL> SELECT * FROM MATH WHERE class_id=1234567;

no rows selected

SQL> select * from math where class_id = 1234567;

no rows selected

SQL> select sql_id, sql_text, executions
  2  from v$sqlarea
  3  where sql_text like '%123456';

SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
cqrc0s5qx1qzs select * from math where student_id = 123456                          1
1vgu7zqj1qc0z SELECT * FROM MATH WHERE STUDENT_ID=123456                            1
80rgungv4qnbf select * from math where student_id=123456                            2

SQL> select sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
  2  from v$sql
  3  where sql_id in ('cqrc0s5qx1qzs','1vgu7zqj1qc0z','80rgungv4qnbf');

SQL_ID        CHILD_NUMBER SQL_TEXT                                                     OPTIMIZER_ PLAN_HASH_VALUE
------------- ------------ ------------------------------------------------------------ ---------- ---------------
cqrc0s5qx1qzs            0 select * from math where student_id = 123456                 ALL_ROWS         730931367
1vgu7zqj1qc0z            0 SELECT * FROM MATH WHERE STUDENT_ID=123456                   ALL_ROWS         730931367
80rgungv4qnbf            0 select * from math where student_id=123456                   ALL_ROWS         730931367

SQL>

/***************************************************************************************
**********   Unsharable Child Cursor   **********************************************
***************************************************************************************/

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> select count(*) from math;

  COUNT(*)
----------
         9

SQL> alter session set optimizer_mode = first_rows_10;

Session altered.

SQL> select count(*) from math;

  COUNT(*)
----------
         9

SQL> select sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
  2  from v$sql
  3  where sql_id = (select prev_sql_id
  4  from v$session
  5  where sid = sys_context('userenv','sid'));

SQL_ID        CHILD_NUMBER SQL_TEXT                                                     OPTIMIZER_ PLAN_HASH_VALUE
------------- ------------ ------------------------------------------------------------ ---------- ---------------
3wbsy78w80uk1            0 select count(*) from math                                    ALL_ROWS        2816132623
3wbsy78w80uk1            1 select count(*) from math                                    FIRST_ROWS      2816132623

SQL>
SQL>
SQL> select child_number, optimizer_mode_mismatch
  2  from v$sql_shared_cursor
  3  where sql_id = '3wbsy78w80uk1';

CHILD_NUMBER O
------------ -
           0 N
           1 Y

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-672424/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9765498/viewspace-672424/