Secret of oracle logical IO: Index Scan
How to calculate the io of a query? This question is the most concern of dba&developer when we do performance tuning. We know the IO is affected by many factors in different operations, e.g. , high water mark, index height, extents number of a segment, cr UNDO application and so on. However, for the details of the factors in IO calculation formula, are secrets for us. Even work with 10200 event, we can not count up to the final total CRs, because this trace ignored the meta data reads. By monitoring the buffer cache, we can just guess it also reads the segment header for times, and repeat reads for number of extents. From the fixed table sys.x$kcbwh, sys.x$kcbsw, we can find the buffer cache statisitcs data, which are directly related to the io behaviors. By comparing the statistic data before&after the query, we may peek some internal information for the logic IO. However, this view is a db level statistics data, it will be affected by the backgroup processes, we may not get the truth from it. So, is there any other way to find out the secret? Yes! As we know, for each data reads, it will be pined in the cache, means if we can trace the pin behaviors, we will know IO behaviors. Read which block? For what purpose? How many times a block will be red? The switch is the undocumented parameter, "_trace_pin_time".
|
最新文章