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! |