CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
jalal rasooly
jalal rasooly

Reputation: 705

how to preserve a table in memory in oracle?

I know oracle automatically preserve frequently accessed data in memory. I'm curious is any way to keep a table in memory manually for more performance?

Upvotes: 2

Views: 17164

Answers (2)

FlyingGuy
FlyingGuy

Reputation: 333

The short answer is no, and you don't want to.

If you need that high a level of retrieval performance, then consider using an in memory DB like Times 10.

Think about what you are asking the DB to do. You are asking the DB to dedicate n amount of cache memory to a single table and hold it there indefinitely. In a busy DB this will simply kill performance to the point of the DB being useless. Lets say you have a DB with a few hundred tables in it, some of them small, some large and some very large and throw in a few PK's and indexes.

A query comes along that asks for say 100K rows of data that are 1 Kbyte each and the index is a 100 byte long string. The DB will allocate sufficient memory to load up the index, and then start grabbing 8K chunks of data off the disk and putting those into cache.

If you ask it to store a few gigabytes of data, in ram, permanently, you will run out of memory in a big hurry unless you have a VERY expensive machine with 512 gigs of ram in it and you will start hitting the swap file and well at that point your performance is toast.

If you are having performance issues on queries run explain plan and learn how to use it to discover the bottle necks. I have a 24 core machine with 48 gigs of ram, but I have tables with billions of rows of data. I keep a close eye on my cache hits and execution plans.

Also consider materialized views.

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Yes, you could certainly do that. You need to pin the table in the KEEP POOL cache in DB cache.

For example,

ALTER TABLE table_name STORAGE (buffer_pool KEEP);

By the way, Oracle 11g and up, you can have a look at the RESULT CACHE. It is quite useful.

Have a look at this AskTom link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676

Upvotes: 4

Related Questions