SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    q.sql_text,
    s.last_call_et AS "Seconds_Running",  -- 當(dāng)前操作已運行秒數(shù)
    q.elapsed_time / 1000000 AS "Elapsed_Sec_Total",  -- 該SQL總耗時（秒）
    q.cpu_time / 1000000 AS "CPU_Sec"
FROM 
    v$session s
JOIN 
    v$sql q ON s.sql_id = q.sql_id
WHERE 
    s.status = 'ACTIVE'
    AND s.username IS NOT NULL
    AND s.type != 'BACKGROUND';

SELECT 
    sql_id,
    substr(sql_text, 1, 100) AS sql_preview,
    executions AS execs,
    ROUND(elapsed_time / 1000000, 2) AS total_sec,
    ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 4) AS avg_sec,
    ROUND(cpu_time / NULLIF(executions, 0) / 1000000, 4) AS avg_cpu_sec,
    last_active_time
FROM 
    v$sql
WHERE 
    executions > 0
    AND last_active_time > SYSDATE - 1  -- 過去24小時
ORDER BY 
    elapsed_time DESC;

-- 需要知道快照 ID（snap_id）
SELECT * FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.select_sql(
        begin_snap => 12345,
        end_snap   => 12346,
        sql_id     => NULL  -- NULL 表示返回 Top SQL
    )
);

-- 生成 HTML 或文本格式的 AWR 報告（需 DBA 權(quán)限）
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

ALTER SESSION SET sql_trace = TRUE;
-- 或更詳細的 10046 trace（level 12 包含綁定變量和等待事件）
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM large_table WHERE ...;

ALTER SESSION SET sql_trace = FALSE;
-- 或
ALTER SESSION SET EVENTS '10046 trace name context off';

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

tkprof tracefile.trc output.txt explain=user/pass sort=exeela

-- 跟蹤某個客戶端程序（如 JDBC 應(yīng)用）
BEGIN
  DBMS_MONITOR.client_id_trace_enable(
    client_id => 'MY_APP_USER',
    waits     => TRUE,
    binds     => TRUE
  );
END;
/

SELECT 
    opname,
    target,
    sofar,
    totalwork,
    ROUND(sofar/totalwork*100, 2) AS pct_done,
    time_remaining,
    elapsed_seconds
FROM 
    v$session_longops
WHERE 
    totalwork > 0 
    AND sofar != totalwork;