是什么导致 Oracle tkprof 文件中 CPU 时间和 Elapsed 时间之间存在差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5568855/
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
What causes the difference between CPU time and Elapsed time in Oracle tkprof files
提问by LauraB
When analysing Oracle tkprof trace files I have noticed that there is sometimes a big difference between the cpu time and the elapsed time, and I don't know what causes it.
在分析 Oracle tkprof 跟踪文件时，我注意到有时 cpu 时间和经过时间之间存在很大差异，我不知道是什么原因造成的。
For example:
例如：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 42.09 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 45 14.44 62.71 48664 505513 0 1871
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47 14.45 104.82 48664 505513 0 1871
The wait statistics are like this:
等待统计如下：
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 46 0.00 0.00
SQL*Net message from client 46 0.19 1.68
buffer busy waits 559 0.23 8.59
db file scattered read 5204 0.21 7.49
db file sequential read 4240 0.20 13.49
latch free 215 0.11 3.62
I'm a software developer (not a DBA) so I'm generally looking these trace files to find inefficient queries or to see if an index could be used to stop a full table scan and so on. For this purpose I tend to go on the cpu time. In most cases the elapsed time is very similar to the cpu time.
我是一名软件开发人员（不是 DBA），所以我通常会查看这些跟踪文件以查找低效查询或查看是否可以使用索引来停止全表扫描等。为此，我倾向于在 cpu 时间上继续。在大多数情况下，经过的时间与 CPU 时间非常相似。
I don't have access to the database that generated the trace file (it's from a client site) but I would like to understand what is going on so that I can make suggestions as to how to reduce the elapsed time.
我无权访问生成跟踪文件的数据库（它来自客户端站点），但我想了解发生了什么，以便我可以就如何减少经过的时间提出建议。
回答by Tony Andrews
The CPU time is the time your query actually needed; the rest is waiting for resources. On a busy server this could be largely due to waiting for CPU currently in use by other users; this doesn't show up in the wait stats.
CPU 时间是您的查询实际需要的时间；剩下的就是等待资源。在繁忙的服务器上，这可能主要是由于等待其他用户当前正在使用的 CPU；这不会显示在等待统计信息中。
回答by ik_zelf
How busy is the system, what is the architecture, how does the query look like? How is the sga sizing?
系统有多忙，架构是什么，查询是什么样的？sga的尺寸如何？
Most amazing in this sample is the parse time. That poor server gets some things thrown to it that does not look like a lot of fun ....
此示例中最令人惊奇的是解析时间。那个糟糕的服务器得到了一些看起来不太有趣的东西......
Normally, the elapsed time is the wall-clock time that it took to process the complete query. The cpu time, the time that the cpus were used. For your system I would go and try to find out why the parsing took so long. There is a good chance that it if you solve that, you also solve the fetch time. Ask for an addm report for the period in which the query ran and study that.Oracle? Database 2 Day + Performance Tuning Guide 11g Release 2 (11.2)is a good place to gain some understanding in addm reports.
通常，经过的时间是处理完整查询所用的挂钟时间。cpu 时间，cpu 被使用的时间。对于你的系统，我会去尝试找出为什么解析需要这么长时间。如果你解决了这个问题，很有可能你也解决了获取时间。要求提供查询运行期间的 addm 报告并研究该报告。甲骨文？Database 2 Day + Performance Tuning Guide 11g Release 2 (11.2)是了解 addm 报告的好地方。
回答by Dave Costa
As Tony said, one common explanation for unaccounted-for time in a trace is time spent waiting for the CPU. Another that I have experienced is time spent writing to the trace file itself, if something is causing that to occur slowly; but if this is the case you should see a big difference in clock time when running the query with or without trace.
正如托尼所说，跟踪中未考虑时间的一种常见解释是等待 CPU 所花费的时间。我遇到的另一个问题是写入跟踪文件本身所花费的时间，如果某些原因导致其缓慢发生；但如果是这种情况，您应该会在有或没有跟踪的情况下运行查询时看到时钟时间的巨大差异。
The parse time is huge. Parsing is generally CPU-bound whereas this shows no CPU time and lots of elapsed time. The fact that you have significant latch freewaits could be an indication of lots of parsing contention, but the time attributed to the wait is only about 1/10 of your elapsed parse time.
解析时间是巨大的。解析通常受 CPU 限制，而这显示没有 CPU 时间和大量经过的时间。您有大量latch free等待的事实可能表明存在大量解析争用，但归因于等待的时间仅为您已用解析时间的 1/10 左右。
So I would agree with Tony that heavy CPU contention is a likely problem in this case. A large amount of parsing may be contributing to this problem.
所以我同意托尼的观点，在这种情况下，严重的 CPU 争用可能是一个问题。大量解析可能会导致此问题。
