ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11.2 绑定变量执行计划怎么这样?求助!

11.2 绑定变量执行计划怎么这样?求助!

原创 Linux操作系统 作者:sundog315 时间:2010-07-12 16:32:11 0 删除 编辑
本来是想在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博客 ” ,链接:http://blog.itpub.net/19423/viewspace-667815/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-01-05

  • 博文量
    126
  • 访问量
    503897