DBA 日常診斷必備：參數修改、會話識別、登入用戶查詢核心指令集
掌握初始化參數動態調整、當前會話識別、所有登入用戶審視等核心查詢，是 Oracle DBA 日常運維與效能調優的基礎技能。本文彙整高頻指令並附參數說明。
初始化參數管理
| 操作 | 指令 | 範例 | 說明 |
|---|
| 動態修改參數 (記憶體即時生效) | ALTER SYSTEM SET param = value [SCOPE=MEMORY|SPFILE|BOTH]; | ALTER SYSTEM SET db_cache_size = 20M SCOPE=BOTH; | SCOPE=BOTH 同時修改記憶體與 SPFILE，持久化 |
| PL/SQL 查詢參數值 | SELECT * FROM v$parameter WHERE name = 'param_name'; | SELECT name, value, isdefault, isses_modifiable, issys_modifiable FROM v$parameter WHERE name = 'db_cache_size'; | v$parameter 為實例級目前值 |
| SQL*Plus 顯示參數 | SHOW PARAMETER param_name; | SHOW PARAMETER db_block_size; | 支援模糊匹配 |
| 查看 SPFILE 參數 | SELECT * FROM v$spparameter WHERE name LIKE 'param%'; | | 下次啟動生效的值 |
當前會話與用戶識別
| 需求 | 查詢指令 | 說明 |
|---|
| 當前用戶權限資訊 | SELECT * FROM user_tab_privs; | 當前用戶擁有的物件權限 |
| 當前 Session ID (SID) | SELECT sid FROM v$mystat WHERE rownum = 1; | v$mystat 只含當前 Session 統計，取首行即得 SID |
| 當前用戶名 | SELECT user FROM dual; | 最簡單取得登入用戶 |
| 所有登入 Session 詳細資訊 | SELECT sid, serial#, username, status, machine, program, logon_time, sql_id FROM v$session WHERE type='USER'; | 排除背景程序，聚焦用戶連線 |
| 特定 SID 完整資訊 | SELECT * FROM v$session WHERE sid = 154; | 含等待事件、SQL、鎖定物件等 |
| 當前執行 SQL 文本 | SELECT sql_text FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = 154); | 結合 v$session.sql_id 反查 SQL 全文 |
進階診斷組合查詢
長時間執行 SQL 排查
SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.logon_time,
sq.sql_text, sq.elapsed_time/1e6 elapsed_sec
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.type = 'USER'
AND s.status = 'ACTIVE'
AND sq.elapsed_time > 10e6 -- 超過 10 秒
ORDER BY sq.elapsed_time DESC;
鎖等待鏈分析
SELECT blocking_session, sid, serial#, username, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;
參數修改歷程追蹤 (11g+)
SELECT name, value, last_modified, issys_modifiable
FROM v$system_parameter
WHERE ismodified != 'FALSE'
ORDER BY last_modified DESC;
常用參數調優參考值
| 參數 | 建議範圍 | 調整建議 |
|---|
| DB_CACHE_SIZE | 實體記憶體 40-60% | 配合 DB_16K_CACHE_SIZE 等多區塊大小 |
| PGA_AGGREGATE_TARGET | 實體記憶體 20-30% | 11g+ 建議啟用 MEMORY_TARGET 自動管理 |
| PROCESSES | 預估最大連線 * 1.5 | 含背景程序、並行查詢奴隸程序 |
| OPEN_CURSORS | 500-3000 | 防止 ORA-01000，應用端需關閉游標 |
| UNDO_RETENTION | 900-3600 秒 | 配合 UNDO 表空間大小，支援閃回查詢 |
操作提醒
- 動態修改參數前先查詢
ISSYS_MODIFIABLE 確認是否支援線上修改
SCOPE=SPFILE 僅修改參數檔，需重啟生效；SCOPE=MEMORY 僅記憶體生效，重啟失效- 關鍵參數變更必須記錄變更單、通知相關方、驗證效能影響
- 定期比對
V$PARAMETER 與 V$SPPARAMETER 發現差異
熟練這組核心查詢指令，讓 DBA 從「被動救火」轉向「主動診斷」。
