Basic Oracle management: alter system flush "oracle cache"

The following three statements are used to refresh the Oracle cache. Here is a summary.

1)Alter system flush global context

Note:

For multi-tier architectures such as application servers and data block servers that communicate through the connection pool, the information about the connection pool is kept in SGA. This statement clears the connection information.

2)Alter system flush shared_pool

Make the library cache and data dictionary cachePreviousAll saved SQL Execution plans are cleared, but the recently executed entries cached in the shared SQL area or pl/SQL area are not cleared. Refreshing the shared pool can helpMerge shards(Small chunks) to release a few shared pool resources and temporarily solve the shared_pool fragmentation problem. However, this approach is generally not recommended. The reason is as follows:

· Flush Shared Pool will cause unused cursor to be cleared out of the Shared Pool. If these SQL statements need to be executed subsequently, the database will experience a lot of hard parsing, the system will experience severe CPU contention, and the database will have fierce Latch competition.
· If the application does not use Bound variables and a large number of similar SQL statements are not executed, the Flush Shared Pool may only provide a short improvement and the database will return to the original state soon.
· If the Shared Pool is large and the system is very busy, refreshing the Shared Pool may cause the system to be suspended. For systems like this, try to do it when the system is idle.

The following tests the effect of refreshing on Shared Pool fragments:

1. SQL>Select Count(*)FromX $ ksmsp;
3. COUNT(*)
4. ----------
5. 41637
7. SQL>AlterSystem flush shared_pool;
9. The system has been changed.
11. SQL>Select Count(*)FromX $ ksmsp;
13. COUNT(*)
14. ----------
15. 9276

3)Alter system flush buffer_cache

To minimize the impact of the cache on the test experiment, you need to manually refresh the buffer cache to urge oracle to re-Execute physical access (in statistics:Physical reads).

Test Environment

1. SQL>Select Count(*)FromTt;
3. COUNT(*)
4. ----------
5. 1614112
7. SQL> showUser;
8. USERIs"HR"
9. SQL>ExecDbms_stats.gather_table_stats ('Hr','TT');
11. The PL/SQL process is successfully completed.
13. SQL>SelectBlocks, empty_blocksFromDba_tablesWhereTable_name ='TT' AndOwner ='Hr';
15. BLOCKS EMPTY_BLOCKS
16. ----------------------
17. 22357 0
18. Table TT has a total of 22357 Blocks

Observe the state = 0 with x $ bh

1. SQL>Select Count(*)FromX $ bhWhereState = 0;
3. COUNT(*)
4. ----------
5. 0
7. SQL>AlterSystem flush buffer_cache;
9. The system has been changed.
11. SQL>Select Count(*)FromX $ bhWhereState = 0;
13. COUNT(*)
14. ----------
15. 40440

State = 0 indicates that the buffer state is free. After the cache is flush, all the buffers are marked as free.

Observe the impact of flush cache on queries:

1. SQL>SetAutotOn Statistics
2. SQL>Select Count(*)FromTt;
4. COUNT(*)
5. ----------
6. 1614112
9. Statistics
10. ----------------------------------------------------------
11. 0 recursive cballs
12. 0 db block gets
13. 22288 consistent gets
14. 22277 physical reads
15. 0 redoSize
16. 416 bytes sent via SQL * NetToClient
17. 385 bytes encoded ed via SQL * NetFromClient
18. 2 SQL * Net roundtripsTo/FromClient
19. 0 sorts (memory)
20. 0 sorts (disk)
21. 1RowsProcessed
23. SQL>/
25. COUNT(*)
26. ----------
27. 1614112
30. Statistics
31. ----------------------------------------------------------
32. 0 recursive cballs
33. 0 db block gets
34. 22288 consistent gets
35. 0 physical reads
36. 0 redoSize
37. 416 bytes sent via SQL * NetToClient
38. 385 bytes encoded ed via SQL * NetFromClient
39. 2 SQL * Net roundtripsTo/FromClient
40. 0 sorts (memory)
41. 0 sorts (disk)
42. 1RowsProcessed
44. SQL>AlterSystem flush buffer_cache;
46. The system has been changed.
48. SQL>Select Count(*)FromTt;
50. COUNT(*)
51. ----------
52. 1614112
55. Statistics
56. ----------------------------------------------------------
57. 0 recursive cballs
58. 0 db block gets
59. 22288 consistent gets
60. 22277 physical reads
61. 0 redoSize
62. 416 bytes sent via SQL * NetToClient
63. 385 bytes encoded ed via SQL * NetFromClient
64. 2 SQL * Net roundtripsTo/FromClient
65. 0 sorts (memory)
66. 0 sorts (disk)
67. 1RowsProcessed