oracle AWR PERF TREND HISTORY

WITH snap_range AS (
    -- 1. 定義時間範圍與基礎快照資訊
    SELECT 
        snap_id, dbid, instance_number, begin_interval_time, end_interval_time,
        -- 計算快照區間的總秒數，方便後續計算
        (CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400 as interval_seconds
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_time_model AS (
    -- 2. 取得 DB Time 的原始累計值
    SELECT snap_id, dbid, instance_number, value
    FROM dba_hist_sys_time_model
    WHERE stat_name = 'DB time'
),
LICENSE AS (
  select inst_id,CPU_CORE_COUNT_CURRENT from gV$LICENSE
)
SELECT * from(
SELECT 
    DB.DBID,
    sr.snap_id,
    sr.instance_number,
    sr.begin_interval_time,
    sr.end_interval_time,
    -- 3. 計算兩次快照間的增量 (Delta)，轉換為秒數
    ROUND((rt.value - LAG(rt.value) OVER (ORDER BY sr.snap_id)) / 1000000, 2) AS db_time_seconds,
    -- 4. 使用 CTE 預算的 interval_seconds 轉換成分鐘
    ROUND(sr.interval_seconds / 60, 2) AS interval_minutes,
    -- 5. 額外提供 AAS (Average Active Sessions) 指標，這是衡量負載最直觀的方式
    ROUND(((rt.value - LAG(rt.value) OVER (ORDER BY sr.snap_id)) / 1000000) / NULLIF(sr.interval_seconds, 0), 2) AS aas
FROM snap_range sr
JOIN DB on 1=1
JOIN raw_time_model rt ON sr.snap_id = rt.snap_id AND sr.dbid = rt.dbid AND sr.instance_number = rt.instance_number
ORDER BY sr.snap_id
) T
WHERE t.db_time_seconds >=0

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time,
           (CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400 as interval_seconds
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_stats AS (
    -- 取得 System Statistics (SYSSTAT) 的累計值
    SELECT snap_id,
           MAX(DECODE(stat_name, 'redo size', value)) as redo_size,
           MAX(DECODE(stat_name, 'session logical reads', value)) as logical_reads,
           MAX(DECODE(stat_name, 'db block changes', value)) as block_changes,
           MAX(DECODE(stat_name, 'physical reads', value)) as phys_reads,
           MAX(DECODE(stat_name, 'physical writes', value)) as phys_writes,
           MAX(DECODE(stat_name, 'physical read IO requests', value)) as read_io_req,
           MAX(DECODE(stat_name, 'physical write IO requests', value)) as write_io_req,
           MAX(DECODE(stat_name, 'physical read total bytes', value)) as read_io_bytes,
           MAX(DECODE(stat_name, 'physical write total bytes', value)) as write_io_bytes,
           MAX(DECODE(stat_name, 'logons cumulative', value)) as logons,
           MAX(DECODE(stat_name, 'user logons cumulative', value)) as user_logons,
           MAX(DECODE(stat_name, 'execute count', value)) as executes,
           MAX(DECODE(stat_name, 'user rollbacks', value)) as rollbacks
    FROM dba_hist_sysstat
    WHERE stat_name IN ('redo size', 'session logical reads', 'db block changes', 'physical reads', 
                        'physical writes', 'physical read IO requests', 'physical write IO requests',
                        'physical read total bytes', 'physical write total bytes', 
                        'logons cumulative', 'user logons cumulative', 'execute count', 'user rollbacks')
    GROUP BY snap_id
),
raw_time_model AS (
    SELECT snap_id,
           MAX(DECODE(stat_name, 'DB time', value)) as db_time,
           MAX(DECODE(stat_name, 'DB CPU', value)) as db_cpu
    FROM dba_hist_sys_time_model
    WHERE stat_name IN ('DB time', 'DB CPU')
    GROUP BY snap_id
)
SELECT * FROM (
SELECT 
    DB.DBID,
    s.snap_id,
    s.instance_number,
    s.begin_interval_time,
    s.end_interval_time,
    ROUND((tm.db_time - LAG(tm.db_time) OVER (ORDER BY s.snap_id)) / 1000000, 2) as "DB Time (s)",
    ROUND((tm.db_cpu - LAG(tm.db_cpu) OVER (ORDER BY s.snap_id)) / 1000000, 2) as "DB CPU (s)",
    (rs.redo_size - LAG(rs.redo_size) OVER (ORDER BY s.snap_id)) as "Redo Size (bytes)",
    (rs.logical_reads - LAG(rs.logical_reads) OVER (ORDER BY s.snap_id)) as "Logical Reads (blocks)",
    (rs.block_changes - LAG(rs.block_changes) OVER (ORDER BY s.snap_id)) as "Block Changes",
    (rs.phys_reads - LAG(rs.phys_reads) OVER (ORDER BY s.snap_id)) as "Phys Reads (blocks)",
    (rs.phys_writes - LAG(rs.phys_writes) OVER (ORDER BY s.snap_id)) as "Phys Writes (blocks)",
    (rs.read_io_req - LAG(rs.read_io_req) OVER (ORDER BY s.snap_id)) as "Read IO Req",
    (rs.write_io_req - LAG(rs.write_io_req) OVER (ORDER BY s.snap_id)) as "Write IO Req",
    ROUND((rs.read_io_bytes - LAG(rs.read_io_bytes) OVER (ORDER BY s.snap_id)) / 1024 / 1024, 2) as "Read IO (MB)",
    ROUND((rs.write_io_bytes - LAG(rs.write_io_bytes) OVER (ORDER BY s.snap_id)) / 1024 / 1024, 2) as "Write IO (MB)",
    (rs.logons - LAG(rs.logons) OVER (ORDER BY s.snap_id)) as "Logons",
    (rs.user_logons - LAG(rs.user_logons) OVER (ORDER BY s.snap_id)) as "User Logons",
    (rs.executes - LAG(rs.executes) OVER (ORDER BY s.snap_id)) as "Executes",
    (rs.rollbacks - LAG(rs.rollbacks) OVER (ORDER BY s.snap_id)) as "Rollbacks"
FROM snap_range s
JOIN DB on 1=1
JOIN raw_stats rs ON s.snap_id = rs.snap_id
JOIN raw_time_model tm ON s.snap_id = tm.snap_id
ORDER BY s.snap_id
) T
WHERE t."DB Time (s)" >=0

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time,
           -- 計算快照區間的總秒數
           (CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400 as interval_seconds
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_stats AS (
    SELECT snap_id,
           MAX(DECODE(stat_name, 'redo size', value)) as redo_size,
           MAX(DECODE(stat_name, 'session logical reads', value)) as logical_reads,
           MAX(DECODE(stat_name, 'physical reads', value)) as phys_reads,
           MAX(DECODE(stat_name, 'physical read total bytes', value)) as read_io_bytes,
           MAX(DECODE(stat_name, 'execute count', value)) as executes,
           MAX(DECODE(stat_name, 'user commits', value)) as commits,
           MAX(DECODE(stat_name, 'user rollbacks', value)) as rollbacks
    FROM dba_hist_sysstat
    WHERE stat_name IN ('redo size', 'session logical reads', 'physical reads', 
                        'physical read total bytes', 'execute count', 'user commits', 'user rollbacks')
    GROUP BY snap_id
),
deltas AS (
    -- 先計算出每個指標的增量 (Delta)
    SELECT 
        DB.DBID,
	    s.snap_id,
	    s.instance_number,
	    s.begin_interval_time,
	    s.end_interval_time,
		s.interval_seconds,
        (rs.redo_size - LAG(rs.redo_size) OVER (ORDER BY s.snap_id)) as d_redo,
        (rs.logical_reads - LAG(rs.logical_reads) OVER (ORDER BY s.snap_id)) as d_lread,
        (rs.phys_reads - LAG(rs.phys_reads) OVER (ORDER BY s.snap_id)) as d_pread,
        (rs.read_io_bytes - LAG(rs.read_io_bytes) OVER (ORDER BY s.snap_id)) as d_read_bytes,
        (rs.executes - LAG(rs.executes) OVER (ORDER BY s.snap_id)) as d_exec,
        -- 事務數計算：Commits + Rollbacks
        ( (rs.commits - LAG(rs.commits) OVER (ORDER BY s.snap_id)) + 
          (rs.rollbacks - LAG(rs.rollbacks) OVER (ORDER BY s.snap_id)) ) as d_tx
    FROM snap_range s
    JOIN DB on 1=1
    JOIN raw_stats rs ON s.snap_id = rs.snap_id
)
-- 最終輸出：標準化指標
SELECT 
    DBID,snap_id,instance_number,begin_interval_time,end_interval_time,
    -- Per Second (每秒)
    ROUND(d_redo / NULLIF(interval_seconds, 0), 2) as "Redo/s (bytes)",
    ROUND(d_lread / NULLIF(interval_seconds, 0), 2) as "Logical Reads/s",
    ROUND(d_pread / NULLIF(interval_seconds, 0), 2) as "Phys Reads/s",
    ROUND(d_exec / NULLIF(interval_seconds, 0), 2) as "Executes/s",
    ROUND(d_tx / NULLIF(interval_seconds, 0), 2) as "Transactions/s",
    -- Per Transaction (每個事務)
    ROUND(d_redo / NULLIF(d_tx, 0), 2) as "Redo/Tx (bytes)",
    ROUND(d_lread / NULLIF(d_tx, 0), 2) as "Logical Reads/Tx",
    ROUND(d_pread / NULLIF(d_tx, 0), 2) as "Phys Reads/Tx",
    ROUND((d_read_bytes/1024) / NULLIF(d_tx, 0), 2) as "Read KB/Tx"
FROM deltas
WHERE d_tx >= 0 -- 過濾掉重啟後的負值
ORDER BY snap_id

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_stats AS (
    SELECT snap_id,
           -- Buffer Hit 相關
           MAX(DECODE(stat_name, 'session logical reads', value)) as log_rd,
           MAX(DECODE(stat_name, 'physical reads', value)) as phy_rd,
           MAX(DECODE(stat_name, 'physical reads direct', value)) as phy_rd_dir,
           MAX(DECODE(stat_name, 'physical reads cache', value)) as phy_rd_ch,
           -- Parse 相關
           MAX(DECODE(stat_name, 'parse count (total)', value)) as prs_tot,
           MAX(DECODE(stat_name, 'parse count (hard)', value)) as prs_hard,
           MAX(DECODE(stat_name, 'execute count', value)) as exec_cnt,
           -- Redo / Buffer / Latch Nowait
           MAX(DECODE(stat_name, 'redo log space requests', value)) as redo_spc_req,
           MAX(DECODE(stat_name, 'buffer is not pinned count', value)) as buf_not_pin, -- 簡化計算用
           MAX(DECODE(stat_name, 'latch free', value)) as latch_wait,
           -- Sort
           MAX(DECODE(stat_name, 'sorts (memory)', value)) as sort_mem,
           MAX(DECODE(stat_name, 'sorts (disk)', value)) as sort_disk
    FROM dba_hist_sysstat
    GROUP BY snap_id
),
raw_time AS (
    SELECT snap_id,
           MAX(DECODE(stat_name, 'DB CPU', value)) as db_cpu,
           MAX(DECODE(stat_name, 'parse time elapsed', value)) as prs_elap,
           MAX(DECODE(stat_name, 'parse time cpu', value)) as prs_cpu
    FROM dba_hist_sys_time_model
    GROUP BY snap_id
),
deltas AS (
    -- 計算各項指標增量 (Delta)
    SELECT 
		DB.DBID,
	    s.snap_id,
	    s.instance_number,
	    s.begin_interval_time,
	    s.end_interval_time,
        (rs.log_rd - LAG(rs.log_rd) OVER (ORDER BY s.snap_id)) as d_log_rd,
        (rs.phy_rd - LAG(rs.phy_rd) OVER (ORDER BY s.snap_id)) as d_phy_rd,
        (rs.phy_rd_dir - LAG(rs.phy_rd_dir) OVER (ORDER BY s.snap_id)) as d_phy_rd_dir,
        (rs.prs_tot - LAG(rs.prs_tot) OVER (ORDER BY s.snap_id)) as d_prs_tot,
        (rs.prs_hard - LAG(rs.prs_hard) OVER (ORDER BY s.snap_id)) as d_prs_hard,
        (rs.exec_cnt - LAG(rs.exec_cnt) OVER (ORDER BY s.snap_id)) as d_exec,
        (rs.sort_mem - LAG(rs.sort_mem) OVER (ORDER BY s.snap_id)) as d_sort_mem,
        (rs.sort_disk - LAG(rs.sort_disk) OVER (ORDER BY s.snap_id)) as d_sort_disk,
        (rt.db_cpu - LAG(rt.db_cpu) OVER (ORDER BY s.snap_id)) as d_db_cpu,
        (rt.prs_elap - LAG(rt.prs_elap) OVER (ORDER BY s.snap_id)) as d_prs_elap,
        (rt.prs_cpu - LAG(rt.prs_cpu) OVER (ORDER BY s.snap_id)) as d_prs_cpu
    FROM snap_range s
    JOIN DB on 1=1
    JOIN raw_stats rs ON s.snap_id = rs.snap_id
    JOIN raw_time rt ON s.snap_id = rt.snap_id
)
SELECT 
    DBID,snap_id,instance_number,begin_interval_time,end_interval_time,
    -- 1. Buffer Hit %: 1 - (Physical Reads Cache / Session Logical Reads)
    ROUND(100 * (1 - ( (d_phy_rd - d_phy_rd_dir) / NULLIF(d_log_rd, 0) )), 2) as "Buffer Hit %",
    -- 2. Soft Parse %: 1 - (Hard Parse / Total Parse)
    ROUND(100 * (1 - ( d_prs_hard / NULLIF(d_prs_tot, 0) )), 2) as "Soft Parse %",
    -- 3. Execute to Parse %: 1 - (Total Parse / Execute Count)
    ROUND(100 * (1 - ( d_prs_tot / NULLIF(d_exec, 0) )), 2) as "Execute to Parse %",
    -- 4. Parse CPU to Parse Elapsd %: (Parse CPU / Parse Elapsed)
    ROUND(100 * ( d_prs_cpu / NULLIF(d_prs_elap, 0) ), 2) as "Parse CPU to Elap %",
    -- 5. In-memory Sort %: Memory Sorts / (Memory + Disk Sorts)
    ROUND(100 * ( d_sort_mem / NULLIF(d_sort_mem + d_sort_disk, 0) ), 2) as "In-memory Sort %",
    -- 6. % Non-Parse CPU: (DB CPU - Parse CPU) / DB CPU
    ROUND(100 * ( (d_db_cpu - d_prs_cpu) / NULLIF(d_db_cpu, 0) ), 2) as "% Non-Parse CPU"
FROM deltas
WHERE d_log_rd > 0
ORDER BY snap_id

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_osstat AS (
    -- 取得主機實體記憶體總量 (Host Mem)
    SELECT snap_id,
           MAX(DECODE(stat_name, 'PHYSICAL_MEMORY_BYTES', value)) / 1024 / 1024 as host_mem_mb
    FROM dba_hist_osstat
    WHERE stat_name = 'PHYSICAL_MEMORY_BYTES'
    GROUP BY snap_id
),
raw_sgastat AS (
    -- 取得該快照點 SGA 的總和 (SGA Use)
    -- 注意：SGA 在 AWR 中是分組件記錄的，需要加總
    SELECT snap_id,
           SUM(BYTES) / 1024 / 1024 as sga_use_mb
    FROM dba_hist_sgastat
    GROUP BY snap_id
),
raw_pgastat AS (
    -- 取得該快照點 PGA 的總分配量 (PGA Use)
    SELECT snap_id,
           MAX(DECODE(name, 'total PGA allocated', value)) / 1024 / 1024 as pga_use_mb
    FROM dba_hist_pgastat
    WHERE name = 'total PGA allocated'
    GROUP BY snap_id
)
SELECT 
    DB.DBID,
    s.snap_id,
    s.instance_number,
    s.begin_interval_time,
    s.end_interval_time,
    -- Memory Statistics 指標
    ROUND(os.host_mem_mb, 2) as "Host Mem (MB)",
    ROUND(sga.sga_use_mb, 2) as "SGA use (MB)",
    ROUND(pga.pga_use_mb, 2) as "PGA use (MB)",
    -- 衍生計算：總資料庫記憶體佔主機比例
    ROUND((sga.sga_use_mb + pga.pga_use_mb) / NULLIF(os.host_mem_mb, 0) * 100, 2) as "DB Mem/Host %"
FROM snap_range s
JOIN DB on 1=1
LEFT JOIN raw_osstat os ON s.snap_id = os.snap_id
LEFT JOIN raw_sgastat sga ON s.snap_id = sga.snap_id
LEFT JOIN raw_pgastat pga ON s.snap_id = pga.snap_id
ORDER BY s.snap_id

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_sga_cache AS (
    -- 從 SGASTAT 彙總各關鍵組件的大小
    SELECT 
        snap_id,
        -- Buffer Cache 通常紀錄為 'buffer_cache'
        SUM(DECODE(name, 'buffer_cache', BYTES, 0)) / 1024 / 1024 as buffer_cache_mb,
        -- Shared Pool 需要加總 pool 欄位為 'shared pool' 的所有項目
        SUM(CASE WHEN pool = 'shared pool' THEN BYTES ELSE 0 END) / 1024 / 1024 as shared_pool_mb,
        -- In-Memory Area (若未啟用則為 0)
        SUM(CASE WHEN pool = 'inmemory pool' THEN BYTES ELSE 0 END) / 1024 / 1024 as inmemory_mb,
        -- Log Buffer 是固定大小的項
        MAX(DECODE(name, 'log_buffer', BYTES, 0)) / 1024 / 1024 as log_buffer_mb
    FROM dba_hist_sgastat
    GROUP BY snap_id
),
raw_params AS (
    -- 獲取標準塊大小 (db_block_size)
    SELECT 
        snap_id,
        value as std_block_size
    FROM dba_hist_parameter
    WHERE parameter_name = 'db_block_size'
)
SELECT 
    DB.DBID,
    s.snap_id,
    s.instance_number,
    s.begin_interval_time,
    s.end_interval_time,
    -- Cache Sizes 指標
    ROUND(c.buffer_cache_mb, 2) as "Buffer Cache (MB)",
    ROUND(c.shared_pool_mb, 2) as "Shared Pool (MB)",
    ROUND(c.inmemory_mb, 2) as "In-Memory Area (MB)",
    ROUND(c.log_buffer_mb, 2) as "Log Buffer (MB)",
    p.std_block_size as "Std Block Size"
FROM snap_range s
JOIN DB on 1=1
LEFT JOIN raw_sga_cache c ON s.snap_id = c.snap_id
LEFT JOIN raw_params p ON s.snap_id = p.snap_id
ORDER BY s.snap_id

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_shared_pool AS (
    -- 1. 計算 Shared Pool 使用率 (總量 vs 空閒)
    SELECT 
        snap_id,
        SUM(bytes) as total_sp_bytes,
        SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_sp_bytes
    FROM dba_hist_sgastat
    WHERE pool = 'shared pool'
    GROUP BY snap_id
),
raw_sql_reuse AS (
    -- 2. 計算 SQL 重用性 (基於 AWR 捕獲到的 SQL)
    -- 注意：AWR 僅記錄 Top SQL，但其比例趨勢仍具備高度參考價值
    SELECT 
        snap_id,
        COUNT(sql_id) as total_sql_count,
        COUNT(CASE WHEN executions_total > 1 THEN sql_id END) as reused_sql_count,
        SUM(sharable_mem) as total_sql_mem_bytes,
        SUM(CASE WHEN executions_total > 1 THEN sharable_mem ELSE 0 END) as reused_sql_mem_bytes
    FROM dba_hist_sqlstat
    GROUP BY snap_id
)
SELECT 
    DB.DBID,
    s.snap_id,
    s.instance_number,
    s.begin_interval_time,
    s.end_interval_time,
    -- Shared Pool Statistics 指標
    -- Memory Usage %: (Total - Free) / Total
    ROUND(100 * (sp.total_sp_bytes - sp.free_sp_bytes) / NULLIF(sp.total_sp_bytes, 0), 2) as "SP Memory Usage %",
    -- % SQL with executions > 1: (重用 SQL 數 / 總 SQL 數)
    ROUND(100 * sr.reused_sql_count / NULLIF(sr.total_sql_count, 0), 2) as "% SQL w/exec>1",
    -- % Memory for SQL w/exec > 1: (重用 SQL 佔用的記憶體 / 總 SQL 記憶體)
    ROUND(100 * sr.reused_sql_mem_bytes / NULLIF(sr.total_sql_mem_bytes, 0), 2) as "% Mem for SQL w/exec>1",
    ROUND(sr.total_sql_mem_bytes / 1024 / 1024, 2) as "Total_SQL_Mem_MB",
    ROUND(sr.avg_sql_mem_bytes / 1024 / 1024, 2) as "avg_SQL_Mem_MB"
FROM snap_range s
JOIN DB on 1=1
LEFT JOIN raw_shared_pool sp ON s.snap_id = sp.snap_id
LEFT JOIN raw_sql_reuse sr ON s.snap_id = sr.snap_id
ORDER BY s.snap_id

WITH snap_range AS (
    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    WHERE instance_number = 1
    AND end_interval_time >= SYSDATE - 1
    AND begin_interval_time <= SYSDATE
),
DB AS (SELECT DBID FROM V$DATABASE),
raw_db_time AS (
    -- 取得總 DB Time 的累計值
    SELECT snap_id,
           MAX(value) as db_time_val
    FROM dba_hist_sys_time_model
    WHERE stat_name = 'DB time'
    GROUP BY snap_id
),
raw_wait_class AS (
    -- 取得各 Wait Class 的總等待時間累計值 (單位：微秒)
    -- 過濾掉 Idle 等待，因為它不計入 DB Time
    SELECT snap_id,
           SUM(DECODE(wait_class, 'User I/O', time_waited_micro, 0)) as user_io_time,
           SUM(DECODE(wait_class, 'System I/O', time_waited_micro, 0)) as system_io_time,
           SUM(DECODE(wait_class, 'Network', time_waited_micro, 0)) as network_time,
           SUM(DECODE(wait_class, 'Commit', time_waited_micro, 0)) as commit_time,
           SUM(DECODE(wait_class, 'Concurrency', time_waited_micro, 0)) as concurrency_time,
           SUM(DECODE(wait_class, 'Other', time_waited_micro, 0)) as other_time
    FROM dba_hist_system_event
    WHERE wait_class <> 'Idle'
    GROUP BY snap_id
),
deltas AS (
    -- 計算增量 (Delta)
    SELECT 
	    DB.DBID,
	    s.snap_id,
	    s.instance_number,
	    s.begin_interval_time,
	    s.end_interval_time,
        (t.db_time_val - LAG(t.db_time_val) OVER (ORDER BY s.snap_id)) as d_db_time,
        (wc.user_io_time - LAG(wc.user_io_time) OVER (ORDER BY s.snap_id)) as d_user_io,
        (wc.system_io_time - LAG(wc.system_io_time) OVER (ORDER BY s.snap_id)) as d_sys_io,
        (wc.network_time - LAG(wc.network_time) OVER (ORDER BY s.snap_id)) as d_net,
        (wc.commit_time - LAG(wc.commit_time) OVER (ORDER BY s.snap_id)) as d_cmt,
        (wc.concurrency_time - LAG(wc.concurrency_time) OVER (ORDER BY s.snap_id)) as d_concur,
        (wc.other_time - LAG(wc.other_time) OVER (ORDER BY s.snap_id)) as d_other
    FROM snap_range s
    JOIN DB on 1=1
    JOIN raw_db_time t ON s.snap_id = t.snap_id
    JOIN raw_wait_class wc ON s.snap_id = wc.snap_id
)
SELECT 
	DBID,snap_id,instance_number,begin_interval_time,end_interval_time,
    -- 計算百分比
    ROUND(100 * d_user_io / NULLIF(d_db_time, 0), 2) as "% User I/O",
    ROUND(100 * d_sys_io / NULLIF(d_db_time, 0), 2) as "% System I/O",
    ROUND(100 * d_net / NULLIF(d_db_time, 0), 2) as "% Network",
    ROUND(100 * d_cmt / NULLIF(d_db_time, 0), 2) as "% Commit",
    ROUND(100 * d_concur / NULLIF(d_db_time, 0), 2) as "% Concurrency",
    ROUND(100 * d_other / NULLIF(d_db_time, 0), 2) as "% Other",
    -- 補充：DB CPU 佔比 (DB Time = CPU + Wait Time)
    ROUND(100 * (d_db_time - (d_user_io + d_sys_io + d_net + d_cmt + d_concur + d_other)) / NULLIF(d_db_time, 0), 2) as "% DB CPU (Est.)"
FROM deltas
WHERE d_db_time > 0
ORDER BY snap_id