对与oracle解析的观察

1.比较硬解析：

SQL> create table t as select * from dba_objects;

表已创建。

已用时间: 00: 00: 00.92
SQL> set linesize 1000
SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from t;

COUNT(*)
----------
79691

已用时间: 00: 00: 00.24

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 69218 | 316 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1196 consistent gets
1129 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> --第2次执行
SQL>
SQL> --该命令只是为了先不考虑2次执行物理读减少带来的效果，只考虑减少解析
应
SQL> alter system flush buffer_cache;            这一次主要是为了去除数据缓存带来的比对比。

系统已更改。

已用时间: 00: 00: 01.61
SQL>
SQL> select count(*) from t;

COUNT(*)
----------
79691

已用时间: 00: 00: 00.17

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 69218 | 316 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1134 consistent gets
1130 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2.比较数据缓存：

SQL> create table t as select * from dba_objects;

表已创建。

已用时间: 00: 00: 01.68
SQL>
SQL> set linesize 1000
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> --第1次执行
SQL>
SQL> select count(*) from t;

COUNT(*)
----------
79691

已用时间: 00: 00: 00.54

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 69218 | 316 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1196 consistent gets
1129 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> --第2次执行
SQL> --该命令只是为了先不考虑解析的优化,单纯考虑第2次执行物理读减少带
应
SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.41
SQL> select count(*) from t;

COUNT(*)
----------
79691

已用时间: 00: 00: 00.15

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 69218 | 316 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
282 recursive calls
0 db block gets
1224 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed