用户名:   自动登录 找回密码
密   码:    * 注册




发表新帖 回复这个主题  [ 2 篇帖子 ] 
作者 内容
 文章标题 : oracle如何清除SQL缓存
帖子发表于 : 2012-03-27 8:56 
离线
头像

注册: 2011-05-01 9:15
帖子: 120
打开sql跟踪的功能:
SQL> set autotrace traceonly;

执行下面的SQL看计划:
SQL> select * from awen.ob_bak where owner='AWEN';

已选择2304行。


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

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 774K| 68M| 27184 (2)| 00:00:33 |

| 1 | TABLE ACCESS BY INDEX ROWID| OB_BAK | 774K| 68M| 27184 (2)| 00:00:33 |

|* 2 | INDEX RANGE SCAN | OB_INDEX | 774K| | 1860 (4)| 00:00:03 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='AWEN')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1466 consistent gets
1258 physical reads
0 redo size
220074 bytes sent via SQL*Net to client
2068 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2304 rows processed

有1258 次physical reads,再次运行同样的SQL,看执行计划:
SQL> select * from awen.ob_bak where owner='AWEN';

已选择2304行。


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

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 774K| 68M| 27184 (2)| 00:00:33 |

| 1 | TABLE ACCESS BY INDEX ROWID| OB_BAK | 774K| 68M| 27184 (2)| 00:00:33 |

|* 2 | INDEX RANGE SCAN | OB_INDEX | 774K| | 1860 (4)| 00:00:03 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='AWEN')


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1466 consistent gets
0 physical reads
0 redo size
220074 bytes sent via SQL*Net to client
2068 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2304 rows processed

0个 physical reads,说明现在用的是缓存里面保留的数据,

清除缓存:
SQL> alter system flush buffer_cache;
系统已更改。

再次执行SQL,看执行计划:
SQL> select * from awen.ob_bak where owner='AWEN';

已选择2304行。


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

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 774K| 68M| 27184 (2)| 00:00:33 |

| 1 | TABLE ACCESS BY INDEX ROWID| OB_BAK | 774K| 68M| 27184 (2)| 00:00:33 |

|* 2 | INDEX RANGE SCAN | OB_INDEX | 774K| | 1860 (4)| 00:00:03 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='AWEN')


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1466 consistent gets
1587 physical reads
0 redo size
220074 bytes sent via SQL*Net to client
2068 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2304 rows processed

又会有 physical reads 出现。


页首
 用户资料  
 
 文章标题 : Re: oracle如何清除SQL缓存
帖子发表于 : 2015-09-08 12:49 
离线
头像

注册: 2015-09-08 12:18
帖子: 2
I'm lucky to have owned it soon.

_________________
แทงบอลออนไลน์


页首
 用户资料  
 
显示帖子 :  排序  
发表新帖 回复这个主题  [ 2 篇帖子 ] 


在线用户

注册用户: 没有注册用户


查找:
前往 :  
cron
Powered by OraSql © 2011, 2012, oracle_awen