自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

如何定位哪些SQL產(chǎn)生了大量的Redo日志

作者： 瀟湘隱者
運(yùn)維 數(shù)據(jù)庫(kù)運(yùn)維
在ORACLE數(shù)據(jù)庫(kù)的管理、維護(hù)過(guò)程中，偶爾會(huì)遇到歸檔日志暴增的情況,也就是說(shuō)一些SQL語(yǔ)句產(chǎn)生了大量的redo log，那么如何跟蹤、定位哪些SQL語(yǔ)句生成了大量的redo log日志呢?

[[373988]]

本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者 。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。   

 在ORACLE數(shù)據(jù)庫(kù)的管理、維護(hù)過(guò)程中，偶爾會(huì)遇到歸檔日志暴增的情況,也就是說(shuō)一些SQL語(yǔ)句產(chǎn)生了大量的redo log，那么如何跟蹤、定位哪些SQL語(yǔ)句生成了大量的redo log日志呢?下面這篇文章結(jié)合實(shí)際案例和官方文檔“How to identify the causes of High Redo Generation (文檔 ID 2265722.1)”來(lái)驗(yàn)證判斷一下。

首先，我們需要定位、判斷那個(gè)時(shí)間段的日志突然暴增了，注意，有些時(shí)間段生成了大量的redo log是正常業(yè)務(wù)行為，有可能每天這個(gè)時(shí)間段都有大量歸檔日志生成，例如，有大量作業(yè)在這個(gè)時(shí)間段集中運(yùn)行。而要分析突然、異常的大量redo log生成情況，就必須有數(shù)據(jù)分析對(duì)比，找到redo log大量產(chǎn)生的時(shí)間段，縮小分析的范圍是第一步。合理的縮小范圍能夠方便快速準(zhǔn)確定位問(wèn)題SQL。下面SQL語(yǔ)句分別統(tǒng)計(jì)了redo log的切換次數(shù)的相關(guān)數(shù)據(jù)指標(biāo)。這個(gè)可以間接判斷那個(gè)時(shí)間段產(chǎn)生了大量歸檔日志。

  1. /******統(tǒng)計(jì)每天redo log的切換次數(shù)匯總,以及與平均次數(shù)的對(duì)比*****/ 
  2. WITH T AS  
  3. ( 
  4.     SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD')    AS LOG_GEN_DAY,  
  5.            TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'YYYY-MM-DD'),  
  6.                        TO_CHAR(FIRST_TIME, 'YYYY-MM-DD'), 1, 0)) 
  7.                 , '999') AS "LOG_SWITCH_NUM"  
  8.     FROM   V$LOG_HISTORY  
  9.   WHERE FIRST_TIME < TRUNC(SYSDATE)  --排除當(dāng)前這一天 
  10.     GROUP  BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD')  
  11. ) 
  12. SELECT  T.LOG_GEN_DAY 
  13.       , T.LOG_SWITCH_NUM 
  14.       , M.AVG_LOG_SWITCH_NUM 
  15.       , (T.LOG_SWITCH_NUM-M.AVG_LOG_SWITCH_NUM) AS DIFF_SWITCH_NUM 
  16. FROM  T CROSS JOIN  
  17. ( 
  18.     SELECT  TO_CHAR(AVG(T.LOG_SWITCH_NUM),'999') AS AVG_LOG_SWITCH_NUM 
  19.     FROM T 
  20. ) M 
  21. ORDER BY T.LOG_GEN_DAY DESC; 
  22.   
  23.  
  24.   
  25.  
  26. SELECT    TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, 
  27.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00", 
  28.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01", 
  29.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02", 
  30.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03", 
  31.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04", 
  32.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05", 
  33.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06", 
  34.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07", 
  35.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08", 
  36.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09", 
  37.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10", 
  38.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11", 
  39.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12", 
  40.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13", 
  41.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14", 
  42.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15", 
  43.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16", 
  44.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17", 
  45.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18", 
  46.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19", 
  47.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20", 
  48.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21", 
  49.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22", 
  50.                 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23" 
  51. FROM V$LOG_HISTORY 
  52. GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD')  
  53. ORDER BY 1 DESC; 

如下案例所示，2018-03-26日有一個(gè)歸檔日志暴增的情況，我們可以橫向、縱向?qū)Ρ确治?，然后判定?7點(diǎn)到18點(diǎn)這段時(shí)間出現(xiàn)異常，這個(gè)時(shí)間段與往常對(duì)比，生成了大量的redo log。

這里分享一個(gè)非常不錯(cuò)的分析redo log 歷史信息的SQL

  1. ------------------------------------------------------------------------------------------------ 
  2. REM Author: Riyaj Shamsudeen @OraInternals, LLC 
  3. REM         www.orainternals.com 
  4. REM 
  5. REM Functionality: This script is to print redo size rates in a RAC claster 
  6. REM ************** 
  7. REM 
  8. REM Source  : AWR tables 
  9. REM 
  10. REM Exectution type: Execute from sqlplus or any other tool. 
  11. REM 
  12. REM Parameters: No parameters. Uses Last snapshot and the one prior snap 
  13. REM No implied or explicit warranty 
  14. REM 
  15. REM Please send me an email to rshamsud@orainternals.com, if you enhance this script :-) 
  16. REM  This is a open Source code and it is free to use and modify. 
  17. REM Version 1.20 
  18. REM 
  19. ------------------------------------------------------------------------------------------------ 
  20.   
  21. set colsep '|' 
  22. set lines 220 
  23. alter session set nls_date_format='YYYY-MM-DD HH24:MI'; 
  24. set pagesize 10000 
  25. with redo_data as ( 
  26. SELECT instance_number, 
  27.        to_date(to_char(redo_date,'DD-MON-YY-HH24:MI'), 'DD-MON-YY-HH24:MI') redo_dt, 
  28.        trunc(redo_size/(1024 * 1024),2) redo_size_mb 
  29.  FROM  ( 
  30.   SELECT dbid, instance_number, redo_date, redo_size , startup_time  FROM  ( 
  31.     SELECT  sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time, 
  32.   VALUE - 
  33.     lag (VALUE) OVER 
  34.     ( PARTITION BY  sysst.dbid, sysst.instance_number, startup_time 
  35.       ORDER BY begin_interval_time ,sysst.instance_number 
  36.      ) redo_size 
  37.   FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps 
  38. WHERE sysst.stat_id = 
  39.        ( SELECT stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' ) 
  40.   AND snaps.snap_id = sysst.snap_id 
  41.   AND snaps.dbid =sysst.dbid 
  42.   AND sysst.instance_number  = snaps.instance_number 
  43.   AND snaps.begin_interval_time> sysdate-30 
  44.    ORDER BY snaps.snap_id ) 
  45.   ) 
  46. ) 
  47. select  instance_number,  redo_dt, redo_size_mb, 
  48.     sum (redo_size_mb) over (partition by  trunc(redo_dt)) total_daily, 
  49.     trunc(sum (redo_size_mb) over (partition by  trunc(redo_dt))/24,2) hourly_rate 
  50.    from redo_Data 
  51. order by redo_dt, instance_number 
  52. / 

分析到這個(gè)階段，我們還只獲取了那個(gè)時(shí)間段歸檔日志異常(歸檔日志暴增)，那么要如何定位到相關(guān)的SQL語(yǔ)句呢?我們可以用下面SQL來(lái)定位：在這個(gè)時(shí)間段，哪些對(duì)象有大量數(shù)據(jù)塊變化情況。如下所示，這兩個(gè)對(duì)象(當(dāng)然，對(duì)象有可能是表或索引，這個(gè)案例中，這兩個(gè)對(duì)象其實(shí)是同一個(gè)表和其主鍵索引)有大量的數(shù)據(jù)塊修改情況?；旧衔覀兛梢耘袛嗍巧婕斑@個(gè)對(duì)象的DML語(yǔ)句生成了大量的redo log， 當(dāng)然有可能有些場(chǎng)景會(huì)比較復(fù)雜，不是那么容易定位。

  1. SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME,  
  2.        DHSO.OBJECT_NAME,  
  3.        SUM(DB_BLOCK_CHANGES_DELTA)                     BLOCK_CHANGED  
  4. FROM   DBA_HIST_SEG_STAT DHSS,  
  5.        DBA_HIST_SEG_STAT_OBJ DHSO,  
  6.        DBA_HIST_SNAPSHOT DHS  
  7. WHERE  DHS.SNAP_ID = DHSS.SNAP_ID  
  8.        AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER  
  9.        AND DHSS.OBJ# = DHSO.OBJ#  
  10.        AND DHSS.DATAOBJ# = DHSO.DATAOBJ#  
  11.        AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018-03-26 17:00',  
  12.                                        'YYYY-MM-DD HH24:MI')  
  13.                                        AND  
  14.            TO_DATE('2018-03-26 18:00', 'YYYY-MM-DD HH24:MI')  
  15. GROUP  BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'),  
  16.           DHSO.OBJECT_NAME  
  17. HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0  
  18. ORDER  BY SUM(DB_BLOCK_CHANGES_DELTA) DESC; 

此時(shí)，我們可以生成這個(gè)時(shí)間段的AWR報(bào)告，那些產(chǎn)生大量redo log的SQL一般是來(lái)自TOP Gets、TOP Execution中某個(gè)DML SQL語(yǔ)句或一些DML SQL語(yǔ)句，結(jié)合上面SQL定位到的對(duì)象和下面相關(guān)SQL語(yǔ)句，基本上就可以判斷就是下面這兩個(gè)SQL產(chǎn)生了大量的redo log。(第一個(gè)SQL是調(diào)用包，包里面有對(duì)這個(gè)表做大量的DELETE、INSERT操作)

如果你此時(shí)還不能完全斷定，也可以使用下面SQL來(lái)輔佐判斷那些SQL生成了大量的redo log。在這個(gè)案例中， 上面AWR報(bào)告中發(fā)現(xiàn)的SQL語(yǔ)句和下面SQL捕獲的SQL基本一致。那么可以進(jìn)一步佐證。

注意，該SQL語(yǔ)句執(zhí)行較慢，執(zhí)行時(shí)需要修改相關(guān)條件：時(shí)間和具體段對(duì)象。

  1. SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY_MM_DD HH24') WHEN, 
  2.        DBMS_LOB.SUBSTR(SQL_TEXT,4000,1) SQL, 
  3.        DHSS.INSTANCE_NUMBER INST_ID, 
  4.        DHSS.SQL_ID, 
  5.        EXECUTIONS_DELTA EXEC_DELTA, 
  6.        ROWS_PROCESSED_DELTA ROWS_PROC_DELTA 
  7. FROM DBA_HIST_SQLSTAT DHSS, 
  8.      DBA_HIST_SNAPSHOT DHS, 
  9.      DBA_HIST_SQLTEXT DHST 
  10. WHERE UPPER(DHST.SQL_TEXT) LIKE '%<segment_name>%'  --此處用具體的段對(duì)象替換 
  11.   AND LTRIM(UPPER(DHST.SQL_TEXT)) NOT LIKE 'SELECT%' 
  12.   AND DHSS.SNAP_ID=DHS.SNAP_ID 
  13.   AND DHSS.INSTANCE_NUMBER=DHS.INSTANCE_NUMBER 
  14.   AND DHSS.SQL_ID=DHST.SQL_ID 
  15.   AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018-03-26 17:00','YYYY-MM-DD HH24:MI') 
  16.   AND TO_DATE('2018-03-26 18:00','YYYY-MM-DD HH24:MI') 

其實(shí)上面分析已經(jīng)基本完全定位到SQL語(yǔ)句，剩下的就是和開發(fā)人員或Support人員溝通、了解是正常業(yè)務(wù)邏輯變更還是異常行為。如果需要進(jìn)一步挖掘深入，我們可以使用日志挖掘工具Log Miner深入分析。在此不做展開分析。其實(shí)個(gè)人在判斷分析時(shí)生成了正常時(shí)段和出現(xiàn)問(wèn)題時(shí)段的AWR對(duì)比報(bào)告(WORKLOAD REPOSITORY COMPARE PERIOD REPORT)，如下所示，其中一些信息也可以供分析、對(duì)比參考。可以為復(fù)雜場(chǎng)景做對(duì)比分析(因?yàn)閺?fù)雜場(chǎng)景，僅僅通過(guò)最上面的AWR報(bào)告可能無(wú)法準(zhǔn)確定位SQL)

此次截圖，沒(méi)有截取相關(guān)SQL，其實(shí)就是最上面分析的SQL語(yǔ)句，如果復(fù)雜場(chǎng)景下，非常有用。

參考資料：

How to identify the causes of High Redo Generation (文檔 ID 2265722.1)

 

責(zé)任編輯：武曉燕 來(lái)源： DBA閑思雜想錄
SQL Redo日志
相關(guān)推薦

2022-06-13 11:33:59

RedoMySQL

2015-07-13 13:12:51

閃存數(shù)據(jù)中心

2022-07-03 16:42:10

后端Web開發(fā)

2015-08-21 09:55:09

APP工程師泡沫

2010-07-08 13:13:14

清除SQL Serve

2019-07-28 18:30:52

MySQL日志數(shù)據(jù)庫(kù)

2022-08-15 09:00:23

數(shù)據(jù)庫(kù)日志

2011-04-28 10:09:11

傳真

2011-06-28 08:32:40

MySQL慢查詢?nèi)罩?/a>

2019-08-21 14:27:33

大數(shù)據(jù)網(wǎng)絡(luò)安全

2019-09-27 16:33:50

人工智能AI教育

2023-11-29 16:15:48

CIOCISO

2019-03-13 14:51:13

云計(jì)算AI云平臺(tái)

2017-12-28 15:40:16

開源LinuxGitHub

2018-09-20 11:54:31

數(shù)據(jù)庫(kù)MySQL性能優(yōu)化

2010-07-15 13:22:28

2022-05-11 07:41:55

死鎖運(yùn)算線程

2023-02-20 11:02:59

數(shù)字化轉(zhuǎn)型企業(yè)

2021-09-30 14:23:23

服務(wù)器開發(fā)工具

2018-01-26 07:44:04

數(shù)據(jù)中心機(jī)房霧霾
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)