How to clean the cache of SQL database

The method to clean the SQL database cache depends on the DBMS used: Microsoft SQL Server: Use the DBCC DROPCLEANBUFFERS command or close and reopen the database. MySQL: Use the FLUSH command or change the InnoDB buffer pool state variable. PostgreSQL: Use the VACUUM command or the ALTER SYSTEM command. Oracle: Use the ALTER SYSTEM command or the DBMS_CACHE package.

SQL database cache cleaning

How to clean SQL database cache?

The process of cleaning up SQL database caches depends on the specific database management system (DBMS) used. Here is a guide on how to clean caches for different DBMSs:

Microsoft SQL Server

1. Use DBCC DROPCLEANBUFFERS command:

    <code class="sql">DBCC DROPCLEANBUFFERS</code>

2. Close and reopen the database:

   1. Run SHUTDOWN WITH NOWAIT command to close the database.
   2. Wait for the database to be completely shut down.
   3. Use the START command to reopen the database.

MySQL

1. Use the FLUSH command:

    <code class="sql">FLUSH TABLES WITH READ LOCK;</code>

2. Use InnoDB buffer pool state variables:

   1. Run SHOW INNODB STATUS command.
   2. Find Buffer Pool section.
   3. Change Buffer Pool Hit Rate to 0 and then change back to 1.

PostgreSQL

1. Use the VACUUM command:

    <code class="sql">VACUUM FULL;</code>

2. Use the ALTER SYSTEM command:

    <code class="sql">ALTER SYSTEM RESET ALL;</code>

Oracle

1. Use the ALTER SYSTEM command:

    <code class="sql">ALTER SYSTEM FLUSH BUFFER_CACHE;</code>

2. Using DBMS_CACHE package:

   1. Create a process in PL/SQL:

       <code class="sql">CREATE OR REPLACE PROCEDURE flush_buffer_cache IS BEGIN DBMS_CACHE.FLUSH; END;</code>

   2. Perform the process:

       <code class="sql">CALL flush_buffer_cache;</code>

hint:

• Before performing the above, back up the database to ensure data security.
• Cleaning up the cache can cause performance degradation, especially when the database is in large quantities.
• Regularly cleaning caches can improve database performance and free up memory.

The above is the detailed content of How to clean the cache of SQL database. For more information, please follow other related articles on the PHP Chinese website!