楼主补充：
以下是我的一些查询结果select name,value from v$sysstat where name like 'parse%';
NAME
VALUE
---------------------------------------------------------------- ----------
parse time cpu
245140
parse time elapsed
747530
parse count (total)
176055425
parse count (hard)
2188
parse count (failures)
133这样一算，命中率挺高的，应该不是shared pool的问题吧？
SQL> select count(*) from x$bh where state=0;
COUNT(*)
----------
0
标记为free的buffer cache数量为1，有时为0，没有free buffer了，对影响oracle运行应该很大吧？
SQL> SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
2FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
3 WHERE P1.name = 'db block gets'
4 AND P2.name = 'consistent gets'
5 AND P3.name = 'physical reads'
6;
(P1.VALUE+P2.VALUE-P3.VALUE)/(P1.VALUE+P2.VALUE)
------------------------------------------------
.742522047
这个应该失算buffer cache命中率的，只有74.3%。
SQL> SELECT event, sum(decode(wait_time,0,1,0)) "Curr", sum(decode(wait_time,0,0,1)) "Prev", count(*)"Total" FROM v$session_wait GROUP BY event ORDER BY count(*);
EVENT
Curr PrevTotal
---------------------------------------------------------------- ---------- ---------- ----------
SQL*Net message to client
0
1
1
buffer busy waits
1
0
1
db file parallel write
0
1
1
enqueue
1
0
1
pmon timer
1
0
1
smon timer
1
0
1
rdbms ipc message
4
0
4
free buffer waits
8
0
8
SQL*Net message from client
12
0 12
9 rows selected.
这条好像是查找等待时间的，free buffer waits为8，不算大，但我不知对oracle运行（比如插入数据，查询数据的影响大不大。）的影响大不大？
如果是buffer cache的问题，那怎么处理？
我只知道alter system set events = 'immediate trace name flush_cache';
这样强行刷新buffer cache应该不行吧，我看到我的：
SQL> SELECT component,current_size,min_size FROM v$sga_dynamic_components;
COMPONENT
CURRENT_SIZE MIN_SIZE
---------------------------------------------------------------- ------------ ----------
shared pool
134217728134217728
large pool
83886080 83886080
buffer cache
1056964608 1056964608
这样一刷缓存不就全没了？
有没有只清空部分buffer cache的命令？ |
