ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 绑定变量、BIND PEEKING、histogram(柱状图)的使用

绑定变量、BIND PEEKING、histogram(柱状图)的使用

原创 Linux操作系统 作者:sxzhanghl 时间:2009-06-07 12:23:42 0 删除 编辑
在我们进行对oracle系统进行优化的时候,我们希望在数据中执行的语句尽量的少出现hard parse,尽量使用绑定变量,可以减少SQL分析,节约共享池的空间,减少CPU的使用,所以对于系统中未使用的绑定语句通常告诉开发人员尽量改成使用绑定变量,当where 条件上的字段分布均匀的时候,绑定变量可以达到比较好的效果, 但当该字段数据分布倾斜严重时,并在该字段上收集了histogram信息时会出现什么情况呢?下面将一一测试

一. create table testtb, 并插入98304条值为(1,’aaa’),3条值为(2,’bbb’);

[oracle@rac1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SQL> select count(1) from hr.testtb where id=1; 

  COUNT(1)
----------
     98304

SQL> select count(1) from hr.testtb where id=2;

  COUNT(1)
----------
         4

SQL> create index hr.testtb_ind on hr.testtb(id);    

Index created.

二.不使用绑定变量的情况:
1.当不进行分析时
SQL> Select  count(*) from hr.testtb where id=1;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  40c2k892vm3hy, child number 0
-------------------------------------
Select  count(*) from hr.testtb where id=1

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------
::走的全表扫描,是正确计划

SQL> select count(*) from hr.testtb where id =2;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  450qy7mmbsq0j, child number 0
-------------------------------------
select count(*) from hr.testtb where id =2

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
执行计划走的是索引,也是正确的执行计划。

2.分析但不生成柱状图信息

SQL> alter system flush shared_pool;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100);

PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZE   NUM_ROWS
------------ ----------
15-JUL-08         98308

SQL> select count(1) from hr.testtb where id=1;

  COUNT(1)
----------
     98304
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7gsvvztdv0vhk, child number 0
-------------------------------------
select count(1) from hr.testtb where id=1

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
分析后走的是正确的全表扫描的执行计划。

SQL> select count(1) from hr.testtb where id=2;

  COUNT(1)
----------
         4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b1wjkn7rw2tn6, child number 0
-------------------------------------
select count(1) from hr.testtb where id=2

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
执行计划走的是索引,也是正确的执行计划。


3.分析并生成柱状图信息
SQL> alter system flush shared_pool;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');
PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------
2008-07-15 22:50:00      98308

SQL> select * from dba_tab_histograms where table_name='TESTTB';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
----------------------------------------------------------------------------------------------------
HR                             TESTTB
ID
          98304              1

 

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
----------------------------------------------------------------------------------------------------
HR                             TESTTB
ID
          98308              2

SQL> select count(1) from hr.testtb where id=1;

  COUNT(1)
----------
     98304

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7gsvvztdv0vhk, child number 0
-------------------------------------
select count(1) from hr.testtb where id=1

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
依然走的是正确的全表扫描的执行计划。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  b1wjkn7rw2tn6, child number 0
-------------------------------------
select count(1) from hr.testtb where id=2

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
依然走的是索引正确的执行计划.

由此可以看出当表中数据倾斜很厉害的时候,并且未使用绑定变量时,无论是否存在histogram每次执行时,都走了正确的执行计划。

三.使用绑定变量的情况:
在oracle 9i之后引入了bind peeking,在第一次分析的时候,优化器会根据绑定变量来确定执行计划。BIND PEEKING只有当该SQL第一次执行的时候,并且在进行HARD PARSE的时候才进行,第二次调用该SQL,就不会再次进行BIND PEEKING,继续使用上一次产生的执行计划。以下为3种测试:

1. 当不进行分析时

SQL> exec dbms_stats.delete_table_stats(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE_COLUMNS=>TRUE,CASCADE_INDEXES=>TRUE);
PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------


SQL> var testid number;
SQL> exec :testid:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
全表扫描,对了

   2 - filter("ID"=:TESTID)

Note
-----
   - dynamic sampling used for this statement

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


23 rows selected.

SQL> exec :testid:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
不正确-----正确应该是走索引。

2.分析但不生成柱状图信息
SQL> exec dbms_stats.delete_table_stats(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE_COLUMNS=>TRUE,CASCADE_INDEXES=>TRUE);
PL/SQL procedure successfully completed.

SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'TESTTB',ESTIMATE_PERCENT=>100,CASCADE=>TRUE);       

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("ID"=:TESTID)


19 rows selected.

SQL>
SQL>  exec :testid:=1  

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

从上面可以看出: :testid=1时候本因走全表扫描的因为bind peeking的原因走了错误的执行计划----索引

3.分析并生成柱状图信息
SQL> alter system flush shared_pool;

System altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');


PL/SQL procedure successfully completed.

SQL> SQL>
SQL>  select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------
2008-07-15 23:27:49      98308

SQL> exec :testid:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("ID"=:TESTID)


19 rows selected.

SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
由上可见:生成了柱状图时,后面一次本应起索引的,还是走了全表扫描,可以看出在绑定变量时,柱状图是无效的。

另外如果SQL语句的条件使用了数据分布倾斜严重的列时,并且存在对各个值要使用绑定变量进行查询时,bind peeking可能导致产生不正确的执行计划.这时候,我们应该注意SQL语句的写法, 在这种情况下不要使用绑定变量,或者让其产生几种计划可供自动选择。例如:采用别名来生成另外一个执行计划:
SQL> var testid2 number
SQL> exec :testid2:=1;

PL/SQL procedure successfully completed.

SQL>  select count(*) from hr.testtb T where T.id=:testid2;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  76zpyb6bgab49, child number 0
-------------------------------------
 select count(*) from hr.testtb T where T.id=:testid2

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

这时生成了正确的执行计划:
需要大量数据时应用采用别名语句来查询:select count(*) from hr.testtb T where T.id=:testid2
需要小量数据时应用采用别名语句来查询:select count(*) from hr.testtb  where id=:testid
---如下:
SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb  where id=:testid;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  gmmf6n5fznrpv, child number 0
-------------------------------------
select count(*) from hr.testtb  where id=:testid

Plan hash value: 3027455797

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

三:在执行的会话中关闭bind peeking 时
我们可以通过以下语句查到
select ksppinm name, kspftctxvl value, ksppdesc description,kspftctxdf DefaultV
from x$ksppi x, x$ksppcv2 y
where (x.indx = y.indx)
and ksppinm like '%peek_%'
order by name;


_optim_peek_user_binds   的9i 以后default值为TRUE

Alter session set  “_optim_peek_user_binds”=false;
之后,优化器将按照字段的中值个数,来平均计算各个值数据的分布情况:
字段有20值时,oracle将认为每个值占5%, 如果此字段有索引的时候,将按索引查询。值少时将进行全表扫描。此进histogram信息也将无效。

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> var pp number;
SQL> exec :pp:=1;

PL/SQL procedure successfully completed.

SQL> exec :pp:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb TTBB where TTBB.id=:pp;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2psh0smda07hg, child number 0
-------------------------------------
select count(*) from hr.testtb TTBB where TTBB.id=:pp

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("TTBB"."ID"=:PP)


19 rows selected.

SQL> alter system flush shared_pool;    

System altered.

SQL> var id_value number;
SQL> exec :id_value:=1; 

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_value;

  COUNT(*)
----------
     98304

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'TESTTB',ESTIMATE_PERCENT=>100,CASCADE=>TRUE);

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');

PL/SQL procedure successfully completed.

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> var id_value number;
SQL> exec :id_value:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_value;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4kkcyg71r92bv, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_value

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |  TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("ID"=:ID_VALUE)


19 rows selected.

SQL> alter system flush shared_pool;

System altered.

SQL> var id_22 number;
SQL> exec :id_22:=1;   

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_22;

  COUNT(*)
----------
     98304

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7apbjnbmat3f7, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_22

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |  TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("ID"=:ID_22)


19 rows selected.

SQL> exec :id_22:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_22;

  COUNT(*)
----------
         4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7apbjnbmat3f7, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_22

Plan hash value: 1163950994

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB| 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

注册时间:2009-03-19

  • 博文量
    31
  • 访问量
    58895