Database Administration (MOSC)

MOSC Banner

A table should be fully cached but Oracle still reads from disk

19.23 on AIX, standalone

We have a simple query that select from one table joining the table to itself and using full scans and hash join. Let's call it table R.

This is conventional table, not partitioned, DEGREE = 1, table size = 1,803 MB, column types are NUMBER, VARCHAR2, CHAR, DATE. No CLOB/BLOB or LONG columns. About 50 columns in total, avg row size = 157 bytes, 12 million rows.

To improve performance of the query we configured 10 GB KEEP cache, and assigned table R to KEEP cache (DBA_TABLES.BUFFER_POOL='KEEP'). We also updated DBA_TABLES.BLOCKS so it is less than 2% of KEEP cache size. Finally DBA_TABLES.CACHE was set to Y. There are no other tables assigned to KEEP cache.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center