New Features of Oracle11g trigger DirectPathRead wait event case

New Features of Oracle11g trigger DirectPathRead wait event case

New Features of Oracle 11g trigger Direct Path Read wait event case

Recently, a production database of a company experienced performance problems. My colleague shared this case with me after handling the problem. Here I will sort out and share it with you.

Database environment:

Oracle 11.2.0.3 single instance, the operating system is Windows Server 2008 R2.

Fault symptom:

Slow database access and 100% I/O usage

Fault analysis:

1. High DB Time and high database pressure. In the sampling Time of nearly 60 Minutes, the DB Time is as high as 6983.24.

2. The Physical read and Logical read are of the same magnitude. It seems that such a large physical read is the reason why I/O reaches 100%.

3. The Buffer Nowait 100% in the SGA area seems to be in conflict with a large number of physical reads.

4. The frontend wait event ranks first in direct path read, accounting for 85.97% Of the total DB Time. Direct path reading is characterized by directly retrieving data from the storage without passing through the SGA buffer.

5. From top SQL, we can see that the most time-consuming SQL statements are all waiting for I/O, and these I/O come from the same large table CX_BAS_CUS_CON_SUMUP. The logical reads, physical reads, and direct path reads generated by the system all come from this large table. The problem is found! The execution plan shows that the SQL Execution Plan for accessing this large table is full table scan, and the table size is 488 MB.

Conclusion:

There is a new feature in Oracle 11G. To protect data already cached in the buffer cache, the size of the table is determined when a full table scan query occurs. If the table is too large, Direct Path Read is used to obtain data. Avoid the impact of a large amount of cold data on Buffer Cache. This problem is caused by this new feature. A large number of concurrent queries, CX_BAS_CUS_CON_SUMUP, and the Execution Plan adopts full table scan. This satisfies the new feature of 11 GB and bypasses SGA through direct path reading to obtain data from the storage. Because there is no SGA cache, each query requires a large number of physical reads from the storage, resulting in I/O 100%. Because the processing speed is slow and the CPU generates a large number of waiting queues, the DB Time is also very high.

How can we determine the size of the full table scan in the new feature?

The following is an implicit parameter: _ small_table_threshold

By default, this parameter is set to 2% of Buffer Cache. If the table is greater than 5 times _ small_table_threshold, this feature is triggered.

You can disable this feature by setting the 10949 event.

Alter session set events '10949 trace name context forever, level 1 ';

Solution:

The Application Team confirmed the data of the table and deleted a large amount of historical data, so that the full table is far below the value after _ small_table_threshold x 5. The SQL statement can be cached in the buffer cache again, and the physical read and I/O loads are all restored to a reasonable range.

We cannot optimize the SQL statement because we do not want to modify the application. Therefore, this problem is not fundamentally resolved. When the amount of data increases to the threshold, the problem will come back.

Finally, I would like to thank my colleagues for sharing this case with me.

Full text

This article permanently updates the link address: