oracle分析REDO日志:http://www.cndba.cn/hbhe0316/article/116451http://www.cndba.cn/hbhe0316/article/116451

SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME, 
       DHSO.OBJECT_NAME, 
       SUM(DB_BLOCK_CHANGES_DELTA)                     BLOCK_CHANGED 
FROM   DBA_HIST_SEG_STAT DHSS, 
       DBA_HIST_SEG_STAT_OBJ DHSO, 
       DBA_HIST_SNAPSHOT DHS 
WHERE  DHS.SNAP_ID = DHSS.SNAP_ID 
       AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER 
       AND DHSS.OBJ# = DHSO.OBJ# 
       AND DHSS.DATAOBJ# = DHSO.DATAOBJ# 
       AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2023-02-21 13:23', 
                                       'YYYY-MM-DD HH24:MI') 
                                       AND 
           TO_DATE('2023-02-21 13:35', 'YYYY-MM-DD HH24:MI') 
GROUP  BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'), 
          DHSO.OBJECT_NAME 
HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0 
ORDER  BY SUM(DB_BLOCK_CHANGES_DELTA) DESC;

2.将查看的段对象代入如下SQLhttp://www.cndba.cn/hbhe0316/article/116451http://www.cndba.cn/hbhe0316/article/116451http://www.cndba.cn/hbhe0316/article/116451http://www.cndba.cn/hbhe0316/article/116451

SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY_MM_DD HH24') WHEN,
             DBMS_LOB.SUBSTR(SQL_TEXT,4000,1) SQL,
             DHSS.INSTANCE_NUMBER INST_ID,
             DHSS.SQL_ID,
             EXECUTIONS_DELTA EXEC_DELTA,
             ROWS_PROCESSED_DELTA ROWS_PROC_DELTA
FROM DBA_HIST_SQLSTAT DHSS,
         DBA_HIST_SNAPSHOT DHS,
         DBA_HIST_SQLTEXT DHST
WHERE UPPER(DHST.SQL_TEXT) LIKE '%xxxxxxx%'  --此处用具体的段对象替换
  AND LTRIM(UPPER(DHST.SQL_TEXT)) NOT LIKE 'SELECT%'
  AND DHSS.SNAP_ID=DHS.SNAP_ID
  AND DHSS.INSTANCE_NUMBER=DHS.INSTANCE_NUMBER
  AND DHSS.SQL_ID=DHST.SQL_ID
  AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2023-02-21 13:15','YYYY-MM-DD HH24:MI')
  AND TO_DATE('2023-02-21 13:35','YYYY-MM-DD HH24:MI');

3.或者收集AWR，查看 字段，查看TOP占用REDO日志比较高的SQL http://www.cndba.cn/hbhe0316/article/116451http://www.cndba.cn/hbhe0316/article/116451

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle