Statspack 12-db file scattered read-DB file distributed read (repost)

This usually shows the wait related to the full table scan.
When the database performs a full table scan, the data will be distributed (scattered) into the buffer cache based on performance considerations. If the wait event is significant,
It may indicate that no index is created or an appropriate index is not created for some tables scanned by the full table. We may need to check whether these data tables have been entered.
Set correctly.

However, this wait event does not necessarily mean low performance. In some conditions, Oracle will take the initiative to use full table scanning to replace index scanning to improve performance.
It is related to the amount of data accessed. in CBO, Oracle performs more intelligent selection, and in RBO, Oracle tends to use indexes.

Because full table scan is placed on the cold end (cold end) of the LRU (least recently used, which is least recently applicable) list
Small data tables can be cached in the memory to avoid repeated reading.

When this wait event is significant, you can combine the V $ session_longops dynamic performance view for diagnosis.
For a row that lasts for more than 6 seconds, most of the operations may be full table scan (in any case, this part of information is worth noting ).

We use a case study to familiarize ourselves with this waiting event:

 

DB Name DB Id Instance Inst Num Release OPS Host ---------- ----------- ---------- -------- ---------- ---- ---------- K2 1999167370 k2 1 8.1.5.0.0 NO k2This is a database system of 8.1.5. With script enhancement, we can use statspack for database diagnosis on the database of 8.1.5.Snap Length Start Id End Id Start Time End Time (Minutes) -------- ---------------------- ------------------ ----------- 170 176 25-Feb-03 10:00:11 25-Feb-03 15:00:05 299.90 Cache Sizes ~~~~~~~~~~~ Db_block_buffers: 64000 db_block_size: 8192 log_buffer: 8388608 shared_pool_size: 157286400 .................. Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % TotalEvent Waits Time (cs) wt Time taken ------------ ----------------------- db file scattered read 16,842,920 3,490,719 43.32 latch free 844,272 3,270,073 40.58 buffer busy waits 114,421 933,136 11.58db file sequential read 2,067,910 117,750 1.46 enqueue 464 110,840 1.38 bytes -------------------------------------------------------------This is a typical low-performance system. Several important wait events appear in Top 5. Among them, the first three waits are extremely significant and need to be adjusted accordingly. During the 5-hour sampling interval, the total waiting time for db file scattered read is about 10 hours, which has become the main cause of the impact on system performance. After learning about this, we can further check the related SQL statements to see if any SQL statements are available.SQL ordered by Gets for DB: K2 Instance: k2 Snaps: 170-176 Gets % of Buffer Gets Executes per Exec Total Hash Value -------------- ------------ SQL statement limit 6,480,163 12 540,013.6 2.4 SELECT "PROCESS_REQ ". "WORK_ID", "PROCESS_REQ ". "STOCK_NO", "PROCESS_R 3,784,566 16 236,535.4 1.4 2932917818 SELECT * FROM FIND_LATER_WO order by note, ORDER_NO 1,200,976 3 400,325.3. 4 4122791109 SELECT "ITEM_STOCK ". "ITEM_NO", "ITEM ". "NOTE", "ITEM" 923,944 9 102,660.4. 3 2200071737 SELECT "ITEM_STOCK ". "ITEM_NO", "ITEM_STOCK ". "STOCK_NO", 921,301 3 307,100.3. 3 2218843294 SELECT "ITEM_STOCK ". "ITEM_NO", "ITEM ". "NOTE", "ITEM" 911,285 3 303,761.7. 3 1769130587 SELECT "LISTS ". "ITEM_NO", "LIS TS ". "SUB_ITEM", "LISTS" 831,439 2 415,719.5. 3 1349577999 SELECT "group_instances ". "ITEM_NO", "group_detail ". "PROCESS_ID", 802,918 1 802,918.0. 3 3613809507 SELECT "LISTS ". "ITEM_NO", "LISTS ". "SUB_ITEM", "ITEM ". 800,548 2 400,274.0. 3 2643788247 SELECT "ITEM_STOCK ". "ITEM_NO", "ITEM ". "NOTE", "ITEM" 666,085 2 333,042.5. 2 3391363608 SELECT "ITEM_STOCK ". "ITEM_NO", "ITEM_STOCK ". "STOCK_NO ",...........Note that the buffer gets caused by many of the above queries is very large, and we have a lot of reason to suspect that the index is faulty, or even the necessary index is missing. The preceding records are SQL fragments. Using Hash Value Combined with V $ SQL _text, we can obtain the complete SQL statement. In this diagnosis, I am going to query the V $ session_longops data table. The results of a group query are as follows:Target count (*) ---------------------------------------------------------------- ---------- SA. PPBT_GRAPHOBJTABLE 418SA. PPBT_PPBTOBJRELATTABLE 53We found that the full SQL table scan (combined with the opname in the V $ session_longops view) is mainly concentrated on the ppbt_graphobjtable and ppbt_ppbtobjrelattable data tables. Further research found that there were no indexes on the two data tables and there was a considerable amount of data:SQL> select count (*) from SA. PPBT_PPBTOBJRELATTABLE; COUNT (*) ---------- 1209017 SQL> select count (*) from SA. PPBT_GRAPHOBJTABLE; COUNT (*) ---------- 2445After creating an appropriate index, the system performance has been greatly improved!

 

From: http://www.eygle.com/statspack/statspack12.htm