一. 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/230160/viewspace-674871/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/230160/viewspace-674871/