硬解析和物理读取与软解析和逻辑读取

SYS@ENMOEDU> alter system flush buffer_cache;
System altered.

清空共享池的缓存

SYS@ENMOEDU> alter system flush shared_pool;
System altered.

打开统计信息

SYS@ENMOEDU> set autotrace traceonly statistics;

执行查询语句

SYS@ENMOEDU> select * from hr.employees where department_id=60;
Statistics
----------------------------------------------------------
         197  recursive calls
          0  db block gets
        225  consistent gets
          27  physical reads
          0  redo size
       1447  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

关闭统计信息

SYS@ENMOEDU> set autotrace off;

清空buffer _cache 的数据影响
SYS@ENMOEDU> alter system flush buffer_cache;

System altered.

SYS@ENMOEDU> set autotrace traceonly statistics
SYS@ENMOEDU> select * from hr.employees where department_id=60;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          16  physical reads
          0  redo size
       1447  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SYS@ENMOEDU> select * from hr.employees where department_id=60;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
           4  consistent gets
           0  physical reads
          0  redo size
       1447  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

从上面的统计信息中看到，软解析的执行任务所耗占的资源最少 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30176559/viewspace-1670886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30176559/viewspace-1670886/