SQL 在 Oracle 中，“缓冲区获取”实际上指的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/295009/
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
In Oracle what does 'Buffer Gets' actually refer to?
提问by rustyshelf
I'm dealing with an Oracle DBA at the moment, who has sent me some profiling he's done. One of the terms in his report is 'Buffer Gets', any idea what this actually means? My guess is bytes retrieved from a buffer, but I have no idea really. Here is some sample output:
我目前正在与一名 Oracle DBA 打交道，他向我发送了一些他所做的分析。他的报告中的一个术语是“缓冲区获取”，您知道这实际上意味着什么吗？我的猜测是从缓冲区检索的字节，但我真的不知道。这是一些示例输出：
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
137,948,100 31,495 4,380.0 98.4 6980.57 6873.46 4212400674
Module: JDBC Thin Client
SELECT fieldOne, fieldTwo, fieldThree, fieldFour, fieldFive FROM TableExample
WHERE fieldOne = 'example'
It would also be handy to know what 'Gets per Exec' means, as I guess they are related? I'm a programmer, but not a DBA.
知道“Gets per Exec”是什么意思也很方便，因为我猜它们是相关的？我是程序员，但不是 DBA。
回答by WW.
Oracle storage is arranged into blocks of a given size (e.g. 8k). Tables and indexes are made up of a series of blocks on the disk. When these blocks are in memory they occupy a buffer.
Oracle 存储被安排成给定大小（例如 8k）的块。表和索引由磁盘上的一系列块组成。当这些块在内存中时，它们会占用一个缓冲区。
When Oracle requires a block it does a buffer get. First it checks to see if it already has the block it needs in memory. If so, the in-memory version is used. If it does not have the block in memory then it will read it from disk into memory.
当 Oracle 需要一个块时，它会执行一个缓冲区 get。首先它检查它是否已经在内存中拥有它需要的块。如果是，则使用内存版本。如果它在内存中没有该块，那么它将从磁盘读取到内存中。
So a buffer get represents the number of times Oracle had to access a block. The reads could have been satisfied either from memory (the buffers) or have resulted in a physical IO.
因此，缓冲区 get 表示 Oracle 必须访问块的次数。读取可能已经从内存（缓冲区）得到满足，也可能导致物理 IO。
Since physical IO is so expensive (compared to memory or CPU) one approach to tuning is to concentrate on reduction in buffer gets which is assumed will flow on to reduce physical IO.
由于物理 IO 如此昂贵（与内存或 CPU 相比），一种调整方法是专注于减少缓冲区获取，假设将继续减少物理 IO。
回答by Dave Costa
Just to answer one part of your question that WW didn't:
只是为了回答 WW 没有回答的问题的一部分：
"Gets per Exec" is simply the gets divided by the number of executions of the statement. You might have a statement that is very efficient but executed many times; this would have a high number of total buffer gets but a low gets per exec ratio.
“Gets per Exec”只是获取数除以语句的执行次数。您可能有一个非常高效但执行多次的语句；这将有大量的总缓冲区获取，但每个 exec 的获取比率较低。
回答by LordMax
I want just to make a little consideration.
我只想稍微考虑一下。
In first Dave Costa is right, Buffer gets for exec is what really matters.
首先，Dave Costa 是对的，Buffer 获得 exec 才是真正重要的。
With that data we can't say if they are too much or not 'cause you don't say how many records your query extract
有了这些数据，我们不能说它们是否太多，因为你没有说你的查询提取了多少条记录
If you extract 4000 records maybe 4,380 are ok If you extract only 1 record they're probably too much
如果您提取 4000 条记录，可能 4,380 条就可以了 如果您只提取 1 条记录，它们可能太多了
So, how many records?
那么，有多少记录？
And more, have you a sql plan to see? Have you an index on fieldOne?
更重要的是，您有计划查看 sql 吗？你有 fieldOne 的索引吗？
The right index is the first "secret" for tuning a query.
正确的索引是调整查询的第一个“秘密”。
Just my 2 cents
只是我的 2 美分
