Production environment direct path read with log file sync waits for event problem handling

1. AWR report for the first 7 days of 2018-09-26 (Oracle usage is 4,022.34/6,179.76/24=2.71% during this period)

The most notable problem is the log file sync wait event, which is accompanied by the log file parallel write wait event, which is also relatively high, initially judging this database operating system I/O usage is high, and through the Windows Task Manager to view the IO at this time The average read byte for the Oracle process in Performance Monitor for 100k/s-2m/s is 4m/s (contains network) the Write section only 10-30k/s disk utilization above 50%

Portal : Wait Event "Log file Sync"

2.2018-10-09 23:00 Database Server restart Operation 3.2018-10-10 9:00 get 2018-10-10 00:00 2018-10-10 a report (Oracle usage is 866.78/480.61/during this period 24=7.51%)

At this time

At this point, the TOP5 wait event remains log file Sync high, but the direct path read wait event appears through the Windows Task Manager to see that at this time IO is 80m/s-120m/s between the Oracle processes in the Performance Monitor flat Read-only byte 4m/s (contains network) write section only 10-30k/s disk utilization at 100%

Portal: Wait Event "direct path read"

Analysis of 1> problems

1.1) in general, block blocks (that is, Oracle's smallest storage unit) are always buffered by the background server process to buffer cache before being fetched by the server process. But for some large tables, buffering them to buffer cache is bound to extrude many other objects in the buffer cache, i.e. ageing out. To avoid this, Oracle produces direct path read, which does not need to buffer to the cache, but is fetched directly from the disk by the server process.

1.2) by looking at the segment by direct physical reads to get which objects direct path read Wait high

1.3) Find out that the Aud$ object physical reading accounted for 98.84% of the entire physical I/O resources, we then view the Table object aud$ in the database (find data found this object is the Oracle audit function of the underlying view, records all users of Oracle operations information)

1.4) Then we'll look at what's going on on the aud$ object causing this serious wait (view SQL order by reads)

1.5) The analysis finds that the logical read and the physical read ratio exception are all D15CDR0ZT3VTP get the details of this SQL, it is not difficult to find that this SQL is the Aud$ object

SELECT To_char (Current_timestamp at time ZONE ' GMT ',
' Yyyy-mm-dd HH24:MI:SS TZD ') as Curr_timestamp,
COUNT (username) as Failed_count
From Sys.dba_audit_session
WHERE ReturnCode! = 0
and To_char (timestamp, ' yyyy-mm-dd HH24:MI:SS ') >=
To_char (Current_timestamp-to_dsinterval (' 0 0:30:00 '),
' Yyyy-mm-dd HH24:MI:SS ')

1.6) In this SQL Wehere condition has "! =", ">=" and other restrictions, it is likely to cause a full table scan, and then query the Aud$ object's data volume, this object total 342812m/1024=334g (this table has 300 million + bar data)

SELECT *
From (SELECT segment_name, SUM (BYTES)/1024/1024 MB
From Dba_segments
GROUP by Segment_name
ORDER by 2 DESC)
WHERE segment_name= ' aud$ '

1.7) Confirm that this audit data is useless and can be cleaned periodically (TRUNCATE TABLE sys.aud$) (Operation time 2018-10-11 10:21) truncate the disk I/O from 100m+/s to 1m-/s

1. The auditing capabilities of Oracle 11gr2 are turned on by default, but due to the fact that all accounts are logged in and out by default, the amount of data in the audit log is very large.

2. Can cancel login and logout audit to make audit log data grow slower

sql> Noaudit Connect;

1.8)

Get the 2018-10-11 11 to 13 awr report Top 5 Wait event, we found that the direct path read wait event has disappeared, and the log file sync wait event has dropped significantly indicates that the log file sync wait event is caused by the previous full table Scan causes I/O tension caused by

This figure waits for a higher and Disk file operations I/O wait event, so let's look at this wait event

Portal: Wait Event "Disk file operations I/O"

At this point, the log file sync wait event is due to the hardware performance is kept at 9ms wait, and then this record the library log file sync, Disk file operations I/O, log file parallel write wait event time distribution table, convenient late Compare to see if performance improves log file sync

Select event, Wait_time_milli,wait_count, wait_count/(select sum (wait_count) from V$event_histogram where event = ' log fi Le Sync ') from V$event_histogram where event = ' log file sync ';

Log file Parallel write

Select event, Wait_time_milli,wait_count, wait_count/(select sum (wait_count) from V$event_histogram where event = ' log fi Le parallel write ') from V$event_histogram where event = ' log file parallel write ';

Disk file operations I/O

Select event, Wait_time_milli,wait_count, wait_count/(select sum (wait_count) from V$event_histogram where event = ' Disk f Ile Operations I/O ') from V$event_histogram where event = ' Disk file operations I/O ';

Direct Path Read

Select event, Wait_time_milli,wait_count, wait_count/(select sum (wait_count) from V$event_histogram where event = ' Direc T path read ') from V$event_histogram where event = ' direct path read ';

Via

81222523

49935205

Https://www.linuxidc.com/Linux/2015-09/122732.htm

Https://www.cnblogs.com/6yuhang/p/5923914.html

52051066

Production environment direct path read with log file sync waits for event problem handling