如何在Oracle中了解跟踪文件的统计信息.例如CPU，经过时间，查询...等 [英] How to understand statistics of trace file in Oracle. Such as CPU, elapsed time, query...etc
问题描述
我正在学习Oracle中的查询优化，并且我知道跟踪文件将创建有关查询执行和查询的解释计划的统计信息.
I am learning query optimization in Oracle and I know that trace file will create statistic about the query execution and EXPLAIN Plan of the query.
在跟踪文件的底部，它是查询的EXPLAIN PLAN.我的第一个问题是，"time = 136437 us"部分是否显示查询执行步骤的持续时间? 我们"是什么意思?是时间单位吗?
At the bottom of the trace file, it is EXPLAIN PLAN of the query. My first question is , does the part "time = 136437 us" show the time duration for the steps of query execution? what does "us" mean ? Is it unit of time?
此外，任何人都可以解释一下诸如count，cpu，elapsed，disk和query之类的统计信息吗?我已经在Google上搜索并阅读了有关它们的Oracle文档，但我仍然无法理解.谁能更清楚地阐明这些统计信息的含义?
In addition, can anyone explain what statistics such as count, cpu, elapsed , disk and query mean? I google and read Oracle doc about them already but I still can not understand it. Can anyone clarify the meaning of those stats more clearly?
先谢谢了.我是新人，对我的英语感到抱歉.
Thanks in advance. I am new and sorry for my English.
推荐答案
Oracle数据库中最小的数据访问单元是一个块.不行.
The smallest unit of data access in Oracle Database is a block. Not a row.
每个块可以存储许多行.
Each block can store many rows.
数据库可以当前或一致模式访问块.
The database can access a block in current or consistent mode.
- 当前=，因为该块现在"存在.
- Consistent =，因为查询开始时已存在被阻止.
查询和当前列报告数据库以一致(查询)和当前模式访问一个块的次数.
The query and current columns report how many times the database accessed a block in consistent (query) and current mode.
访问一个块时，它可能已经在缓冲区高速缓存(内存)中.如果是这样，则不需要磁盘访问.如果不是，则必须进行物理读取(pr). 磁盘"列是对物理读取总数的计数.
When accessing a block it may already be in the buffer cache (memory). If so, no disk access is needed. If not, it has to do a physical read (pr). The disk column is a count of the total physical reads.
计划中每行的统计数据就是该操作的数字.加上所有子操作的总和.
The stats for each line in the plan are the figures for that operation. Plus the sum of all its child operations.
简单来说，数据库通过首先访问第一个孩子来处理计划.然后将行向上传递给父级.然后按顺序排列该父级的所有其他子级操作.子操作在显示中从其父缩进.
In simple terms, the database processes the plan by accessing the first child first. Then passes the rows up to the parent. Then all the other child ops of that parent in order. Child operations are indented from their parent in the display.
因此数据库按如下方式处理您的查询:
So the database processed your query like so:
- 从客户那里读取2,000行.这需要749个一致的块获取和363个磁盘读取(此行上的cr和pr值).这花费了10,100微秒.
- 从预订中读取112,458行.这完成了8,203次一致读取，磁盘读取为零.这花费了337,595微秒
- 使用哈希联接将这两个表联接在一起. CR，PR，PW(物理写入)和时间值是低于此值的操作的总和.加上此操作所做的任何工作.因此，哈希联接:
- 8,952-(749 + 8,203)=零次一致读取
- did 363-(363 + 0)=零物理读取
- 花费1,363,447-(10,100 +337595)= 1,015,752微秒来执行
- Read 2,000 rows from CUSTOMER. This required 749 consistent block gets and 363 disk reads (cr and pr values on this row). This took 10,100 microseconds.
- Read 112,458 rows from BOOKING. This did 8,203 consistent reads and zero disk reads. This took 337,595 microseconds
- Joined these two tables together using a hash join. The CR, PR, PW (physical writes) and time values are the sum of the operations below this. Plus whatever work this operation did. So the hash join:
- did 8,952 - ( 749 + 8,203 ) = zero consistent reads
- did 363 - ( 363 + 0 ) = zero physical reads
- took 1,363,447 - ( 10,100 + 337,595 ) = 1,015,752 microseconds to execute
请注意，CR&哈希联接的PR总数与查询行中的查询总数和磁盘总数是否匹配?
Notice that the CR & PR totals for the hash join match the query and disk totals in the fetch line?
计数列报告操作发生的次数.访存是对数据库的获取行的调用.因此，客户端将数据库调用了7499次.每次收到ceil(112,458/7,499)= 15行.
The count column reports the number of times that operation happened. A fetch is a call to the database to get rows. So the client called the database 7,499 times. Each time it received ceil( 112,458 / 7,499 ) = 15 rows.
CPU是服务器的处理器执行该步骤的总时间(以秒为单位).经过的时间是总的挂钟时间.这是CPU时间+任何额外的工作.例如磁盘读取，网络时间等.
CPU is the total time in seconds the server's processors were executing that step. Elapsed is the total wall clock time. This is the CPU time + any extra work. Such as disk reads, network time, etc.
这篇关于如何在Oracle中了解跟踪文件的统计信息.例如CPU，经过时间，查询...等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
