物理读，硬解析，函数调用在查询中耗时的大概分比

第一次执行：

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是在硬解析的执行计划的产生过程中，软解析中没有， 且硬解析的时间， 要远远大于物理读的时间（当然这个和数据量有关系）。

物理读，硬解析，函数调用在查询中耗时的大概分比,布布扣,bubuko.com

物理读，硬解析，函数调用在查询中耗时的大概分比

原文：http://www.cnblogs.com/kangls/p/3586586.html