Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: July 12, 2016 - 1:32 pm UTC

Last updated: July 13, 2016 - 1:16 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,
I would like to list all caches in Oracle. Are there any usefull SQL queries which can help me to see some usesull performance data from Oracle's caches?

BR,
Peter

and Chris said...

You can read about the caches in the Memory Architecture section of the concepts guide:

http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT007

But basically you have:

- The buffer cache. This stores data blocks read from disk. You can split this into separate pools if you wish
- The Smart Flash Cache: If you have flash disks, you can configure this as a second level cache
- The shared pool. This contains the:
- library cache: stores shared SQL & PL/SQL structures
- data dictionary cache: this holds metadata about the database
- result cache: holds the results of the SQL result cache and PL/SQL function cache

"Are there any usefull SQL queries which can help me to see some usesull performance data"

What precisely are you looking for here? What is "useful performance data" for you?

You can find details about the buffer pools in v$buffer_pool_statistics:

http://docs.oracle.com/database/121/REFRN/GUID-1E914318-C648-4A72-9AA9-711F8CAA92FB.htm#REFRN30033

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 12, 2016 - 6:12 pm UTC

Thank you for the reply!

Can you show me please SQL queries how to list the information into these tables?
Chris Saxon
July 13, 2016 - 1:16 am UTC

Some of the views you could consider

v$bh
v$sga
v$sgastat
v$sqlstats
v$active_session_history
v$librarycache

Cheers,
Connor