ITPub博客

首页 > Linux操作系统 > Linux操作系统 > bind peeking

bind peeking

原创 Linux操作系统 作者:v_fantasy 时间:2009-05-25 11:26:02 0 删除 编辑

Create Table PreferredMeals
(EmpID Number(5),
Meal Varchar2(10)
)
/

Begin
For i in 1..74998 Loop
Insert Into PreferredMeals Values (i, 'Mansaf');
End Loop;
Insert Into PreferredMeals Values (74999, 'Kabab');
Insert Into PreferredMeals Values(75000, 'Pasta');
End; 
/

Create Index PrefMl_Indx
On
PreferredMeals(Meal)
/

Exec DBMS_Stats.Gather_Table_Stats(OwnName => 'zz', TabName => 'PreferredMeals', Method_Opt => 'For All Indexed Columns Size Auto', Cascade => True)
/

select column_name,histogram from DBA_TAB_COL_STATISTICS where table_name='PREFERREDMEALS';

EMPID|NONE
MEAL|FREQUENCY


Select Count(*)
From PreferredMeals
Where Meal = 'Kabab'
/

select * from table(dbms_xplan.display_cursor);

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

SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

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

-

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
|

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


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

-

|   0 | SELECT STATEMENT  |             |       |       |     1 (100)|
|

|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |
|

|*  2 |   INDEX RANGE SCAN| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:01

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

|

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

-


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

   2 - access("MEAL"=:BP)


已选择19行。
SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

------------------------------------------------------------------------------
-
| 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| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:0
|
------------------------------------------------------------------------------
-

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

   2 - access("MEAL"=:BP)


已选择19行。

 

刷新shared pool

alter system flush shared_pool;

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 73957 |   505K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Kabab'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 73957 |   505K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。

说明在存在柱状图的情况下,bind peeking是生效的。
我们直接对表进行非柱状图分析,再看看bind peeking生成的cursor是否依然生效;

Exec DBMS_Stats.Gather_Table_Stats(OwnName => 'zz', TabName => 'PreferredMeals', Method_Opt => 'For table', Cascade => True)

select column_name,histogram from DBA_TAB_COL_STATISTICS where table_name='PREFERREDMEALS'

EMPID|NONE
MEAL|NONE

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Kabab'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 73957 |   505K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 1
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 75000 |   512K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。

不过,这种情况好像不能说明,到底是bind peeking是否依然生效,因为这种情况下,如果直接没有柱状图的时候也是这样的执行计划,于是,我们还是利用前面一种情况来测试一下(index range scan的时候)

首先刷一下share pool

alter system flush shared_pool;

再做一下柱状图表分析

Exec DBMS_Stats.Gather_Table_Stats(OwnName => 'zz', TabName => 'PreferredMeals', Method_Opt => 'For all indexed columns size auto', Cascade => True)

执行sql

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Kabab'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

--------------------------------------------------------------------------------
-
| 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| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

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

   2 - access("MEAL"=:BP)


已选择19行。

再试试倾斜比较厉害的值

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

--------------------------------------------------------------------------------
-
| 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| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

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

   2 - access("MEAL"=:BP)


已选择19行。

ok,没问题,然后做下表分析

Exec DBMS_Stats.Gather_Table_Stats(OwnName => 'zz', TabName => 'PreferredMeals', Method_Opt => 'For table', Cascade => True)

继续执行sql

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Kabab'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

--------------------------------------------------------------------------------
-
| 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| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

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

   2 - access("MEAL"=:BP)


已选择19行。

再试一下倾斜大的值

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 123811481

--------------------------------------------------------------------------------
-
| 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| PREFML_INDX |     1 |     7 |     1   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

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

   2 - access("MEAL"=:BP)


已选择19行。

ok,可以看出,在柱状图表分析的基础上做普通表分析,以前保留的share pool的计划还是存在的,我们再刷一下share pool

alter system flush shared_pool;

然后再执行sql

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Kabab'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
         1

SQL> select *from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 37500 |   256K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。

SQL> alter system flush shared_pool;

系统已更改。

SQL> Var BP Varchar2(10)
SQL> Exec :BP := 'Mansaf'

PL/SQL 过程已成功完成。

SQL>
SQL> Select Count(*)
  2  From PreferredMeals
  3  Where Meal = :BP
  4  /

  COUNT(*)
----------
     74998

SQL> select *from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4knqcd9t85h23, child number 0
-------------------------------------
Select Count(*) From PreferredMeals Where Meal = :BP

Plan hash value: 3614369359

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| PREFML_INDX | 37500 |   256K|    45   (5)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - filter("MEAL"=:BP)


已选择19行。
发现在没有柱状图的时候计划的走的是INDEX FAST FULL SCAN,确实是正确的,可见如果要删除柱状图表分析转化为普通表分析,cursor并不会invalid

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-604127/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2008-10-07

  • 博文量
    98
  • 访问量
    181908