Generate report - log file sync

最新推荐文章于 2025-10-05 14:35:37 发布

原创最新推荐文章于 2025-10-05 14:35:37 发布 · 108 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

77 篇文章

订阅专栏

本文深入探讨了一段SQL查询代码，旨在优化日志同步过程中的性能。通过分析日志文件同步事件的时间等待和等待次数，作者识别了性能瓶颈并提供了优化建议。

set term off heading on verify off pagesize 1000
set markup html on entmap ON preformat off spool on

spool c:\temp\event_log_file_sync.html

select s.BEGIN_INTERVAL_TIME, m.INSTANCE_NUMBER, m.SNAP_ID, EVENT_NAME, m.EVENT_TIME_WAITED, m.TOTAL_WAITS, m.PCT, AVG_WAIT
    FROM    (SELECT ee.instance_number,
                    ee.snap_id,
                    ee.event_name,
                    ROUND (ee.event_time_waited / 1000000) event_time_waited,
                    ee.total_waits,
                    ROUND (
                       (ee.event_time_waited * 100) / et.total_time_waited,
                       1)
                       pct,
                    ROUND ( (ee.event_time_waited / ee.total_waits) / 1000)
                       avg_wait
               FROM    (SELECT ee1.instance_number,
                               ee1.snap_id,
                               ee1.event_name,
                               ee1.time_waited_micro - ee2.time_waited_micro
                                  event_time_waited,
                               ee1.total_waits - ee2.total_waits total_waits
                          FROM    dba_hist_system_event ee1
                               JOIN
                                  dba_hist_system_event ee2
                               ON     ee1.snap_id = ee2.snap_id + 1
                                  AND ee1.instance_number = ee2.instance_number
                                  AND ee1.event_id = ee2.event_id
                                  AND ee1.wait_class_id <> 2723168908
                                  AND ee1.time_waited_micro
                                      - ee2.time_waited_micro > 0
                        UNION
                        SELECT st1.instance_number,
                               st1.snap_id,
                               st1.stat_name event_name,
                               st1.VALUE - st2.VALUE event_time_waited,
                               1 total_waits
                          FROM    dba_hist_sys_time_model st1
                               JOIN
                                  dba_hist_sys_time_model st2
                               ON     st1.instance_number = st2.instance_number
                                  AND st1.snap_id = st2.snap_id + 1
                                  AND st1.stat_id = st2.stat_id
                                  AND st1.stat_name = 'DB CPU'
                                  AND st1.VALUE - st2.VALUE > 0) ee
                    JOIN
                       (SELECT et1.instance_number,
                               et1.snap_id,
                               et1.VALUE - et2.VALUE total_time_waited
                          FROM    dba_hist_sys_time_model et1
                               JOIN
                                  dba_hist_sys_time_model et2
                               ON     et1.snap_id = et2.snap_id + 1
                                  AND et1.instance_number = et2.instance_number
                                  AND et1.stat_id = et2.stat_id
                                  AND et1.stat_name = 'DB time'
                                  AND et1.VALUE - et2.VALUE > 0) et
                    ON ee.instance_number = et.instance_number
                       AND ee.snap_id = et.snap_id) m
         JOIN
            dba_hist_snapshot s
         ON m.snap_id = s.snap_id AND m.instance_number = s.instance_number
   WHERE TO_CHAR (BEGIN_INTERVAL_TIME+0.003, 'YYYYMMDDHH24MI') > CONCAT('&&1', '&&2') AND TO_CHAR (BEGIN_INTERVAL_TIME-0.003, 'YYYYMMDDHH24MI') < CONCAT('&&1', '&&3') AND event_name = 'log file sync'
ORDER BY m.instance_number, m.snap_id, event_time_waited desc;

spool off