第一次执行：
drop table t purge;
create table t as select * from dba_objects;
set linesize 1000
set autotrace on
set timing on
select count(*) from t
SQL> create table t as select * from dba_objects;
表已创建。
SQL> set timing on;
SQL> set linesize 1000;
SQL> set autotrace on;
SQL> select count(*) from t;
COUNT(*)
----------
71976
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 72853 | 287 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1100 consistent gets
1025 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
SQL> alter system flush shared_pool;----将缓冲的执行计划清除，物理读保留
SQL>select count(*) from t
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.14
SQL> select count(*) from t;
COUNT(*)
----------
71976
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 72853 | 287 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
282 recursive calls
0 db block gets
1128 consistent gets
6 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
alter system flush buffer_cache; ----data buffer缓冲区掉，即只有物理读
select count(*) from t;
1 rows processed
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.45
SQL> select count(*) from t;
COUNT(*)
----------
71976
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 72853 | 287 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1030 consistent gets
1026 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以得出结论： 在一次sql查询中， recursive calls是在硬解析的执行计划的产生过程中，软解析中没有， 且硬解析的时间， 要远远大于物理读的时间（当然这个和数据量有关系）。
