AUTOTRACE的SQL不会进行绑定变量探测

带绑定变量的SQL用AUTOTRACE查询执行计划还真不靠谱，测试发现AUTOTRACE的SQL不会进行绑定变量探测。

SQL> DESC T

 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> SELECT STATUS,COUNT(1) FROM T GROUP BY STATUS;

STATUS    COUNT(1)
------- ----------
INVALID         23
VALID        49870

SQL> CREATE INDEX IDX_T_STATUS ON T(STATUS);

索引已创建。

SQL> SET LINESIZE 300
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM T WHERE STATUS='INVALID';

执行计划
----------------------------------------------------------
Plan hash value: 709710412

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    37 |  3441 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |    37 |  3441 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_STATUS |    37 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("STATUS"='INVALID')

SQL> SELECT * FROM T WHERE STATUS='VALID';

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49853 |  4527K|   166   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 49853 |  4527K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"='VALID')


由于STATUS列上的直方图的影响，STATUS列不同的值产生的执行计划不一样。
STATUS='INVALID'是走索引扫描的。
但是受到下面的SET AUTOT TRACEONLY EXP的影响，导致ORACLE没有进行榜单变量的探测。

SQL> VARIABLE STATUS VARCHAR2(30)
SQL> EXEC :STATUS:='INVALID';

SQL> set autot traceonly exp
SQL> select object_name from t where status=:status;  <-- 由于没进行绑定变量探测，执行计划是全表扫描

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24947 |   779K|   166   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 24947 |   779K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"=:STATUS)



PL/SQL 过程已成功完成。

SQL> select object_id from t where status=:status; <-- 这里可以进行绑定变量探测，执行计划是索引扫描

 OBJECT_ID
----------
      9339
      9347
      9357
      9363
      9367
      9373
      9379
      9383
      9387
      9391
      9417
      9419
      9423
      9427
      9431
      9455
      9457
      9467
      9469
     52548
     52554
     52578
     53802

已选择23行。

SQL> select sql_id,sql_text,bind_data from v$sql where sql_text like 'select%from t where status=:status';

SQL_ID        SQL_TEXT                                 BIND_DATA
------------- ---------------------------------------- --------------------
0zqrbjd93p9qu select object_name from t where status=:
              status

1hpyb0qrkpxb0 select object_id from t where status=:st BEDA0A200100512479E7
              atus                                     000101F0012003540749
                                                       4E56414C4944

可以看到SQL： select object_name from t where status=:status 根本没有进行绑定变量探测(BIND_DATA是NULL)

执行计划中也看不到绑定变量探测信息：

SQL> select * from table(dbms_xplan.display_cursor('0zqrbjd93p9qu',null,'advanced'));

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

SQL_ID  0zqrbjd93p9qu, child number 0
-------------------------------------
select object_name from t where status=:status

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   166 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 24947 |   779K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - SEL$1 / T@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("STATUS"=:STATUS)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,30]



已选择41行。


SQL> select * from table(dbms_xplan.display_cursor('1hpyb0qrkpxb0',null,'advanced'));

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

SQL_ID  1hpyb0qrkpxb0, child number 0
-------------------------------------
select object_id from t where status=:status

Plan hash value: 709710412

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |    37 |   444 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_STATUS |    37 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1

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

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

Peeked Binds (identified by position):  <--绑定变量探测信息
--------------------------------------

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

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

   2 - access("STATUS"=:STATUS)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_ID"[NUMBER,22]
   2 - "T".ROWID[ROWID,10]



已选择49行。



即便强制提示并行绑定变量探测也无效。

SQL> alter system flush shared_pool;

系统已更改。

SQL> print

STATUS
--------------------------------
INVALID

SQL> set autot traceonly exp
SQL> select /*+opt_param('_optim_peek_user_binds', 'true')*/ object_name from t where status=:status;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24947 |   779K|   166   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 24947 |   779K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"=:STATUS)

SQL> set autot off
SQL> select sql_id,sql_text,bind_data from v$sql where sql_text like 'select%from t where status=:status';

SQL_ID        SQL_TEXT                                 BIND_DATA
------------- ---------------------------------------- --------------------
d2y18fts207cr select /*+opt_param('_optim_peek_user_bi
              nds', 'true')*/ object_name from t where
               status=:status



SQL> select * from v$version;

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

上面的测试在10G环境下进行的。







在11G中的行为和10G一样

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select * from all_objects;

Table created.

SQL> select status,count(1) from t group by status;

STATUS    COUNT(1)
------- ----------
INVALID         38
VALID        72663

SQL> create index idx_t_status on t(status);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for columns status size 254',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot traceonly exp
SQL> set linesize 300 pagesize 300
SQL> select * from t where status='INVALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 709710412

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    39 |  3783 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |    39 |  3783 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_STATUS |    39 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("STATUS"='INVALID')

SQL> select * from t where status='VALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72656 |  6882K|   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 72656 |  6882K|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"='VALID')
      

SQL> variable status varchar2(30)
SQL> exec :status:='INVALID';

PL/SQL procedure successfully completed.

SQL> select object_name from t where status=:status;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36351 |   851K|   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 36351 |   851K|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"=:STATUS)

SQL> set autot off
SQL> select object_id from t where status=:status;

 OBJECT_ID
----------
     76939
     76940
     76846
     76847
     76848
     76850
     76856
     76858
     76875
     76876
     76882
     76901
     76923
     76924
     76925
     76927
     76928
     76929
     76930
     76931
     76932
     76933
     76934
     76935
     76936
     76803
     76813
     76812
     76815
     76820
     76821
     76823
     76824
     76825
     76828
     76832
     76833
     76835

38 rows selected.

SQL> set autot traceonly exp
SQL> select /*+opt_param('_optim_peek_user_binds', 'true')*/ object_name from t where status=:status;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36351 |   851K|   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 36351 |   851K|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"=:STATUS)

SQL> set autot off
SQL> select sql_id,sql_text,bind_data from v$sql where sql_text like 'select%from t where status=:status';

SQL_ID        SQL_TEXT                                 BIND_DATA
------------- ---------------------------------------- ----------------------------------------
d2y18fts207cr select /*+opt_param('_optim_peek_user_bi
              nds', 'true')*/ object_name from t where
               status=:status

0zqrbjd93p9qu select object_name from t where status=:
              status

1hpyb0qrkpxb0 select object_id from t where status=:st BEDA0B2001005123BE76000101F0012003540749
              atus                                     4E56414C4944