IO 异常读请求分析

来源：互联网发布：淘宝商品侵权如何处理编辑：程序博客网时间：2024/06/03 06:25

有一个备库，准备重建了，所以备库上介质恢复进程停了，主库的归档也没有传过来，所以备库上应该没有活动才对，可是通过zabbix监控发现IO 写请求非常

有规律：

在IO请求的时段，在系统上使用IOTOP获得了一下信息：

通过进程号（11646）可以获得下面的信息

08:55:04 sys@ORADB1>select * from v$process where spid=11646;ADDR                    PID SPID                     PNAME USERNAME                          SERIAL# TERMINAL---------------- ---------- ------------------------ ----- ------------------------------ ---------- ------------------------------PROGRAM------------------------------------------------TRACEID---------------------------------------------------------------------------------------------------------------------------------------TRACEFILE---------------------------------------------------------------------------------------------------------------------------------------B LATCHWAIT        LATCHSPIN        PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM- ---------------- ---------------- ------------ ------------- ---------------- -----------000000104A81F590         29 11646                          oracle                                 35 UNKNOWNoracle@hddb2.800best.com/u01/app/oracle/diag/rdbms/oradb2/hddb2/trace/hddb2_ora_11646.trc                                         1823672       2945328          1048576  1112600880

通过ADDR 获得sql_id 的信息：

08:57:28 sys@ORADB1>select SQL_ID from v$session where PADDR='000000104A81F590';SQL_ID-------------dyy8ajb4sbwu4

通过sql_id 获得 sql 详细信息

09:00:18 sys@ORADB1>select SQL_FULLTEXT from v$sql where sql_id='dyy8ajb4sbwu4';SQL_FULLTEXT---------------------------------------------------------------------------------------------------------------------------------------SELECT SCAN_TYPE,      SCAN_SITE_CODE,      SCAN_SITE,      LISTING_CODE,      SUM(BAG_COUNTER) AS BAG_COUNTER,      SUM(PIECE_COUNTER) AS PIECE_COUNTER      FROM (SELECT T.SCAN_TYPE,      QSS.SITE_CODE AS SCAN_SITE_CODE,      T.SCAN_SITE,      T.LISTING_CODE,      COUNT(DISTINCT T.BILL_CODE) AS BAG_COUNTER,      0 AS PIECE_COUNTER      FROM TAB_SCAN_Arrive T      LEFT JOIN Q9_SYS_SITE QSS ON QSS.SITE_NAME = T.SCAN_SITE      WHERE T.LISTING_CODE IS NOT NULL      AND (T.BILL_CODE LIKE '40%' OR T.BILL_CODE LIKE '42%')        AND SCAN_DATE >=  :param0  AND SCAN_DATE <=  :param1      GROUP BY T.SCAN_TYPE, QSS.SITE_CODE, T.SCAN_SITE, T.LISTING_CODE      UNION      SELECT T.SCAN_TYPE,      QSS.SITE_CODE AS SCAN_SITE_CODE,      T.SCAN_SITE,      T.LISTING_CODE,      0 AS BAG_COUNTER,      COUNT(DISTINCT T.BILL_CODE) AS PIECE_COUNTER      FROM TAB_SCAN_Arrive T      LEFT JOIN Q9_SYS_SITE QSS ON QSS.SITE_NAME = T.SCAN_SITE      WHERE T.LISTING_CODE IS NOT NULL      AND (T.BILL_CODE NOT LIKE '40%' AND T.BILL_CODE NOT LIKE '42%' AND      T.BILL_CODE NOT LIKE '43%')        AND SCAN_DATE >=  :param0  AND SCAN_DATE <=  :param1      GROUP BY T.SCAN_TYPE, QSS.SITE_CODE, T.SCAN_SITE, T.LISTING_CODE)      GROUP BY SCAN_TYPE, SCAN_SITE_CODE, SCAN_SITE, LISTING_CODE

一个查询语句怎么会引起 IO 写请求的呢？难道是排序太多了，要把结果放到临时表空间上？

0 0