Detailed description of db file sequential read wait event, dbsequential

Source: Internet
Author: User

Detailed description of db file sequential read wait event, dbsequential

Db file sequential read (this article is translated by thomaswoo_dba. For more information, see the source)

The db file sequential read event has three parameters: file #, first block #, block count. in oracle 10 Gb, this wait event is under User I/O wait class. to handle db file sequential read events, you must firmly grasp the following three main ideas:
1) the block to be accessed by the oracle process cannot be obtained from SGA. Therefore, the oracle process will wait for the block to read SGA from I/O.
2) two important parameters, TIME_WAITED and AVERAGE_WAIT, are obtained by a single session.
3) db file sequential read, which has a large impact, is generally similar to application problems.

Common Causes, Diagnosis, and Actions

Db file sequential read wait event is initialized by SQL statements, mainly from index, rollback (or undo) segments, tables (Access Table through rowid ), control files and data file headers.

Accessing a table (index) always generates Physical I/o requirements. When a db file sequential read wait event occurs, it does not mean that the database has system problems, it is not a bad thing to use it in large numbers. it is worth noting that events like enqueue and latch free wait always cause the root cause of systemic problems. and they make single-block (read from a single block) difficult.

So under what circumstances can a db file sequential read wait event be considered a performance problem?
Under what circumstances can db file sequential read be considered as an excess of the system capacity, and how should the baseline be defined?
This is a complicated problem. without the guidance of industrial standards, we need to develop standard lines based on the database runtime environment.
For example, if we define the db file sequential read wait event for more than a certain period of time, it can be regarded as a performance problem, or we can use the most primitive method, that is, wait for the user to complain.

In the V $ SESSION_EVENT view, the high TIME_WAITED of db file sequential read is easy to find, because V $ SESSION_EVENT records the data since the instance was started, therefore, we can compare it with the previous TIME_WAITED. Of course, we can compare non-idle events of the same LOGON_TIME with the same session, which is also accurate. when the instance runs continuously for a long period of time (several days or several weeks), the accumulated value of TIME_WAITED will be very high, which of course cannot be said to be a performance problem.

Select a. sid,
A. event,
A. time_waited,
A. time_waited/c. sum_time_waited * 100 pct_wait_time,
Round (sysdate-B. logon_time) * 24) hours_connected
From v $ session_event a, v $ session B,
(Select sid, sum (time_waited) sum_time_waited
From v $ session_event
Where event not in (
'Null event ',
'Client message ',
'Kxfx: Execution Message Dequeue-Slave ',
'Px Deq: Execution Msg ',
'Kxfq: kxfqdeq-normal deqeue ',
'Px Deq: Table Q normal ',
'Wait for credit-send blocked ',
'Px Deq Credit: send blkd ',
'Wait for credit-need buffer to send ',
'Px Deq Credit: need buffer ',
'Wait for credit-free buffer ',
'Px Deq Credit: free buffer ',
'Parallel query dequeue wait ',
'Px Deque wait ',
'Parallel Query Idle Wait-slafs ',
'Px Idle wait ',
'Slave wait ',
'Dispatcher timer ',
'Virtual circuit status ',
'Pipe get ',
'Rdbms ipc message ',
'Rdbms ipc reply ',
'Pmon timer ',
'Smon timer ',
'Pl/SQL lock timer ',
'SQL * Net message from client ',
'Wmon goes to sleep ')
Having sum (time_waited)> 0 group by sid) c
Where a. sid = B. sid
And a. sid = c. sid
And a. time_waited> 0
And a. event = 'db file sequential read'
Order by hours_connected desc, pct_wait_time;
(This statement comes from the OWI material, but the results calculated by this SQL statement are not accurate, because the session sid is changed from time to time)

Reduce db file sequential read wait events. We can start from two aspects:
1) The first is, of course, optimizing SQL statements to reduce physical and logical reads.
2) The second is to reduce the average wait time in statistics (for example, to optimize the wait events with the maximum wait_time)
Note: in particular, it is the most effective to show the results to the customer, because the graphics give a more obvious sense.

For each item, it is very difficult to lock the SQL statement that causes long wait_time unless you use 10046 events or do an uninterrupted wait event program on your own. the current SQL statement is not necessarily the cause of wait_time. therefore, it is very difficult to solve the problem of waiting for events without historical data.

You can also query the V $ SQL view to obtain the average DISK_READS. Of course, we cannot think that this SQL statement belongs to a SESSION, so we can trace the session next time. Generally, we can locate the SQL statement, then optimize the SQL statement to reduce physical and logical reads.

Note: Apart from DISK_READS, oracle 10 Gb adds some exciting new columns for the V $ SQL and V $ SQLAREA views:
USER_IO_WAIT_TIME
DIRECT_WRITES
APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
PLSQL_EXEC_TIME
JAVA_EXEC_TIME
Of course, it is possible to locate the SQL statement through the highly accumulated USER_IO_WAIT_TIME, but the access speed of the V $ SQL and V $ SQLAREA views is slow.
In addition, the way to reduce the impact of db file sequential read wait events is to reduce AVERAGE_WAIT. The AVERAGE_WAIT column is the average wait time for a session to wait for the single block to be obtained from the hard disk, my level is insufficient)
This is the average time a session has to wait for a single block fetch from disk ). AVERAGE_TIME is the column in the V $ SESSION_EVENT view. in high-speed storage systems, the average single-block read capacity cannot exceed 10 ms (milliseconds, 1‰ seconds) or 1 CS (centiseconds, 1% seconds ). generally, the AVERAGE_TIME of a SAN (storage area network) is between 4 to 8 ms on average, because the SAN cache is large.
The larger the AVERAGE_TIME value, the more system resources consumed by single-block reading, that is, the process response time will be affected.

In another aspect, a lower AVERAGE_TIME value reflects that the process will wait for a single-block read for a short time. Of course
The priority of AVERAGE_TIME optimization is much higher than that of SQL optimization, because the effect of optimizing an SQL statement that occupies a large amount of resources is obvious and effective.

Note that db file sequential read does not always occupy the index image resources, but sometimes occupies the table/partition image resources. therefore, we need to convert the value of the P1/P2 parameter. Here we will use the view DBA_EXTENTS to get the object name.
However, DBA_EXTENTS is a complex view with extremely slow responses. to use a faster method, X $ and DBA_OBJECTS will be a better choice. because X $ BH does not occupy BUFFER_CACHE, accessing X $ BH will produce I/O, And the DBA-OBJECTS view does not include rollback and undo segments, therefore, if db file sequential read accesses these two objects, it cannot be parsed.
Query example:

select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
union
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.data_object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
order by 1;

SID OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
----- ------------------------- ------------------------- -----------------
12 DVC_TRX_REPOS DVC_TRX_REPOS_PR64 TABLE PARTITION
128 DVC_TRX_REPOS DVC_TRX_REPOS_PR61 TABLE PARTITION
154 ERROR_QUEUE ERROR_QUEUE_PR1 TABLE PARTITION
192 DVC_TRX_REPOS_1IX DVC_TRX_REPOS_20040416 INDEX PARTITION
194 P1=22 P2=30801 P3=1
322 P1=274 P2=142805 P3=1
336 HOLD_Q1_LIST_PK INDEX
Like object_type in this example, if it is a table, you need to optimize the SQL statement accordingly.
Sequential Reads Against Indexes
The main problem with db file sequential read is not access to the index, and excessive access to the wrong index. When the system
When the access path is changed, the slow-performance index may be accessed, resulting in waiting. Of course, if an SQL statement executes a large number of index reads
This may also be a performance problem. Therefore, it is better to analyze the SQL Execution Plan. When FULL TABLE SCAN is used, index is used.
There will be performance problems. There will also be problems with FIRST_ROWS and ALL_ROWS. Of course, from a large perspective, mixed OLTP and DSS will also produce problems
Suitable db file sequential read. There are also driving table problems. The performance of the wrong driver table is not good.
Remember, the purpose of all efforts should be the same, that is, to reduce the logical and physical I/OS
There is a method below:
1) analyze the SQL statements, find out the SQL logic, and check what the SQL statements want to obtain, optimize, or even rewrite the SQL statements.
2) Place the index on the fast disk, especially the RAID-5, because the slow disk leads to a high average time, but the I/O-optimized priority
It cannot be higher than the optimization of SQL code. Because SQL is faulty and the disk is faster, it is best to use OUTLINE to stabilize the stubborn plan, especially third-party software.
3) for the index table, it is best to arrange the data to reduce I/O. You can use DBA_INDEXS.CLUSTERING_FACTOR to check whether the index has reached
If yes, the number of all blocks in the table indicates that most columns are arranged. If not, the table is randomly arranged. You can reorganize the table to solve the problem.
4) check that the table has not created a new index recently, so that the SQL Execution Plan has changed. (The following statements can be found)
Check whether there is an invalid index.
select owner, 
substr(object_name,1,30) object_name,
object_type,
created
from dba_objects
where object_type in ('INDEX','INDEX PARTITION')
order by created;
5) OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING
(From the internet) Second, due to the different test environments, Tom's test results are in the default (100) environment,
It is already the same as when the value is 500, that is, the index is used for T2 full table scan while T1. In the Tom test, the value is reduced to 0,
The access path uses two indexes, but no index is used. Apart from system differences
(It may lead to the consistency of the access path when the default value is used). It only depends on the change trend. Obviously, the 10 Gb is more flexible.
The value 1-allows the CBO to overwrite all access paths. On the other hand, as Tom concluded,
The greater the value of OPTIMIZER_INDEX_COST_ADJ, the more inclined the optimizer to use full table scan. The smaller the value,
The optimizer tends to use indexes.
Again, we compare the differences under the same access path. The value ranges from 1 to 200 (1-50-100-200)
The cost calculated by the optimizer continues to grow, while from 1000 to 10000 remains unchanged.
This indicates that this parameter is related to the index I/O cost, but is irrelevant to full table scanning. This is not in conflict with what Tom said,
But it is more accurate.
Finally, we should see that although the price changes mentioned above,
There is no difference in the actual running performance under the same access path. Because the data volume is small, the above example may not explain this very well,
However, there is no reason for different performance for Oracle to use the same path for execution.
The value of OPTIMIZER_INDEX_CACHING is 0. The greater the value, the more tendence the system uses nested loops.
Find out what values the sessions are running with. Up to Oracle9i Database,
This information cocould only be obtained by tracing the sessions with the trace
Event 10053 at level 1 and examining the trace files. In Oracle Database 10g,
This is as simple as querying the V $ SES_OPTIMIZER_ENV view.
You can view the value of OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING corresponding to the SESSION through the 10053 event,
You can check the V $ SES_OPTIMIZER_ENV view in 10 Gb. The following is an example:
select * FROM V$SES_OPTIMIZER_ENV  WHERE NAME=LOWER('OPTIMIZER_INDEX_COST_ADJ') or 
name=lower('OPTIMIZER_INDEX_CACHING');
SID   ID    NAME                       ISDEFAULT  VALUE
--------------------------------------------------------
144 67 optimizer_index_caching YES 0
145 66 optimizer_index_cost_adj YES 100
145 67 optimizer_index_caching YES 0
Because oracle optimizer depends on the statistics of tables and indexes, make sure that the current statistics can represent existing data,
Incorrect statistics will produce inefficient execution plans for optimizer. Of course, statistics does not have to be updated every day, because in this case,
The execution plan will be updated every day, which will interfere with the analysis of performance problems.
System-Level Diagnosis
The V $ SYSTEM_EVENT view provides data for system-level diagnostics. In this view, AVERAGE_TIME and TIME_WAITED are associated with I/O-related events.
Remember that TIME_WAITED only records the records since the instance was started. After the instance has been running for a long time, db file sequential read
It is usually relatively high. Of course, the V $ SYSTEM_EVENT is frequently queried and sorted by TIME_WAITED, and obvious wait events can be found through mutual comparison.
When db file sequential read is not in top five, don't worry, because there may be more problems to discover
When db file sequential read is in top five, it can also indicate that the database has performed a large number of single-block reads.
It can be seen that the system-level diagnosis capability is very limited, but the event is always two sides, but here we can see the bottleneck on the system hardware.
This is not visible in the v $ session_wait event. When you want to upgrade the system, but your direct boss asks you to provide the System Bottleneck report,
The following is a good method:
Select a. event,
A. total_waits,
A. time_waited,
A. time_waited/a. total_waits average_wait. The average_wait here is very useful.
Sysdate-B. startup_time days_old
From v $ system_event a, v $ instance B
Order by a. time_waited;
When the average single-block read exceeds the threshold, check whether the I/O subsystem is optimized.
Of course, using the I/O Control Command (iostat, vmstat) of the operating system to monitor the hard disk can find the I/O bottleneck,
You can evaluate whether the I/O subsystems are balanced.
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
Dev8-0 3.93 17.03 34.66 54592552 111099454
Dev8-1 12.08 56.68 99.93 181659920 320286944
Dev8-2 23.38 194.11 189.93 622154550 608747464
Dev8-3 16.00 230.43 128.04 738570544 410383416
Dev8-4 4.73 59.89 80.98 191965458 259557752
Through the above example, we can see that the dev8-2, the block read and write of the dev8-3 is far more than other, so you can consider balancing I/O
In addition to system-level db file sequential read average wait from the V $ SYSTEM_EVENT view,
Oracle also provides another view v $ filestat to obtain single-block read statistics.
select a.file#, 
b.file_name,
a.singleblkrds,
a.singleblkrdtim,
a.singleblkrdtim/a.singleblkrds average_wait
from v$filestat a, dba_data_files b
where a.file# = b.file_id
and a.singleblkrds > 0
order by average_wait;


FILE# FILE_NAME SINGLEBLKRDS SINGLEBLKRDTIM AVERAGE_WAIT
----- ----------------------------- ------------ -------------- ------------
367 /dev/vgEMCp113/rPOM1P_4G_039 5578 427 .076550735
368 /dev/vgEMCp113/rPOM1P_4G_040 5025 416 .08278607
369 /dev/vgEMCp113/rPOM1P_4G_041 13793 1313 .095193214
370 /dev/vgEMCp113/rPOM1P_4G_042 6232 625 .100288832
371 /dev/vgEMCp113/rPOM1P_4G_043 4663 482 .103366931
372 /dev/vgEMCp108/rPOM1P_8G_011 164828 102798 .623668309
373 /dev/vgEMCp108/rPOM1P_8G_012 193071 125573 .65039804
374 /dev/vgEMCp108/rPOM1P_8G_013 184799 126720 .685717996
375 /dev/vgEMCp108/rPOM1P_8G_014 175565 125969 .717506337

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.