CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Peter Penzov
Peter Penzov

Reputation: 1678

List all caches in Oracle

Is there a way to list all caches in Oracle with SQL query? I would like to list all available information about them in Oracle 12c Enterprise Edition.

Upvotes: 0

Views: 1422

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

Oracle has many different types of caches. There's no single query to display them all.

  1. Buffer Cache (SGA) This is the most important cache, it contains blocks of tables, indexes, and other objects. Generally, the most active and most recently used objects will be in the buffer cache. But the algorithms surrounding the size and persistence of objects in the buffer cache are complicated and partially undocumented.

  2. Shared Pool (SGA) This is a collection of multiple caches that contain SQL queries, stored procedures, data dictionary, etc.

  3. Session and Client Caches (PGA) Normally the Program Global Area is used to store query specific data, such as sorting and hashing areas. But it also contains program variables, which means it can sometimes be used as a cache.

  4. In-Memory Option An option to cache column-oriented data, and potentially the entire data.

  5. SQL Result Cache Caches the results of a specific SQL query. This cache is not often used and must be manually enabled.

  6. PL/SQL Function Result Cache Caches the results of a function. This cache is also unusual and must be manually enabled.

  7. Subquery Caching Some subqueries are automatically cached, improving query performance. For example, select (select count(*) from large_table) from another_large_table; may run much faster than you'd expect.

Upvotes: 4

Sudhish K
Sudhish K

Reputation: 119

SELECT * FROM v$parameter WHERE name LIKE 'result_cache%';

Not sure if i understood your question correctly. But this query will give you result_cache_mode, result_cache_max_size, result_cache_max_result, result_cache_remote_expiration. Hope it helps.

Thanks.

Upvotes: 1

Related Questions