实验分以下几种情况:
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 03.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
1374 consistent gets
21 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------------------------------------------------------------------
********************************************************************************
select *
from
tab where b='5'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.13 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.00 0.18 39 1371 0 9991
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.00 0.32 39 1371 0 9991
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
9991 TABLE ACCESS BY INDEX ROWID TAB
9991 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9991 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
9991 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************
select *
from
tab where b='3'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TAB
1 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************
第二种情况:
SQL> Analyze table tab compute statistics for table for columns b size 10;
表已分析。
SQL> set timing on;
SQL> set autotrace on;
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 02.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=9991 Bytes=59
946)
1 0 TABLE ACCESS (FULL) OF 'TAB' (Cost=4 Card=9991 Bytes=59946
)
Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
694 consistent gets
0 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1 Byte
s=6)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---------------------------------------------------------------------------------------------------
********************************************************************************
select *
from
tab where b='5'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.16 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.00 0.05 21 688 0 9991
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.01 0.21 21 688 0 9991
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
9991 TABLE ACCESS FULL TAB
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9991 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAB'
********************************************************************************
select *
from
tab where b='3'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 2 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 2 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TAB
1 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************
以上两种情况下,cursor_sharing为exact,两种方式下得到的执行计划一致:在字段数据分布不均匀的情况下,有直方图可得到预期的执行计划;无直方图,oracle无法判断数据分布情况,无论检索何值都走了索引。
--------------------------------------------------------------------------------------------------------------
第三种情况:
SQL> Analyze table tab compute statistics for table for columns b size 1;
表已分析。
SQL> set timing on;
SQL> set autotrace on;
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 03.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
1377 consistent gets
21 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---------------------------------------------------------------------------------------------------------
********************************************************************************
select *
from
tab where b=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.12 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 670 0.00 0.14 39 1375 0 9992
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 674 0.03 0.26 39 1375 0 9992
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
9991 TABLE ACCESS BY INDEX ROWID TAB
9991 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9991 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
9991 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************
第三种情况下,两种方式得到的执行计划也一致。
------------------------------------------------------------------------------------------------------------
第四种情况:
SQL> Analyze table tab compute statistics for table for columns b size 10;
表已分析。
SQL> set timing on;
SQL> set autotrace on;
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 03.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=9991 Bytes=59
946)
1 0 TABLE ACCESS (FULL) OF 'TAB' (Cost=4 Card=9991 Bytes=59946
)
Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
1377 consistent gets
21 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1 Byte
s=6)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.00
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1 Byte
s=6)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
308 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 03.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=9991 Bytes=59
946)
1 0 TABLE ACCESS (FULL) OF 'TAB' (Cost=4 Card=9991 Bytes=59946
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1371 consistent gets
0 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
这种方式下,无论先执行哪条语句,都能得到预期的执行计划;
-------------------------------------------------------------------------------------------------------------
********************************************************************************
select *
from
tab where b=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.10 0 83 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 1340 0.00 0.13 39 2750 0 19984
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1348 0.00 0.24 39 2833 0 19984
Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
9991 TABLE ACCESS BY INDEX ROWID TAB
9991 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9991 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
9991 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************
alter system flush shared_pool
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
这种方式下,无论先执行哪条语句,都是一样的
********************************************************************************
综上,第四种情况下,追踪客户会话得到的执行计划与直接运行语句得到的执行计划出现了不一致的现象。直接运行语句得到的执行计划只是理论上的。
从内存中直接查询执行计划,结果显示和跟踪会话得到的执行计划一致,都走索引。
1.SELECT sql_text, address, hash_value FROM v$sql
WHERE sql_text like '%TAG%';
2. SELECT operation, options, object_name, cost FROM v$sql_plan
WHERE address = :ADDRESS AND hash_value = :HASHVALUE;
看来,直接运行语句得到的执行计划不一定是真正的执行计划,只是比较理想的执行计划。理论上的。要想获得真正的执行计划,可以追踪客户会话或是直接利用视图查询。
但是,实验结果又与理论不一致。不知道在真正的绑定变量情况下是怎样的。
cursor_sharing为force情况下的实验就到这吧。下面实验similar,自己库中是这种情况,但经常出现7445错误,不知道是不是这个参数的原因。
-------------------------------------------------------------------------------------------------------------
第五种情况:(cursor_sharing为similar,实验字段上无直方图;)
在这种情况下,直接运行语句得到的执行计划是否准确??
SQL> Analyze table tab compute statistics for table for columns b size 1;
表已分析。
SQL> select * from tab where b='5';
已选择9991行。
已用时间: 00: 00: 03.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1371 consistent gets
0 physical reads
0 redo size
170751 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> select * from tab where b='3';
A B
---------- ----------
3 3
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1000 Bytes=60
00)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=4 Card=1000 B
ytes=6000)
2 1 INDEX (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE) (Cost=2 Ca
rd=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------------------------------------------------------------------------------------------
********************************************************************************
select *
from
tab where b=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.04 0.12 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 670 0.00 0.10 39 1375 0 9992
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 674 0.04 0.23 39 1375 0 9992
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (SLECC_ZFT)
Rows Row Source Operation
------- ---------------------------------------------------
9991 TABLE ACCESS BY INDEX ROWID TAB
9991 INDEX RANGE SCAN IX_TAB_B (object id 29809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9991 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB'
9991 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_TAB_B' (NON-UNIQUE)
********************************************************************************