本来是想在11G下实验一下http://www.itpub.net/thread-1323480-1-1.html
没想到，发现了新的问题，新开帖求助

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 12 15:14:21 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn test/test
已连接。
SQL> create table t (id varchar2(10),text varchar2(4000));

表已创建。

SQL> insert into t select '1',object_name from dba_objects;

已创建63859行。

SQL> insert into t values( '2','aaa');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select id,count(*) from t group by id;

ID          COUNT(*)
---------- ----------
1             63859
2                1

SQL> create index t_idx on t(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns',cascade=>true);

PL/SQL 过程已成功完成。

SQL> var fid varchar2;
SQL> exec :fid := '2';

PL/SQL 过程已成功完成。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

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

SQL_ID  fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 97 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 31930 | 810K| 97 (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('11.2.0.1')
   DB_VERSION('11.2.0.1')
   OPT_PARAM('optimizer_dynamic_sampling' 4)
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
   END_OUTLINE_DATA
  */

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

1 - :FID (VARCHAR2(30), CSID=852): '2'

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

1 - filter("ID"=:FID)

Column Projection Information (identified by operation id):

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

1 - "ID"[VARCHAR2,10], "T"."TEXT"[VARCHAR2,4000]

已选择48行。

没有设置_optim_peek_user_binds，并且，绑定变量也进行了bind peeking，为什么还选择全表扫描？

做一下10053

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

SQL> alter session set events '10053 trace name context off';

会话已更改。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
#Rows: 63860  #Blks:  370  AvgRowLen:  26.00
Index Stats::
  Index: T_IDX  Col#: 1
LVLS: 1  #LB: 116  #DK: 2  LB/K: 58.00  DB/K: 136.00  CLUF: 273.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]

*** 2010-07-12 15:16:49.169
** Performing dynamic sampling initial checks. **
  Column (#1):
NewDensity:0.500000, OldDensity:0.000008 BktCnt:5458, PopBktCnt:5458, PopValCnt:1, NDV:2
  Using density: 0.500000 of col #1 as selectivity of unpopular value pred
** Dynamic sampling initial checks returning FALSE.
  Using density: 0.500000 of col #1 as selectivity of unpopular value pred
  Table: T  Alias: T
Card: Original: 63860.000000  Rounded: 31930  Computed: 31930.00  Non Adjusted: 31930.00
  Access Path: TableScan
Cost:  97.32  Resp: 97.32  Degree: 0
   Cost_io: 96.00  Cost_cpu: 16045533
   Resp_io: 96.00  Resp_cpu: 16045533
  Using density: 0.500000 of col #1 as selectivity of unpopular value pred
  Access Path: index (AllEqRange)
Index: T_IDX
resc_io: 195.00  resc_cpu: 13203631
ix_sel: 0.500000  ix_sel_with_filters: 0.500000
Cost: 196.08  Resp: 196.08  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 97.32  Degree: 1  Resp: 97.32  Card: 31930.00  Bytes: 0

***************************************

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation       | Name | Rows  | Bytes | Cost  | Time    |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT |       |    |    | 97 |          |
| 1 |  TABLE ACCESS FULL | T    | 31K |  811K | 97 |  00:00:02 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("ID"=:FID)

Content of other_xml column
===========================
  db_version    : 11.2.0.1
  parse_schema : TEST
  plan_hash    : 1601196873
  plan_hash_2 : 2498539100
Peeked Binds
============
  Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=852
char format=1
max length=32
value=2
  Outline Data:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
   DB_VERSION('11.2.0.1')
   OPT_PARAM('optimizer_dynamic_sampling' 4)
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
  */

去掉动态采样试试

SQL> alter session set optimizer_dynamic_sampling=0;

会话已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

SQL> alter session set events '10053 trace name context off';

会话已更改。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
#Rows: 63860  #Blks:  370  AvgRowLen:  26.00
Index Stats::
  Index: T_IDX  Col#: 1
LVLS: 1  #LB: 116  #DK: 2  LB/K: 58.00  DB/K: 136.00  CLUF: 273.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Column (#1):
NewDensity:0.500000, OldDensity:0.000008 BktCnt:5458, PopBktCnt:5458, PopValCnt:1, NDV:2
  Using density: 0.500000 of col #1 as selectivity of unpopular value pred
  Table: T  Alias: T
Card: Original: 63860.000000  Rounded: 31930  Computed: 31930.00  Non Adjusted: 31930.00
  Access Path: TableScan
Cost:  97.32  Resp: 97.32  Degree: 0
   Cost_io: 96.00  Cost_cpu: 16045533
   Resp_io: 96.00  Resp_cpu: 16045533
  Using density: 0.500000 of col #1 as selectivity of unpopular value pred
  Access Path: index (AllEqRange)
Index: T_IDX
resc_io: 195.00  resc_cpu: 13203631
ix_sel: 0.500000  ix_sel_with_filters: 0.500000
Cost: 196.08  Resp: 196.08  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 97.32  Degree: 1  Resp: 97.32  Card: 31930.00  Bytes: 0

***************************************

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation       | Name | Rows  | Bytes | Cost  | Time    |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT |       |    |    | 97 |          |
| 1 |  TABLE ACCESS FULL | T    | 31K |  811K | 97 |  00:00:02 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("ID"=:FID)

Content of other_xml column
===========================
  db_version    : 11.2.0.1
  parse_schema : TEST
  plan_hash    : 1601196873
  plan_hash_2 : 2498539100
Peeked Binds
============
  Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=852
char format=1
max length=32
value=2
  Outline Data:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
   DB_VERSION('11.2.0.1')
   OPT_PARAM('optimizer_dynamic_sampling' 0)
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
  */

不是动态采样的缘故，看看是否直方图的问题？

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns size 1',cascade=>true);

PL/SQL 过程已成功完成。

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

SQL> alter session set events '10053 trace name context off';

会话已更改。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
#Rows: 63860  #Blks:  370  AvgRowLen:  26.00
Index Stats::
  Index: T_IDX  Col#: 1
LVLS: 1  #LB: 116  #DK: 2  LB/K: 58.00  DB/K: 136.00  CLUF: 273.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Table: T  Alias: T
Card: Original: 63860.000000  Rounded: 31930  Computed: 31930.00  Non Adjusted: 31930.00
  Access Path: TableScan
Cost:  97.32  Resp: 97.32  Degree: 0
   Cost_io: 96.00  Cost_cpu: 16045533
   Resp_io: 96.00  Resp_cpu: 16045533
  Access Path: index (AllEqRange)
Index: T_IDX
resc_io: 195.00  resc_cpu: 13203631
ix_sel: 0.500000  ix_sel_with_filters: 0.500000
Cost: 196.08  Resp: 196.08  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 97.32  Degree: 1  Resp: 97.32  Card: 31930.00  Bytes: 0

***************************************

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation       | Name | Rows  | Bytes | Cost  | Time    |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT |       |    |    | 97 |          |
| 1 |  TABLE ACCESS FULL | T    | 31K |  811K | 97 |  00:00:02 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("ID"=:FID)

Content of other_xml column
===========================
  db_version    : 11.2.0.1
  parse_schema : TEST
  plan_hash    : 1601196873
  plan_hash_2 : 2498539100
Peeked Binds
============
  Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=852
char format=1
max length=32
value=2
  Outline Data:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
   DB_VERSION('11.2.0.1')
   OPT_PARAM('optimizer_dynamic_sampling' 0)
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
  */

ITPub博客

11.2 绑定变量执行计划怎么这样？求助！

最新文章