oracle 如何在oracle的内存中保留一张表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28039508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to preserve a table in memory in oracle?
提问by jalal rasooly
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?
我知道oracle会自动将经常访问的数据保存在内存中。我很好奇有什么方法可以手动将表保存在内存中以获得更高的性能？
回答by Lalit Kumar B
Yes, you could certainly do that. You need to pin the table in the KEEP POOLcache in DB cache.
是的，你当然可以这样做。您需要将表固定在 DB 缓存中的KEEP POOL缓存中。
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.
顺便说一句，Oracle 11g 及更高版本，您可以查看RESULT CACHE。这是非常有用的。
Have a look at this AskTom link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676
看看这个 AskTom 链接https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676
回答by FlyingGuy
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.
如果您需要如此高的检索性能，那么可以考虑使用像 Times 10 这样的内存数据库。
Think about what you are asking the DB to do. You are asking the DB to dedicate n amount of cachememory 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.
想想你要数据库做什么。您要求 DB 将 n 个缓存内存专用于单个表并无限期地保留在那里。在繁忙的数据库中，这只会将性能降低到数据库无用的程度。假设你有一个数据库，里面有几百个表，其中一些很小，一些很大，有些非常大，并加入了一些 PK 和索引。
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.
出现一个查询，要求输入 100K 行数据，每行 1 KB，索引是一个 100 字节长的字符串。DB 将分配足够的内存来加载索引，然后开始从磁盘中抓取 8K 数据块并将它们放入缓存中。
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.
如果您要求它在 ram 中永久存储几 GB 的数据，您将很快耗尽内存，除非您有一台配备 512 gig ram 的非常昂贵的机器，并且您将开始访问交换文件和那么在这一点上，你的表现就是敬酒。
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.
如果您在查询中遇到性能问题，请运行解释计划并学习如何使用它来发现瓶颈。我有一台 24 核的机器，有 48 个内存，但我有包含数十亿行数据的表。我密切关注我的缓存命中和执行计划。
Also consider materialized views.
还要考虑物化视图。
