為何需要新時間型別
DATE 精度僅至秒，無法表達毫秒/微秒NUMBER 存秒數計算困難 (60/60/24 非十進制)- SQL:1999 標準引入
INTERVAL 與 TIMESTAMP 系列解決上述問題
核心時間型別
| 型別 | 精度 | 時區感知 | 用途 |
|---|
| DATE | 秒 | 否 | 相容舊系統 |
| TIMESTAMP(fsp) | 微秒 (fsp 0-9, 預設 6) | 否 | 高精度時間點 |
| TIMESTAMP(fsp) WITH TIME ZONE | 微秒 | 是 (儲存位移) | 跨時區應用 |
| TIMESTAMP(fsp) WITH LOCAL TIME ZONE | 微秒 | 自動轉換 (資料庫/會話時區) | 全球化系統首選 |
| INTERVAL YEAR(p) TO MONTH | 月 | 否 | 年月間隔 |
| INTERVAL DAY(p) TO SECOND(fsp) | 微秒 | 否 | 天/時/分/秒間隔 |
INTERVAL 語法與運算
-- 字面值
INTERVAL '2-6' YEAR TO MONTH -- 2 年 6 月
INTERVAL '10 12:30:45.123' DAY TO SECOND -- 10 天 12 時 30 分 45.123 秒
-- 運算範例
SELECT SYSDATE + INTERVAL '1' DAY FROM dual; -- 明天
SELECT SYSTIMESTAMP + INTERVAL '30' MINUTE FROM dual; -- 30 分鐘後
SELECT (end_time - start_time) DAY TO SECOND FROM jobs; -- 執行間隔
-- 精度控制
INTERVAL DAY(3) TO SECOND(6) -- 天數最多 3 位數，秒精度 6 位小數
時間轉換函數
-- TO_DATE: 字串 → DATE (無微秒)
TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS')
-- TO_TIMESTAMP: 字串 → TIMESTAMP
TO_TIMESTAMP('2024-01-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')
-- TO_CHAR: 時間 → 字串
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR')
-- CAST: 型別轉換
CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE)
目前時間取得
-- 資料庫伺服器時間 (DATE)
SELECT SYSDATE FROM dual;
-- 會話時區時間 (TIMESTAMP WITH TIME ZONE)
SELECT CURRENT_TIMESTAMP FROM dual;
-- 指定精度
SELECT CURRENT_TIMESTAMP(3) FROM dual; -- 毫秒
-- 格式化輸出
SELECT TO_CHAR(CURRENT_TIMESTAMP(6), 'DD-MON-YYYY HH24:MI:SS.FF6') FROM dual;
效能監控中的時間應用
等待時間計算
-- V$SQL 統計
ELAPSED_TIME - CPU_TIME = 等待時間 (微秒)
-- V$SYSTEM_EVENT 實例級等待
SELECT event, total_waits, time_waited_micro
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC;
操作系統層級時間
vmstat 1 / iostat -x 1 / sar -u 1：CPU/IO 佔用、佇列長度、等待百分比- Oracle
V$OSSTAT / V$SYSMETRIC 整合 OS 指標
精確追蹤 SQL 執行時間
-- 1. SQL Trace (10046)
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- 執行 SQL
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- tkprof 分析 trace 檔
-- 2. DBMS_MONITOR (11g+)
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 123, serial_num => 456, waits => TRUE, binds => TRUE);
-- 執行 SQL
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 123, serial_num => 456);
最佳實踐建議
- 新系統全面採用 TIMESTAMP WITH LOCAL TIME ZONE，避免時區 Bug
- 間隔運算使用 INTERVAL DAY TO SECOND，語意清晰、自動處理進位
- 避免在 WHERE 條件對時間欄位使用函數 (如
TRUNC(created) = SYSDATE) → 改用範圍查詢 created >= TRUNC(SYSDATE) AND created < TRUNC(SYSDATE+1) 利用索引
- 統計資訊收集包含時間欄位直方圖：
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
- 效能分析以「時間」為核心指標 (DB Time, Elapsed Time, Wait Time)，而非比率 (Hit Ratio)
