Oracle 10g获取bind peek值

晏3101

在分析SQL执行效率时，在存在柱状图的情况下，绑定变量窥视（bind peeking）是不容忽视的因素，但往往缺乏必要的技术获知绑定变量具体值，从而无法为分析提供直接的证据，
所幸的是在Oracle 10g，Oracle提供了2种途径获取绑定变量具体值。
首先创建具有倾斜字段的表格，在其上创建索引，并进行表分析，以保证能进行绑定变量窥视
SQL> create table test1 as select * from dba_objects;

Table created.

SQL> select status,count(*) from test1 group by status;

STATUS    COUNT(*)
------- ----------
INVALID          9
VALID        51074

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

SQL> create index testidx on test1(status);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'TEST1',estimate_percent=>100,method_opt=>'FOR COLUMNS STATUS SIZE 2');

PL/SQL procedure successfully completed.
为保证测试干净环境，刷出shared pool内存

SQL> alter system flush shared_pool;

System altered.

（一）倾斜字段为INVALID时
SQL>  exec :v1 :='INVALID';

PL/SQL procedure successfully completed.

SQL> select count(*) from test1 where status=:v1;

  COUNT(*)
----------
         9
（1）采用dbms_xplan.display_cursor获取绑定变量
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7vnz1tjwm8zpv, child number 0
-------------------------------------
select count(*) from test1 where status=:v1

Plan hash value: 121734241

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |         |     1 |     7 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTIDX |     9 |    63 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / TEST1@SEL$1

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

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."STATUS"))
      END_OUTLINE_DATA
  */


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

   1 - :V1 (VARCHAR2(30), CSID=852): 'INVALID'

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

   2 - access("STATUS"=:V1)

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]


48 rows selected.
（2）采用dbms_sqltune.extract_binds获取绑定变量
SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE sql_id='7vnz1tjwm8zpv';

BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(2000)', 852, NULL, NULL, 2000,
'28-MAR-11', 'INVALID', ANYDATA()))

二、倾斜字段为VALID时，绑定变量窥视发生作用，可以看到依然为INVALID
SQL> exec :v1 :='VALID'

PL/SQL procedure successfully completed.

SQL> select count(*) from test1 where status=:v1;

  COUNT(*)
----------
     51074

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7vnz1tjwm8zpv, child number 0
-------------------------------------
select count(*) from test1 where status=:v1

Plan hash value: 121734241

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |         |     1 |     7 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTIDX |     9 |    63 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / TEST1@SEL$1

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

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."STATUS"))
      END_OUTLINE_DATA
  */


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

   1 - :V1 (VARCHAR2(30), CSID=852): 'INVALID'

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

   2 - access("STATUS"=:V1)

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]


48 rows selected.

SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE sql_id='7vnz1tjwm8zpv';

BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(2000)', 852, NULL, NULL, 2000,
'28-MAR-11', 'INVALID', ANYDATA()))

附：如果要确切知道绑定变量窥视具体发生时间，可以将nls_date_format置为'yyyy-mm-dd hh24:mi:ss'格式
dbms_sqltune.extract_binds(bind_data).在bind_data的类型定义中
  name                VARCHAR2(30),                     /* bind variable name */
  position            NUMBER,            /* position of bind in sql statement */
  dup_position        NUMBER,    /* if any, position of primary bind variable */
  datatype            NUMBER,                    /* datatype id for this bind */
  datatype_string     VARCHAR2(15),/* string representation of above datatype */
  character_sid       NUMBER,              /* character set id if bind is NLS */
  precision           NUMBER,                               /* bind precision */
  scale               NUMBER,                                   /* bind scale */
  max_length          NUMBER,                          /* maximum bind length */
  last_captured       DATE,      /* DATE when this bind variable was captured */
  value_string        VARCHAR2(4000),     /* bind value (text representation) */
  value_anydata       ANYDATA)         /* bind value (anydata representation) */

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE sql_id='7vnz1tjwm8zpv';

BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(2000)', 852, NULL, NULL, 2000,
'2011-03-28 14:51:09', 'INVALID', ANYDATA()))