Tests via DBMS_XPLAN


12:34:01 scott@TESTORA> select * from table(
  2*    dbms_xplan.display_cursor('&m_sql_id',&m_child_no,'outline peeked_binds'));

<!--more-->

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  cjgnx825610ky, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ empno, ename, deptno from
employees where empno= :m_number1 and empno between :m_number1 and
:m_number2

Plan hash value: 370102239

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     1 (100)|          |
|*  1 |  FILTER                      |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | E_PK      |     1 |       |     0   (0)|          |
------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPNO"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :M_NUMBER1 (NUMBER): 7369
   2 - :M_NUMBER1 (NUMBER, Primary=1)
   3 - :M_NUMBER2 (NUMBER): 8000

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:M_NUMBER2&gt;=:M_NUMBER1 AND :M_NUMBER1&lt;=:M_NUMBER1))    3 - access("EMPNO"=:M_NUMBER1)        filter(("EMPNO"&gt;=:M_NUMBER1 AND "EMPNO"&lt;=:M_NUMBER2)) 45 rows selected. Elapsed: 00:00:00.33 12:41:48 scott@TESTORA&gt; select * from table(
12:41:48   2    dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'ALLSTATS LAST'));
Enter value for m_sql_id: cjgnx825610ky
Enter value for m_child_no: 0
old   2:        dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'ALLSTATS LAST'))
new   2:        dbms_xplan.display_cursor('cjgnx825610ky',0,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  cjgnx825610ky, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ empno, ename, deptno from
employees where empno= :m_number1 and empno between :m_number1 and
:m_number2

Plan hash value: 370102239

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  FILTER                      |           |      1 |        |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | E_PK      |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:M_NUMBER2&gt;=:M_NUMBER1 AND :M_NUMBER1&lt;=:M_NUMBER1))    3 - access("EMPNO"=:M_NUMBER1)        filter(("EMPNO"&gt;=:M_NUMBER1 AND "EMPNO"&lt;=:M_NUMBER2)) 24 rows selected. Elapsed: 00:00:00.07 12:49:32 scott@TESTORA&gt; SELECT plan_table_output
12:49:34   2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=&gt;'ADVANCED'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

Elapsed: 00:00:00.04



12:50:11 scott@TESTORA&gt; select * from table(
12:50:11   2    dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'ADVANCED'));
Enter value for m_sql_id: cjgnx825610ky
Enter value for m_child_no: 0
old   2:        dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'ADVANCED'))
new   2:        dbms_xplan.display_cursor('cjgnx825610ky',0,'ADVANCED'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  cjgnx825610ky, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ empno, ename, deptno from
employees where empno= :m_number1 and empno between :m_number1 and
:m_number2

Plan hash value: 370102239

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     1 (100)|          |
|*  1 |  FILTER                      |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | E_PK      |     1 |       |     0   (0)|          |
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1
   3 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPNO"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :M_NUMBER1 (NUMBER): 7369
   2 - :M_NUMBER1 (NUMBER, Primary=1)
   3 - :M_NUMBER2 (NUMBER): 8000

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:M_NUMBER2&gt;=:M_NUMBER1 AND :M_NUMBER1&lt;=:M_NUMBER1))    3 - access("EMPNO"=:M_NUMBER1)        filter(("EMPNO"&gt;=:M_NUMBER1 AND "EMPNO"&lt;=:M_NUMBER2)) Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,8], "DEPTNO"[NUMBER,22]    2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,8], "DEPTNO"[NUMBER,22]    3 - "EMPLOYEES".ROWID[ROWID,10], "EMPNO"[NUMBER,22] 59 rows selected. Elapsed: 00:00:00.36 12:53:00 scott@TESTORA&gt; select * from table(
12:54:06   2    dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'outline peeked_binds ALLSTATS LAST'));
Enter value for m_sql_id: cjgnx825610ky
Enter value for m_child_no: 0
old   2:        dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'outline peeked_binds ALLSTATS LAST'))
new   2:        dbms_xplan.display_cursor('cjgnx825610ky',0,'outline peeked_binds ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  cjgnx825610ky, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ empno, ename, deptno from
employees where empno= :m_number1 and empno between :m_number1 and
:m_number2

Plan hash value: 370102239

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  FILTER                      |           |      1 |        |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | E_PK      |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPNO"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 7369
   2 - :1 (NUMBER, Primary=1)
   3 - (NUMBER): 8000

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:M_NUMBER2&gt;=:M_NUMBER1 AND :M_NUMBER1&lt;=:M_NUMBER1))    3 - access("EMPNO"=:M_NUMBER1)        filter(("EMPNO"&gt;=:M_NUMBER1 AND "EMPNO"&lt;=:M_NUMBER2)) 45 rows selected. Elapsed: 00:00:00.15 13:03:45 scott@TESTORA&gt; select * from table(
13:03:46   2    dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'bytes cost outline peeked_binds ALLSTATS LAST'));
Enter value for m_sql_id: cjgnx825610ky
Enter value for m_child_no: 0
old   2:        dbms_xplan.display_cursor('&amp;m_sql_id',&amp;m_child_no,'bytes cost outline peeked_binds ALLSTATS LAST'))
new   2:        dbms_xplan.display_cursor('cjgnx825610ky',0,'bytes cost outline peeked_binds ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  cjgnx825610ky, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ empno, ename, deptno from
employees where empno= :m_number1 and empno between :m_number1 and
:m_number2

Plan hash value: 370102239

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |       |     1 (100)|      1 |00:00:00.01 |       2 |
|*  1 |  FILTER                      |           |      1 |        |       |            |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |      1 |      1 |    13 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | E_PK      |      1 |      1 |       |     0   (0)|      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPNO"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 7369
   2 - :1 (NUMBER, Primary=1)
   3 - (NUMBER): 8000

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:M_NUMBER2&gt;=:M_NUMBER1 AND :M_NUMBER1&lt;=:M_NUMBER1))    3 - access("EMPNO"=:M_NUMBER1)        filter(("EMPNO"&gt;=:M_NUMBER1 AND "EMPNO"&lt;=:M_NUMBER2))


45 rows selected.

Elapsed: 00:00:00.17
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s