缓慢的 Oracle 查询和 USER_IO_WAIT_TIME
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3336399/
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
Slow Oracle query and USER_IO_WAIT_TIME
提问by Marcus Leon
We have a slow query that has a low optimizer_cost value but a very high user_io_wait_time value. Does this just indicate that there is an I/O bottleneck?
我们有一个慢速查询，它的 optimizer_cost 值很低，但 user_io_wait_time 值很高。这是否只是表明存在 I/O 瓶颈？
Should we allocate more memory to Oracle? Get faster disks?
我们应该为 Oracle 分配更多内存吗？获得更快的磁盘？
Note: the stats were gathered by querying V$SQL
注意：统计信息是通过查询 V$SQL 收集的
回答by APC
Allocating more memory to Oracle will tendto make a database faster because enlarging the DB cache reduces I/O. We can get a feel for the potential improvement with the DB cache advisor.
为 Oracle 分配更多内存往往会使数据库更快，因为扩大 DB 缓存会减少 I/O。我们可以感受一下 DB 缓存顾问的潜在改进。
However, there is no guarantee that adding memory will improve your specific query. It is a blunderbuss approach, and whether it works in this case really depends on the nature the query.
但是，不能保证添加内存会改进您的特定查询。这是一种笨拙的方法，在这种情况下它是否有效实际上取决于查询的性质。
There are all sorts of reasons why the cost in the explain plan doesn't match the actual elapsed time. One could be that your statistics are stale, and the plan fits a much smaller table. Another is that you are suffering from skewed distrubution, so you have a plan which suits certain query values but not others. Or you might be suffering from an unhappy choice of bind variable peeking. Then again it could be down to hardware: bad disk, dodgy interconnects, poor SAN configuration.
解释计划中的成本与实际经过的时间不匹配的原因有很多。一个可能是您的统计数据陈旧，并且该计划适合一张小得多的表格。另一个原因是您正遭受偏斜分布的困扰，因此您有一个适合某些查询值但不适合其他查询值的计划。或者你可能会因为绑定变量偷看的不愉快选择而苦恼。然后又可能归结为硬件：坏磁盘、不可靠的互连、糟糕的 SAN 配置。
As Chekhov observed, poorly performing queries are all poorly performing in their own way. So, if you want some more detailed help you will have to supply more details about your specific scenario.
正如 Chekhov 所观察到的，性能不佳的查询都以自己的方式表现不佳。因此，如果您需要更详细的帮助，则必须提供有关特定场景的更多详细信息。
回答by O. Jones
Before you buy anything (before you make any capital expenditure), do some more digging. Explain planis like a weather forecast. It may collapse in the face of reality.
在你购买任何东西之前（在你进行任何资本支出之前），多做一些挖掘。 Explain plan就像天气预报一样。面对现实，它可能会崩溃。
There are a few things you can do, while the offending query is running.
当有问题的查询正在运行时，您可以做一些事情。
(1) log into the server machine and monitor the use of CPU cycles and disk io. In Linux the command sar -u 1 120will give you two minutes' worth of cpu %idle data. If you have no idle cpu, then your query is overusing computational cycles. Similarly sar -d 1 120will tell you whether your disk IO is saturated. Microsoft's perfmon and Windows Task Manager will tell you the same things if that's where your Oracle instance is hosted.
(1)登录服务器机器，监控CPU周期和磁盘io的使用情况。在 Linux 中，该命令sar -u 1 120将为您提供两分钟的 cpu %idle 数据。如果您没有空闲的 CPU，那么您的查询正在过度使用计算周期。同样sar -d 1 120会告诉你你的磁盘IO是否饱和。如果您的 Oracle 实例托管在那里，Microsoft 的 perfmon 和 Windows 任务管理器会告诉您同样的事情。
(2) use the Oracle Enterprise Manager console. Log in to the data base, look at the session list, and take a look at various cpu-intensive sessions. The enterprise manager can show you what queries are running, and what exact execution plans they are using. They also can tell you if they're doing something super time consuming like full-scanning a huge table.
(2) 使用Oracle Enterprise Manager 控制台。登录数据库，查看会话列表，查看各种cpu密集型会话。企业经理可以向您展示正在运行的查询以及它们正在使用的确切执行计划。他们还可以告诉你他们是否正在做一些非常耗时的事情，比如全扫描一张巨大的桌子。
Take a detailed look at the explain planoutput. It may show you a full table scan, or some other nasty performance hit, that suggests a solution like adding an index.
详细查看explain plan输出。它可能会向您显示全表扫描，或其他一些令人讨厌的性能损失，这表明了添加索引之类的解决方案。
