↓ 快轉到主要內容
PolloChang 工作筆記

oracle AWR TOP SQL

·7545 字·16 分鐘·
Oracle Oracle AWR
PolloChang
作者
PolloChang
我是一隻雞
目錄

以下我針對 AWR 中的 TOP SQL 進行研究

  • SQL ordered by Elapsed Time
  • SQL ordered by CPU Time
  • SQL ordered by User I/O Wait Time
  • SQL ordered by Gets
  • SQL ordered by Reads
  • SQL ordered by Physical Reads (UnOptimized)
  • SQL ordered by Executions
  • SQL ordered by Parse Calls
  • SQL ordered by Sharable Memory
  • SQL ordered by Version Count

尋找方式
#

1. SQL ordered by TOP Event
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

WITH sql_metrics AS (
    -- 1. 從歷史視圖中加總各項增量指標
    SELECT 
        s.sql_id,
        SUM(s.elapsed_time_delta) / 1000000 as total_elapsed_sec,
        SUM(s.cpu_time_delta) / 1000000 as total_cpu_sec,
        SUM(s.iowait_delta) / 1000000 as total_io_wait_sec,
        SUM(s.buffer_gets_delta) as total_gets,
        SUM(s.disk_reads_delta) as total_reads,
        -- Physical Reads (UnOptimized) 通常是指排除掉從 Flash Cache 讀取的真正磁碟讀取
        SUM(s.physical_read_requests_delta) as total_phys_read_req,
        SUM(s.executions_delta) as total_execs,
        SUM(s.parse_calls_delta) as total_parse_calls,
        -- 這些是目前快照點的狀態值，取最大值作為參考
        MAX(s.sharable_mem) / 1024 / 1024 as max_sharable_mem_mb,
        MAX(s.version_count) as max_version_count
    FROM dba_hist_sqlstat s
    JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number
    WHERE sn.begin_interval_time >= SYSDATE - 1
    GROUP BY s.sql_id
),
ranked_sql AS (
    -- 2. 針對各個維度進行排名
    SELECT 
        m.*,
        RANK() OVER (ORDER BY total_elapsed_sec DESC) as rank_elapsed,
        RANK() OVER (ORDER BY total_cpu_sec DESC) as rank_cpu,
        RANK() OVER (ORDER BY total_io_wait_sec DESC) as rank_io,
        RANK() OVER (ORDER BY total_gets DESC) as rank_gets,
        RANK() OVER (ORDER BY total_reads DESC) as rank_reads,
        RANK() OVER (ORDER BY total_phys_read_req DESC) as rank_unoptimized,
        RANK() OVER (ORDER BY total_execs DESC) as rank_execs,
        RANK() OVER (ORDER BY total_parse_calls DESC) as rank_parse,
        RANK() OVER (ORDER BY max_sharable_mem_mb DESC) as rank_mem,
        RANK() OVER (ORDER BY max_version_count DESC) as rank_version
    FROM sql_metrics m
)
-- 3. 結合 SQL 文字並輸出 (此處以 Elapsed Time 前 20 名為例)
SELECT * FROM (
    SELECT 
        r.sql_id,
        r.total_elapsed_sec as "Elapsed(s)",
        r.total_cpu_sec as "CPU(s)",
        r.total_io_wait_sec as "IO_Wait(s)",
        r.total_gets as "Gets",
        r.total_reads as "Reads",
        r.total_execs as "Execs",
        r.total_parse_calls as "Parses",
        ROUND(r.max_sharable_mem_mb, 2) as "Mem(MB)",
        r.max_version_count as "Versions",
        -- 顯示前 100 個字元的 SQL 文字
        DBMS_LOB.SUBSTR(st.sql_text, 100, 1) as sql_snippet
    FROM ranked_sql r
    LEFT JOIN dba_hist_sqltext st ON r.sql_id = st.sql_id
    -- 你可以更換下方的排序條件來查看不同的 TOP 榜單
    ORDER BY r.rank_elapsed ASC 
) WHERE ROWNUM <= 20

執行結果範例

1
2
3
4
5
6
7

SQL_ID       |Elapsed(s)|CPU(s)   |IO_Wait(s)|Gets   |Reads|Execs|Parses|Mem(MB)|Versions|SQL_SNIPPET                                                                                         |
-------------+----------+---------+----------+-------+-----+-----+------+-------+--------+----------------------------------------------------------------------------------------------------+
1m0ycv6wsty7n| 61.979993|60.790774|  0.184443|6704627|  711|    2|     2|    0.5|       1|WITH snap_range AS (¶    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_ti|
05s9358mm6vrr|  14.77161|14.543842|  0.029922| 421192|   28|    1|     1|   0.02|       1|begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;                              |
fjhkhwhpjm3sg| 11.539788|11.325182|         0|1267227|    0|    1|     1|    0.5|       1|WITH snap_range AS (¶    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_ti|
9qrhhm7pf2ghv|  6.847261| 6.710161|         0|   1131|    0|  108|   108|   0.07|       1|insert into  wrh$_mvparameter  (dbid, per_pdb, con_dbid, snap_id, instance_number, parameter_hash, o|
10a6561dxa8d5|  5.730789| 5.639107|         0| 623177|    0|    1|     1|    0.5|       1|WITH snap_range AS (¶    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_ti|

根據列出的這些排序維度，實戰中通常會這樣判斷：

  • Elapsed Time vs CPU Time: 如果 Elapsed Time 很高但 CPU Time 很低，代表 SQL 大部分時間在「等待」（如 I/O 或 Lock）。
  • Gets (Logical Reads): 這是衡量 SQL 效率最穩定的指標。如果 Gets 很高但 Rows Processed 很少，說明索引效率極差（Index Scan 讀了太多 Block）。
  • Physical Reads (UnOptimized): 數值高代表該 SQL 經常迫使資料庫從實體磁碟讀取資料，未命中 Buffer Cache 或 Flash Cache，對硬體壓力最大。
  • Parse Calls: 如果這項指標很高且與 Executions 接近，代表應用程式沒有重用 Cursor，每次執行都要解析，非常消耗 CPU。
  • Sharable Memory & Version Count:
  • Version Count: 如果一條 SQL 有數百個版本，代表發生了 High Version Count 問題（通常與 ACS、Bind Peek、shared pool 不足 或不同 Session 的環境參數設定有關）。
  • Sharable Memory: 如果單一 SQL 佔用過多記憶體（如 > 100MB），會增加 Shared Pool 的壓力。

AWR 歷史 TOP SQL 記憶體佔用分析
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

WITH snap_range AS (
    -- 1. 定義時間範圍與基礎快照資訊 (過去 24 小時)
    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
),
sql_mem_stats AS (
    -- 2. 彙整該時段內 SQL 的記憶體與版本資訊
    SELECT 
        s.sql_id,
        -- 取得該時段內此 SQL 曾達到的最大記憶體佔用
        MAX(s.sharable_mem) / 1024 / 1024 as max_sharable_mem_mb,
        -- 取得對應的最大子游標數量
        MAX(s.version_count) as max_version_count,
        -- 累計執行次數
        SUM(s.executions_delta) as total_execs
    FROM dba_hist_sqlstat s
    JOIN snap_range sr ON s.snap_id = sr.snap_id 
                      AND s.dbid = sr.dbid 
                      AND s.instance_number = sr.instance_number
    GROUP BY s.sql_id
)
-- 3. 結合 SQL 文字並輸出前 20 名
SELECT * FROM (
    SELECT 
        m.sql_id,
        ROUND(m.max_sharable_mem_mb, 2) as "Peak_Mem (MB)",
        m.max_version_count as "Max_Versions",
        m.total_execs as "Total_Executions",
        DBMS_LOB.SUBSTR(st.sql_text, 100, 1) as sql_snippet
    FROM sql_mem_stats m
    LEFT JOIN dba_hist_sqltext st ON m.sql_id = st.sql_id
    -- 依據最大記憶體佔用排序
    ORDER BY m.max_sharable_mem_mb DESC
) WHERE ROWNUM <= 20;

執行結果範例

1
2
3
4
5

SQL_ID       |Peak_Mem (MB)|Max_Versions|Total_Executions|SQL_SNIPPET                                                                                         |
-------------+-------------+------------+----------------+----------------------------------------------------------------------------------------------------+
9yv5dwv8k0awg|         4.17|           1|               4|WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, |
ggh55rhz95kyj|         3.57|           1|               1|SELECT AUDIT_OPTION_TYPE, COUNT(DISTINCT POLICY_NAME) POL_CNT FROM AUDIT_UNIFIED_POLICIES GROUP BY A|
f9dcfdnuhy9z1|         2.12|           2|             133|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|

  1. 高記憶體 + 低版本 (High Mem, Low Versions):

    • 現象：Peak_Mem 超過 50-100MB，但 Max_Versions 只有個位數。
    • 原因：這通常是 SQL 文本過長 造成的。最常見的是 IN 子句中包含了數千個參數，或是極其複雜的巢狀 CASE WHEN 邏輯。這會導致解析樹（Parse Tree）變得極其龐大。

  2. 高記憶體 + 高版本 (High Mem, High Versions):

    • 現象：Peak_Mem 很高，同時 Max_Versions 超過 100 甚至上千。
    • 原因：這是 子游標無法共享 (Cursor Non-sharing)。可能是因為不同 Session 的參數設定不同、綁定變數長度差異過大，或是觸發了 Adaptive Cursor Sharing。雖然每個版本可能不大，但累積起來會吃掉大量 Shared Pool。

  3. 高記憶體 + 零執行 (High Mem, 0 Execs):

    • 現象：記憶體佔用高，但 Total_Executions 卻是 0。
    • 原因：這代表該 SQL 解析失敗 (Failed Parse) 或解析後未被執行。頻繁的解析失敗同樣會消耗 Shared Pool 資源，必須檢查應用程式邏輯。

可以將這段 SQL 得到的結果與 AWR Shared Pool Statistics 趨勢圖 比對。

  • 如果 SP Memory Usage % 持續攀升，且這份名單中的前幾名 SQL 的 Peak_Mem 也在增加，那就是你必須優先優化的對象。
  • 如果 SQL 重用率 (% SQL w/exec>1) 下降，應回頭檢查是否有名單外的大量一次性 SQL (Literal SQL) 正在污染 Shared Pool。

AWR 每小時 TOP SQL 記憶體對比分析
#

這段查詢會按小時分組，並列出每小時記憶體佔用前 5 名的 SQL，方便觀察「排名變動」。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

WITH snap_range AS (
    -- 1. 延用你的趨勢報表基礎：定義時間範圍
    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
),
sql_hourly_stats AS (
    -- 2. 按「小時」與「SQL_ID」進行彙整
    SELECT 
        TRUNC(sr.begin_interval_time, 'HH24') as start_hour,
        s.sql_id,
        -- 取該小時內最大的共享記憶體與版本數
        MAX(s.sharable_mem) / 1024 / 1024 as max_mem_mb,
        MAX(s.version_count) as max_versions,
        SUM(s.executions_delta) as hourly_execs
    FROM dba_hist_sqlstat s
    JOIN snap_range sr ON s.snap_id = sr.snap_id 
                      AND s.dbid = sr.dbid 
                      AND s.instance_number = sr.instance_number
    GROUP BY TRUNC(sr.begin_interval_time, 'HH24'), s.sql_id
),
ranked_sql AS (
    -- 3. 為每小時內的 SQL 進行內部排名
    SELECT 
        start_hour,
        sql_id,
        max_mem_mb,
        max_versions,
        hourly_execs,
        ROW_NUMBER() OVER (PARTITION BY start_hour ORDER BY max_mem_mb DESC) as rnk
    FROM sql_hourly_stats
)
-- 4. 最終輸出：每小時對比格式
SELECT 
    TO_CHAR(start_hour, 'YYYY-MM-DD HH24:MI') as "Analysis_Hour",
    rnk as "Rank",
    r.sql_id,
    ROUND(r.max_mem_mb, 2) as "Mem (MB)",
    r.max_versions as "Versions",
    r.hourly_execs as "Execs",
    DBMS_LOB.SUBSTR(st.sql_text, 100, 1) as "SQL_Snippet"
FROM ranked_sql r
LEFT JOIN dba_hist_sqltext st ON r.sql_id = st.sql_id
WHERE rnk <= 5 -- 每小時僅顯示前 5 名，方便對比
ORDER BY start_hour DESC, rnk ASC;

當你拿到這份「按小時對比」的報表時，請重點觀察以下三種現象：

  1. 「常駐型」霸榜 (The Consistent Monster):
  • 現象：某個 SQL_ID 在 24 小時內幾乎每一小時都在 Rank 1。
  • 診斷：這是該系統的核心負載來源。如果它的 Mem (MB) 很大且 Versions 很高，說明存在系統性的不共享問題。
  1. 「突發型」閃現 (The Transient Spike):
  • 現象：某個 SQL_ID 只出現在凌晨 2 點，且直接佔用 200MB 記憶體，隨後消失。
  • 診斷：這通常是特定的批次作業（Batch Job）或備份排程觸發的 SQL。這種「短暫殺手」在每日總結報表中容易被稀釋，但在小時報表中會原形畢露。
  1. 「版本爬升」現象 (Version Creep):
  • 現象：同一個 SQL_ID 的 Mem (MB) 與 Versions 隨著時間緩慢爬升（例如 10:00 是 10MB，15:00 變成 80MB）。
  • 診斷：這代表該 SQL 的 Child Cursors 持續無法被重用，Shared Pool 的壓力會越來越大，直到觸發 ORA-04031 或 Instance 重啟為止。

執行結果

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

Analysis_Hour   |Rank|SQL_ID       |Mem (MB)|Versions|Execs|SQL_Snippet                                                                                         |
----------------+----+-------------+--------+--------+-----+----------------------------------------------------------------------------------------------------+
2026-01-23 21:00|   1|f9dcfdnuhy9z1|    2.12|       2|   35|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 21:00|   2|7bxwkbxnsg0qy|     2.1|       2|    1|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 21:00|   3|8fhz3a6jurk3u|    1.06|       2|    2|SELECT  OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME, TEMPORARY FROM ALL_OBJECTS WHERE OBJECT_TYPE='P|
2026-01-23 21:00|   4|2qwrv481mqbrk|    0.54|       1|    1|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 21:00|   5|3dbzmtf9ahvzt|    0.22|       2|  139|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 20:00|   1|3dbzmtf9ahvzt|    0.22|       2|  129|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 20:00|   2|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 20:00|   3|c3utnxsnrx8tk|    0.18|       3|    4|update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:|
2026-01-23 20:00|   4|c179sut1vgpc8|    0.13|       1|   12|INSERT /*+  LEADING(@"SEL$F5BB74E1" "H"@"SEL$2" "A"@"SEL$1")  USE_NL(@"SEL$F5BB74E1" "A"@"SEL$1")   |
2026-01-23 20:00|   5|6abthk1u14yb7|    0.11|       3|    1|SELECT VERSION FROM V$INSTANCE                                                                      |
2026-01-23 19:00|   1|121ffmrc95v7g|    0.31|       4|    2|select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.ma|
2026-01-23 19:00|   2|3dbzmtf9ahvzt|    0.22|       2|  137|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 19:00|   3|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 19:00|   4|c3utnxsnrx8tk|    0.18|       3|    3|update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:|
2026-01-23 19:00|   5|g0t052az3rx44|    0.17|       4|    2|select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINA|
2026-01-23 18:00|   1|6ymxhj8mfgb46|    0.57|       1|    1|select sum(bytes)/1024.0 from dba_segments where owner = 'SYS' and (segment_name like 'WRI$_OPTSTAT_|
2026-01-23 18:00|   2|ct9ppzr6uuzv9|    0.39|       1|    1|select owner, segment_name, nvl(sum(blocks), 0)   from dba_segments  where tablespace_name = :tsname|
2026-01-23 18:00|   3|8cpwjpm11v8wu|    0.24|       1|    1|SELECT /*+ ordered full(t) full(o) use_hash(o) ¶               OPT_PARAM('_parallel_syspls_obey_forc|
2026-01-23 18:00|   4|3dbzmtf9ahvzt|    0.22|       2|  138|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 18:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 17:00|   1|f9dcfdnuhy9z1|    2.12|       2|    8|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 17:00|   2|7bxwkbxnsg0qy|     2.1|       2|    1|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 17:00|   3|8fhz3a6jurk3u|    1.06|       2|    2|SELECT  OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME, TEMPORARY FROM ALL_OBJECTS WHERE OBJECT_TYPE='P|
2026-01-23 17:00|   4|8z6jf4nswsn2v|    0.65|       1|    1|SELECT IOE.INDEX_OBJECT_ID IDX_OBJ#, IOE.INDEX_OWNER IDX_OWNER, IOE.INDEX_NAME IDX_NAME, IOE.INDEX_S|
2026-01-23 17:00|   5|121ffmrc95v7g|    0.31|       4|    1|select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.ma|
2026-01-23 15:00|   1|9yv5dwv8k0awg|    4.17|       1|    3|WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, |
2026-01-23 15:00|   2|f9dcfdnuhy9z1|    2.12|       2|   22|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 15:00|   3|7bxwkbxnsg0qy|     2.1|       2|    2|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 15:00|   4|atwuyuvqkf27w|       2|       1|    3|SELECT /*+ OPT_PARAM('_fix_control' '16391176:1') */ GROUP_TYPE, BUCKET_START, BUCKET_END, TM_GROUP_|
2026-01-23 15:00|   5|8fhz3a6jurk3u|    1.06|       2|    2|SELECT  OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME, TEMPORARY FROM ALL_OBJECTS WHERE OBJECT_TYPE='P|
2026-01-23 14:00|   1|7nght4ar0wrjs|    0.53|       1|    1|insert into AWR_PERF_TREND_HISTORY(¶SNAP_ID, ¶    INSTANCE_NUMBER, ¶    BEGIN_TIME,¶    AAS, ¶    LR|
2026-01-23 14:00|   2|121ffmrc95v7g|    0.24|       3|   18|select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.ma|
2026-01-23 14:00|   3|3dbzmtf9ahvzt|    0.22|       2|  427|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 14:00|   4|9t6y0bs2fj7xg|    0.21|       1|    4| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 14:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 13:00|   1|f9dcfdnuhy9z1|    2.12|       2|   29|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 13:00|   2|7bxwkbxnsg0qy|     2.1|       2|    2|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 13:00|   3|8fhz3a6jurk3u|    1.06|       2|    2|SELECT  OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME, TEMPORARY FROM ALL_OBJECTS WHERE OBJECT_TYPE='P|
2026-01-23 13:00|   4|8rz77380vz6yg|    0.95|       2|    2|WITH snap_range AS (¶    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_ti|
2026-01-23 13:00|   5|4jmfm820f6hd8|    0.53|       1|    1|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 12:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 12:00|   2|3dbzmtf9ahvzt|    0.22|       2|  415|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 12:00|   3|6wm3n4d7bnddg|    0.22|       1|    1| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 12:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 12:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 11:00|   1|9yv5dwv8k0awg|    4.17|       1|    1|WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, |
2026-01-23 11:00|   2|ggh55rhz95kyj|    3.57|       1|    1|SELECT AUDIT_OPTION_TYPE, COUNT(DISTINCT POLICY_NAME) POL_CNT FROM AUDIT_UNIFIED_POLICIES GROUP BY A|
2026-01-23 11:00|   3|atwuyuvqkf27w|       2|       1|    1|SELECT /*+ OPT_PARAM('_fix_control' '16391176:1') */ GROUP_TYPE, BUCKET_START, BUCKET_END, TM_GROUP_|
2026-01-23 11:00|   4|bqwq0b40tp15k|    1.07|       1|    1|with bracket as (¶             select /*+ materialize */ x.* from¶                    (select dense_|
2026-01-23 11:00|   5|dvu40a9avazf8|    0.94|       1|    2|select xmlagg(¶                        xmlelement("operation", ¶                          xmlattribu|
2026-01-23 10:00|   1|2qwrv481mqbrk|    0.54|       1|    1|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 10:00|   2|0vv9gu44zt1pv|    0.53|       1|    3|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 10:00|   3|4jmfm820f6hd8|    0.53|       1|    3|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 10:00|   4|1xk038qvudzmy|    0.47|       1|    1|WITH snap_range AS (¶    SELECT snap_id, dbid, instance_number, begin_interval_time, end_interval_ti|
2026-01-23 10:00|   5|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 09:00|   1|f9dcfdnuhy9z1|    2.11|       2|   39|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 09:00|   2|7bxwkbxnsg0qy|     2.1|       2|    2|SELECT  O.*,¶t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t.PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t|
2026-01-23 09:00|   3|8fhz3a6jurk3u|    1.04|       2|    2|SELECT  OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME, TEMPORARY FROM ALL_OBJECTS WHERE OBJECT_TYPE='P|
2026-01-23 09:00|   4|4jmfm820f6hd8|    0.53|       1|    9|SELECT  DISTINCT OWNER,OBJECT_NAME,OBJECT_TYPE FROM (¶SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_|
2026-01-23 09:00|   5|fr3r2ky8d6zqw|    0.44|       1|    1|SELECT 1 FROM ALL_ALL_TABLES WHERE 1<>1                                                             |
2026-01-23 08:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 08:00|   2|3dbzmtf9ahvzt|    0.22|       2|  406|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 08:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 08:00|   4|9t6y0bs2fj7xg|    0.21|       1|    2| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 08:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 07:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 07:00|   2|3dbzmtf9ahvzt|    0.22|       2|  416|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 07:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 07:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 07:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 06:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 06:00|   2|3dbzmtf9ahvzt|    0.22|       2|  418|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 06:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 06:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 06:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 05:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 05:00|   2|3dbzmtf9ahvzt|    0.22|       2|  515|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 05:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 05:00|   4|9t6y0bs2fj7xg|    0.21|       1|    2| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 05:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 04:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 04:00|   2|3dbzmtf9ahvzt|    0.22|       2|  413|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 04:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 04:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 04:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 03:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 03:00|   2|3dbzmtf9ahvzt|    0.22|       2|  417|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 03:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 03:00|   4|9t6y0bs2fj7xg|    0.21|       1|    2| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 03:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 02:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 02:00|   2|3dbzmtf9ahvzt|    0.22|       2|  422|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 02:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 02:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 02:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-23 01:00|   1|3wrrjm9qtr2my|     1.2|       1|    2|SELECT T.CLIENT_ID,         T.OPERATION_ID,         T.TARGET_TYPE,         T.TARGET_NAME,         T.|
2026-01-23 01:00|   2|bkryyh4vf4p55|    0.54|       1|    1|SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = :B1 AND OWNER ='ORACLE_OCM'                |
2026-01-23 01:00|   3|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 01:00|   4|3dbzmtf9ahvzt|    0.22|       2|  423|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 01:00|   5|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 00:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-23 00:00|   2|3dbzmtf9ahvzt|    0.22|       2|  421|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-23 00:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-23 00:00|   4|9t6y0bs2fj7xg|    0.21|       1|    2| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-23 00:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-22 23:00|   1|9sg6u8xys290z|    0.39|       2|    1|select count(*) num_enabled, sum(case optimizer_stats when 'ENABLED' then 1 else 0 end) stats_enable|
2026-01-22 23:00|   2|3dbzmtf9ahvzt|    0.22|       2|  522|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-22 23:00|   3|6wm3n4d7bnddg|    0.22|       1|    6| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-22 23:00|   4|9t6y0bs2fj7xg|    0.21|       1|    6| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-22 23:00|   5|3kqrku32p6sfn|    0.19|       1|    4|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-22 22:00|   1|3dbzmtf9ahvzt|    0.22|       2|  209|merge /* KSXM:OPTIM_DML_INF */  into sys.mon_mods_all$ m                   using dual               |
2026-01-22 22:00|   2|6wm3n4d7bnddg|    0.22|       1|    3| SELECT source,        (case when time_secs < 1 then 1 else time_secs end) as time_secs,        oper|
2026-01-22 22:00|   3|9t6y0bs2fj7xg|    0.21|       1|    2| select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0|
2026-01-22 22:00|   4|3kqrku32p6sfn|    0.19|       1|    2|MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SEL|
2026-01-22 22:00|   5|dg0yzvqbxwkps|    0.19|       2|    6|SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name, dbin.host_name, dbin.version, |

TOP
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

WITH snap_range AS (
    -- 1. 鎖定時間範圍與基本資訊
    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
),
sql_hourly_stats AS (
    -- 2. 彙整各類效能指標，確保傳遞 dbid
    SELECT 
        TRUNC(sr.begin_interval_time, 'HH24') as start_hour,
        s.sql_id,
        s.dbid, 
        s.snap_id,s.instance_number,
        SUM(s.elapsed_time_delta) / 1000000 as total_elapsed_sec,
        SUM(s.cpu_time_delta) / 1000000     as total_cpu_sec,
        SUM(s.iowait_delta) / 1000000       as total_iowait_sec,
        SUM(s.buffer_gets_delta)            as total_gets,
        SUM(s.disk_reads_delta)             as total_reads,
        -- 考慮版本相容性的 Unoptimized Reads 邏輯
        SUM(s.disk_reads_delta - NVL(s.optimized_physical_reads_delta, 0)) as unoptimized_reads,
        SUM(s.executions_delta)             as total_execs,
        SUM(s.parse_calls_delta)            as total_parses,
        MAX(s.sharable_mem) / 1024 / 1024   as max_mem_mb,
        MAX(s.version_count)                as max_versions
    FROM dba_hist_sqlstat s
    JOIN snap_range sr ON s.snap_id = sr.snap_id AND s.dbid = sr.dbid AND s.instance_number = sr.instance_number
    GROUP BY TRUNC(sr.begin_interval_time, 'HH24'), s.sql_id, s.dbid,s.snap_id,s.instance_number
),
ranked_sql_el AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_elapsed_sec DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_cpu AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_cpu_sec DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_iowait AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_iowait_sec DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_gets AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_gets DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_reads AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_reads DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_upreads AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY unoptimized_reads DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_exe AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY total_execs DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_maxmb AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY max_mem_mb DESC) as rnk FROM sql_hourly_stats sh),
ranked_sql_vers AS (SELECT sh.*,ROW_NUMBER() OVER ( PARTITION BY start_hour ORDER BY max_versions DESC) as rnk FROM sql_hourly_stats sh)
select 
    t.t,
    t.dbid,
    t.snap_id,
    t.instance_number,
    TO_CHAR(t.start_hour, 'YYYY-MM-DD HH24:MI') as "Analysis_Hour",
    t.rnk as "Rank",
    t.sql_id,
    ROUND(t.total_elapsed_sec, 2) as "Elapsed(s)",
    ROUND(t.total_cpu_sec, 2)     as "CPU(s)",
    ROUND(t.total_iowait_sec, 2)  as "IOWait(s)",
    t.total_gets                  as "Gets",
    t.total_reads                 as "Reads",
    t.unoptimized_reads           as "UnOpt_Reads",
    t.total_execs                 as "Execs",
    t.total_parses                as "Parses",
    ROUND(t.max_mem_mb, 2)        as "Mem(MB)",
    t.max_versions                as "Vers",
    DBMS_LOB.SUBSTR(st.sql_text, 100, 1) as "SQL_Snippet"
from
(
select 'total_elapsed_sec' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_el r WHERE r.rnk <= 5 
union all
select 'total_cpu_sec' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_cpu r WHERE r.rnk <= 5
union all
select 'total_iowait_sec' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_iowait r WHERE r.rnk <= 5
union all
select 'total_gets' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_gets r WHERE r.rnk <= 5
union all
select 'total_reads' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_reads r WHERE r.rnk <= 5
union all
select 'unoptimized_reads' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_upreads r WHERE r.rnk <= 5
union all
select 'total_execs' t,  r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_exe r WHERE r.rnk <= 5
union all
select 'max_mem_mb' t,  r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_maxmb r WHERE r.rnk <= 5
union all
select 'max_versions' t, r.dbid,r.snap_id,r.instance_number,r.start_hour,r.rnk,r.sql_id,r.total_elapsed_sec,r.total_cpu_sec,r.total_iowait_sec,r.total_gets,r.total_reads,r.unoptimized_reads,r.total_execs,r.total_parses,r.max_mem_mb,r.max_versions from ranked_sql_vers r WHERE r.rnk <= 5
) t
LEFT JOIN dba_hist_sqltext st ON t.sql_id = st.sql_id AND t.dbid = st.dbid
ORDER BY t.t, t.start_hour DESC, t.rnk ASC;

特定 SQL 的執行效能趨勢 (Performance Drift)
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT 
    s.snap_id,
    sn.begin_interval_time,
    s.sql_id,
    s.plan_hash_value, -- 觀察執行計畫是否有變動
    s.executions_delta as execs,
    ROUND(s.elapsed_time_delta / 1000000, 2) as elapsed_sec,
    -- 每次執行的邏輯讀取（判斷索引效率）
    ROUND(s.buffer_gets_delta / NULLIF(s.executions_delta, 0), 2) as gets_per_exec,
    -- 每次執行的物理讀取（判斷 I/O 壓力）
    ROUND(s.disk_reads_delta / NULLIF(s.executions_delta, 0), 2) as reads_per_exec,
    -- 每次執行的 CPU 時間
    ROUND(s.cpu_time_delta / 1000000 / NULLIF(s.executions_delta, 0), 2) as cpu_per_exec
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id
WHERE s.sql_id = '&target_sql_id' -- 輸入你想追蹤的 SQL_ID
ORDER BY s.snap_id;

執行結果範例

1
2
3
4

SNAP_ID|BEGIN_INTERVAL_TIME    |SQL_ID       |PLAN_HASH_VALUE|EXECS|ELAPSED_SEC|GETS_PER_EXEC|READS_PER_EXEC|CPU_PER_EXEC|
-------+-----------------------+-------------+---------------+-----+-----------+-------------+--------------+------------+
   1688|2026-01-23 15:10:24.872|1m0ycv6wsty7n|     2935435819|    1|       2.31|        23277|            48|        2.24|
   1688|2026-01-23 15:10:24.872|1m0ycv6wsty7n|      540301959|    1|      59.67|      6681350|           663|       58.55|

解決 SQL 議題方向
#

議題分類 議題項目 核心問題點 解決與優化方向
1. 時間與負載議題 (Time-Based) Elapsed Time 總執行時間最長，是 DB Time 的主要貢獻者。 區分是 CPU 密集還是等待密集。若 CPU 低而 Elapsed 高，應優先拆解 Wait Class（如 I/O 或 Lock）。
CPU Time 消耗過多運算資源。 1. 減少 Logical Reads (Gets)，因為 CPU 主要花在處理資料塊。 2. 檢查是否有複雜的運算、格式轉換或低效的 PL/SQL 函數。
User I/O Wait SQL 花費大量時間等待從磁碟讀取資料。 1. 索引優化：減少 Full Table Scan。 2. 緩存優化：評估 Buffer Cache 是否不足。 3. 儲存效能：檢查實體磁碟回應速度。
2. 資源消耗議題 (Resource-Based) Gets (Logical Reads) 邏輯讀取數過高，是系統 CPU 壓力的源頭。 SQL 效率檢核：檢查每個事務（Transaction）讀取的資料塊是否穩定。若 Logical Reads/Tx 飆升，通常是執行計畫走鐘（如索引失效）。
Reads (Physical Reads) 實體磁碟讀取，效能代價極高。 1. 提升 Buffer Hit %。 2. 考慮使用 Oracle In-Memory 或是將熱點表置入 Keep Pool。
Physical Reads (UnOptimized) 未經優化（如繞過快取）的直接讀取。 1. 檢查是否發生大量的 Direct Path Reads（如大型排序或平行查詢）。 2. 評估是否需要增加實體記憶體以減少 Disk Spill。
3. 應用程式與解析議題 (App & Parsing) Executions 執行頻率極高。 1. 檢查應用程式是否有無效的 Loop 呼叫。 2. 評估是否能合併請求或是使用 Result Cache 緩存結果。
Parse Calls 解析次數過高，消耗大量 CPU。 1. 確保 Soft Parse % 接近 100%。 2. 若 Execute to Parse % 過低，代表程式未保持 Cursor 開放，應優化連線池與語句緩存。
Sharable Memory 單一 SQL 在 Shared Pool 佔用過多空間。 1. 避免在 SQL 中寫入超長列表（如 IN (1, 2, …, 1000)）。 2. 監控 Shared Pool 使用率，防止碎片化導致 ORA-04031。
Version Count 同一 SQL 產生過多子游標 (Child Cursors)。 1. 檢查是否因 Bind Mismatch 或環境參數差異引起。 2. 監控 Adaptive Cursor Sharing (ACS) 的行為。

找原因工具
#

Shared Pool 中「總記憶體佔用前十名」且「未綁定變數」清單
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

SELECT * FROM (
    SELECT 
        force_matching_signature,
        -- 1. 計算這個邏輯結構產生了多少個不同的 SQL_ID (變體數量)
        COUNT(DISTINCT sql_id) as "Distinct_SQL_IDs",
        -- 2. 累計這些變體總共佔用的記憶體 (MB)
        ROUND(SUM(sharable_mem) / 1024 / 1024, 2) as "Total_Mem_MB",
        -- 3. 累計總執行次數
        SUM(executions) as "Total_Executions",
        -- 4. 取出其中一個 SQL 作為範例，觀察其字面值 (Literal)
        MAX(SUBSTR(sql_text, 1, 200)) as "Sample_SQL_Text"
    FROM v$sql
    -- 過濾掉無法計算簽名的 SQL (如部分內部指令)
    WHERE force_matching_signature <> 0
    GROUP BY force_matching_signature
    -- 過濾條件：至少有 5 個以上的變體才視為「未綁定變數」的候選人
    HAVING COUNT(DISTINCT sql_id) > 5
    -- 依據總佔用記憶體排序
    ORDER BY "Total_Mem_MB" DESC
) WHERE ROWNUM <= 10

為什麼 SQL 會佔用過多記憶體？
#

當發現單一 SQL 的 Sharable_Mem 超過 100MB 或 Version Count 異常高時，通常有以下原因：

  1. 超大型 IN 清單：
  • 例如 WHERE id IN (1, 2, ..., 5000)。Oracle 必須解析並儲存這 5000 個值，這會讓 SQL 文本與解析樹變得極大，直接灌爆 Sharable_Mem。
  • 解決方向：改用暫存表 (Global Temporary Table) 進行關聯，或將清單拆小。
  1. 高版本數量 (High Version Count)：
  • 雖然單一子游標（Child Cursor）可能不大，但如果同一個 SQL_ID 因為環境不同、綁定變數長度不一而產生了數百個版本，總體記憶體就會累積得很恐怖。
  • 解決方向：檢查 V$SQL_SHARED_CURSOR 來找出為什麼無法共享游標的原因（例如 BIND_MISMATCH）。
  1. 大數據量的 PL/SQL 內嵌 SQL：
  • 如果在 PL/SQL 中動態拼湊極長的 SQL 語句，也會導致記憶體過度消耗。

單一 SQL 語句在 Shared Pool 中佔用了多少記憶體
#

即時查詢：目前在庫（Library Cache）中的 SQL 記憶體
# 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT 
    sql_id,
    child_number,
    plan_hash_value,
    -- 核心指標：Sharable Memory (轉換為 MB)
    ROUND(sharable_mem / 1024 / 1024, 2) as "Sharable_Mem (MB)",
    -- 額外參考：持續性記憶體與執行時記憶體
    ROUND(persistent_mem / 1024 / 1024, 2) as "Persistent_Mem (MB)",
    ROUND(runtime_mem / 1024 / 1024, 2) as "Runtime_Mem (MB)",
    users_opening,
    executions,
    substr(sql_text, 1, 100) as sql_snippet
FROM v$sql
WHERE sql_id = '&target_sql_id' -- 輸入你想查詢的 SQL_ID
ORDER BY child_number
  • Sharable_Mem: 這是最主要的指標，代表該 SQL 及其執行計畫在 Shared Pool 中共享的大小。
  • Persistent_Mem: 存放在執行期間固定不動的資訊（如綁定變數資訊）。
  • Runtime_Mem: 執行該 SQL 時暫時需要的空間（如堆疊空間）。
歷史回溯：從 AWR 查詢過去的記憶體佔用趨勢
# 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT 
    s.snap_id,
    sn.begin_interval_time,
    s.sql_id,
    -- 取得該快照點時的最大共享記憶體
    ROUND(s.sharable_mem / 1024 / 1024, 2) as "Max_Sharable_Mem (MB)",
    s.version_count as "Versions",
    s.executions_delta as "Execs_Delta"
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number
WHERE s.sql_id = '&target_sql_id'
ORDER BY s.snap_id DESC;