在我们进行对 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/317003/viewspace-591852/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/317003/viewspace-591852/