oracle AWR TOP SQL

2026-01-15 Oracle Oracle AWR

以下我針對 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

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

執行結果範例

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 記憶體佔用分析

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;

執行結果範例

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|

高記憶體 + 低版本 (High Mem, Low Versions):
- 現象：Peak_Mem 超過 50-100MB，但 Max_Versions 只有個位數。
- 原因：這通常是 SQL 文本過長 造成的。最常見的是 IN 子句中包含了數千個參數，或是極其複雜的巢狀 CASE WHEN 邏輯。這會導致解析樹（Parse Tree）變得極其龐大。
高記憶體 + 高版本 (High Mem, High Versions):
- 現象：Peak_Mem 很高，同時 Max_Versions 超過 100 甚至上千。
- 原因：這是 子游標無法共享 (Cursor Non-sharing)。可能是因為不同 Session 的參數設定不同、綁定變數長度差異過大，或是觸發了 Adaptive Cursor Sharing。雖然每個版本可能不大，但累積起來會吃掉大量 Shared Pool。
高記憶體 + 零執行 (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，方便觀察「排名變動」。

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;

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

「常駐型」霸榜 (The Consistent Monster):

現象：某個 SQL_ID 在 24 小時內幾乎每一小時都在 Rank 1。
診斷：這是該系統的核心負載來源。如果它的 Mem (MB) 很大且 Versions 很高，說明存在系統性的不共享問題。

「突發型」閃現 (The Transient Spike):

現象：某個 SQL_ID 只出現在凌晨 2 點，且直接佔用 200MB 記憶體，隨後消失。
診斷：這通常是特定的批次作業（Batch Job）或備份排程觸發的 SQL。這種「短暫殺手」在每日總結報表中容易被稀釋，但在小時報表中會原形畢露。

「版本爬升」現象 (Version Creep):

現象：同一個 SQL_ID 的 Mem (MB) 與 Versions 隨著時間緩慢爬升（例如 10:00 是 10MB，15:00 變成 80MB）。
診斷：這代表該 SQL 的 Child Cursors 持續無法被重用，Shared Pool 的壓力會越來越大，直到觸發 ORA-04031 或 Instance 重啟為止。

執行結果

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, |

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

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;

執行結果範例

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 中「總記憶體佔用前十名」且「未綁定變數」清單

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 異常高時，通常有以下原因：

超大型 IN 清單：

例如 WHERE id IN (1, 2, ..., 5000)。Oracle 必須解析並儲存這 5000 個值，這會讓 SQL 文本與解析樹變得極大，直接灌爆 Sharable_Mem。
解決方向：改用暫存表 (Global Temporary Table) 進行關聯，或將清單拆小。

高版本數量 (High Version Count)：

雖然單一子游標（Child Cursor）可能不大，但如果同一個 SQL_ID 因為環境不同、綁定變數長度不一而產生了數百個版本，總體記憶體就會累積得很恐怖。
解決方向：檢查 V$SQL_SHARED_CURSOR 來找出為什麼無法共享游標的原因（例如 BIND_MISMATCH）。

大數據量的 PL/SQL 內嵌 SQL：

如果在 PL/SQL 中動態拼湊極長的 SQL 語句，也會導致記憶體過度消耗。

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

即時查詢：目前在庫（Library Cache）中的 SQL 記憶體

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 查詢過去的記憶體佔用趨勢

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;

oracle AWR TOP SQL

2026-01-15 Oracle Oracle AWR

尋找方式

1. SQL ordered by TOP Event

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

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

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

解決 SQL 議題方向

找原因工具

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

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

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

即時查詢：目前在庫（Library Cache）中的 SQL 記憶體

歷史回溯：從 AWR 查詢過去的記憶體佔用趨勢

Relevant Topics

oracle AWR PERF TREND HISTORY

2026-01-15 Oracle Oracle AWR

Oracle AWR 常用筆記

2026-01-02 Oracle Oracle AWR

Redo test

2025-12-29 工作雜記 Oracle

Oracle Clusterware 初學

2024-07-03 Oracle Oracle

Oracle OCFS2 初學

2024-07-03 工作雜記 Oracle

Recent Posts

CNS11643 轉 UTF8 經驗

2024-03-21 工作雜記 CNS11643 Java

db-postgresql-note

2024-03-15 工作雜記 postgresql

docker-note

2024-03-15 工作雜記 docker

關閉IPv6

2024-02-28 工作雜記 Linux

輸入法相關

2024-02-28 工作雜記 Linux