Slow database execution Schedules cause I/O slow

Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
------------ ------------

Host Mem (MB): 16,338.5 16,338.5
SGA Use (MB): 3,072.0 3,072.0
PGA Use (MB): 805.1 861.7
% Host Mem used for sga+pga:23.73 24.08

Exception response:

Physical read (blocks): 35,368.4 3,067.3
Physical Write (Blocks): 6.8 0.6

Tota wait% DB
Event Waits time Avg (ms) time wait Class
------------------------------------------------- --------------------
Direct Path read 912,622 306.336 79.2 User I/O
read by other session 119,841 51.5 430 13.3 U Ser I/O
log file sync 41,849 9467 226 2.4 Commit
db file scattered read 15,466 6459 418 1.7 User I/o
Enq:tx-ro W Lock contention 2 5208 2.6E+06 1.3 Applicatio
DB CPU 3868 1.0
DB file sequential read 6,447 1635 254.4 User i/odisk file Operations I/O 691 534. 774.1 User I/O
Control file sequential Read 377 167.445.0 System I/O
log file switch (private Stran 5 39.3 7851. 0 Configurat

Physical Reads Elapsed
Reads executions per Exec%total time (s)%cpu%io SQL Id
----------- ----------- ---------- ------ ---------- ------ ------ -------------
1.23682E+08 7,632 16,205.7 97.7 306,686.8 1.0 98.9 ak7k07x5y8q12
SELECT this_.id as id49_0_, This_. LICENCE as licence49_0_, This_. TYPE as Type49_
0_, This_. Road_type as road4_49_0_, This_. Speed as speed49_0_, This_. STARTTIME A
S starttime49_0_, This_. ENDTIME as endtime49_0_ from vi_externalwarning This_ WH
ERE This_. ENDTIME =:p 0

I. Directpath Reads description
In previous versions of Oracle 11g, if full table scanning was performed on large tables, the wait event was: DB file scattered read; In 11g, if a full table scan is performed on a large table, the wait event is: direct path read; , the large table full table scan is the PGA area where the data blocks are read directly into the session. (Refer to the following example for a specific view method).
In 11g, large table full table scan data block without the SGA and directly into the PGA, which will cause each large table full table scan, physical reading is very large, and in 10g, because the full table scan of the data block in the SGA already exists, so when performing a full table scan, its physical reading is 0.
However, this is mainly the progress of Oracle in the optimization strategy, that is, assuming large table frequent full table scan this phenomenon, in the production library is not too much, by directly reading the data into the PGA, thus reducing the cachebuffer of the busy Exchange degree, improve the efficiency of cachebuffer.

Elapsed Elapsed Time
Time (s) executions per Exec (s)%total%cpu%io SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
306,686.8 7,632 40.18 79.3 1.0 98.9 ak7k07x5y8q12
SELECT this_.id as id49_0_, This_. LICENCE as licence49_0_, This_. TYPE as Type49_
0_, This_. Road_type as road4_49_0_, This_. Speed as speed49_0_, This_. STARTTIME A
S starttime49_0_, This_. ENDTIME as endtime49_0_ from vi_externalwarning This_ WH
ERE This_. ENDTIME =:p 0

SELECT * FROM table (Dbms_xplan. Display_cursor (' ak7k07x5y8q12 ', 0));
This SQL uses a full table scan, please optimize

Plan_table_output
--------------------------------------------------------------------------------
sql_id ak7k07x5y8q12, child number 0
-------------------------------------
SELECT this_.id as id49_0_, This_. LICENCE as licence49_0_, This_. TYPE
As type49_0_, This_. Road_type as road4_49_0_, This_. Speed AS
Speed49_0_, This_. STARTTIME as starttime49_0_, This_. ENDTIME as
endtime49_0_ from vi_externalwarning this_ WHERE this_. ENDTIME =:p 0

Plan Hash value:3931375654

--------------------------------------------------------------------------------
-----------------------

Plan_table_output
--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |
Cost (%CPU) | Time |

--------------------------------------------------------------------------------
-----------------------

| 0 | SELECT STATEMENT | | | |
11048 (100) | |

| 1 | VIEW | vi_externalwarning | 4 | 468 |

Plan_table_output
--------------------------------------------------------------------------------
11048 (1) | 00:02:13 |

| 2 | Union-all | | | |
| |

|* 3 | FILTER | | | |
| |

|* 4 | TABLE ACCESS full| mg_external_speed_warning | 1 | 55 |
3 (0) | 00:00:01 |

Plan_table_output
--------------------------------------------------------------------------------
|* 5 | FILTER | | | |
| |

|* 6 | TABLE ACCESS full| mg_external_restricted_warning | 1 | 88 |
2 (0) | 00:00:01 |

|* 7 | FILTER | | | |
| |

|* 8 | TABLE ACCESS full| mg_external_idle_warning | 1 | 49 |
6631 (1) | 00:01:20 |

Plan_table_output
--------------------------------------------------------------------------------

|* 9 | FILTER | | | |
| |

|* 10 | TABLE ACCESS full| mg_external_longtimext_warning | 1 | 49 |
4412 (1) | 00:00:53 |

--------------------------------------------------------------------------------
-----------------------

Mostly I/O throughput is slow

Direction 1:
Please OA check 6.101 IO, whether normal

Direction 2: Business Check ak7k07x5y8q12 is normal, this SQL consumes a lot of I/O

Physical Reads Elapsed
Reads executions per Exec%total time (s)%cpu%io SQL Id
----------- ----------- ---------- ------ ---------- ------ ------ -------------
1.23682E+08 7,632 16,205.7 97.7 306,686.8 1.0 98.9 ak7k07x5y8q12
SELECT this_.id as id49_0_, This_. LICENCE as licence49_0_, This_. TYPE as Type49_
0_, This_. Road_type as road4_49_0_, This_. Speed as speed49_0_, This_. STARTTIME A
S starttime49_0_, This_. ENDTIME as endtime49_0_ from vi_externalwarning This_ WH
ERE This_. ENDTIME =:p 0

Normal response:

Physical read (blocks): 14,991.1 99.8
Physical Write (Blocks): 21.4 0.1

Top Foreground Events by total wait time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota wait% DB
Event Waits Time Avg (ms) time Wait Class
---------------------------------------------------------------------
DB CP U 2768 35.5
Direct path read 193,541 2075 One 26.6 user I/O
db file scattered read 500,821 1465 3 18.8 user I/O
Re AD by other session 423,225 1165 3 14.9 User I/O
log file Sync 542,810 329.1 4.2 Commit
DB file sequential read 106 , 779 157. 1 2.0 User I/O
sql*net message to client 7,250,622 27.9 0.4 Network
Control file sequential read 771 5 6.1 System I/O
Disk file operations I/O 1,434 2.8 2.0 User I/o
sql*net more data to client 25,644 1.2 0.0 Network

CPU CPU per Elapsed
Time (s) executions Exec (s)%total time (s)%cpu%io SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
4,683.6 11,456 0.41 52.7 4,764.1 98.3.0 ak7k07x5y8q12
SELECT this_.id as id49_0_, This_. LICENCE as licence49_0_, This_. TYPE as Type49_
0_, This_. Road_type as road4_49_0_, This_. Speed as speed49_0_, This_. STARTTIME A
S starttime49_0_, This_. ENDTIME as endtime49_0_ from vi_externalwarning This_ WH
ERE This_. ENDTIME =:p 0

Slow database execution Schedules cause I/O slow