Mục đích chính:
+ Phát hiện SQL_ID / Session đang gây “nóng” hệ thống theo thời gian thực (đặc biệt trong các đợt peak).
+ Ưu tiên xử lý theo mức độ ảnh hưởng: CPU cao → IO nặng → wait bất thường → blocking/lock.
Nguyên tắc an toàn:
+ Ưu tiên kill session bằng Oracle: ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;
+ Chỉ dùng kill -9 khi session bị “treo”/không chết sau kill Oracle (hoặc PMON không dọn).
+ Không kill nhầm: Lọc SYS, background, job hệ thống, GoldenGate, RMAN…
-- Top SQL cost cao + chạy chậm (avg > 1s) trong ngày
SELECT
inst_id,
sql_id,
module,
parsing_schema_name AS username,
optimizer_cost AS cost,
executions,
ROUND(elapsed_time / GREATEST(executions,1) / 1e6, 2) AS avg_sec_per_exec,
sql_fulltext AS sql_text
FROM gv$sqlarea
WHERE optimizer_cost > 2000000
AND last_load_time >= TRUNC(SYSDATE)
AND executions >= 1
AND (elapsed_time / GREATEST(executions,1) / 1e6) > 1
AND parsing_schema_name NOT LIKE '%SYS%'
ORDER BY optimizer_cost DESC;
2. Lấy danh sách session đang chạy các SQL_ID top ở phần 1 và xuất luôn lệnh kill
-- Session đang chạy các SQL_ID top -> xuất kill_cmd (Oracle) + spid (OS)
SELECT
a.inst_id,
a.sid,
a.serial#,
a.sql_id,
a.username,
a.status,
a.schemaname,
a.osuser,
a.machine,
a.program,
a.logon_time,
a.event,
b.spid,
'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;' AS kill_cmd,
'kill -9 '||b.spid AS kill_os_cmd
FROM gv$session a
JOIN gv$process b
ON b.addr = a.paddr AND b.inst_id = a.inst_id
WHERE a.type = 'USER'
AND a.sql_id IN (
SELECT sql_id
FROM gv$sqlarea
WHERE optimizer_cost > 2000000
AND last_load_time >= TRUNC(SYSDATE)
AND executions >= 1
AND (elapsed_time / GREATEST(executions,1) / 1e6) > 1
AND parsing_schema_name NOT LIKE '%SYS%'
)
-- lọc thêm nếu cần:
-- AND a.username NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
-- AND a.status='ACTIVE'
ORDER BY a.inst_id, a.sid;
3. Kill session
3.1. Kill theo SID/SERIAL/INST
Bạn copy cột kill_cmd ở phần 2 và chạy. Ví dụ: alter system kill session '13562,12345,@2' immediate;
3.2. Kill theo SPID OS
Bạn copy cột kill_os_cmd ở phần 2. Ví dụ: kill -9 123456
4. Theo dõi active / lock / blocking (đang gây treo)
4.1. Danh sách session ACTIVE + action (CPU hoặc WAIT) + SQL text (rút gọn)
SELECT DISTINCT
s.inst_id AS i#,
s.username,
s.sid,
s.serial#,
s.osuser,
s.machine,
DECODE(s.wait_time, 0, s.event, 'CPU') AS action,
s.sql_id,
s.logon_time,
SUBSTR(NVL(ss.sql_text, aa.name), 1, 1000) AS sqltext,
s.p1text, s.p1,
s.p2text, s.p2,
s.p3text, s.p3
FROM gv$session s
LEFT JOIN gv$sqlstats ss
ON s.inst_id = ss.inst_id AND s.sql_id = ss.sql_id
JOIN audit_actions aa
ON aa.action = s.command
WHERE s.type='USER'
AND s.status='ACTIVE'
AND s.username NOT IN ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE','GGADMIN')
AND LOWER(NVL(ss.sql_text,'x')) NOT LIKE LOWER('%***%')
ORDER BY s.username, s.sql_id;
4.2. Tổng active session theo user
-- Đếm active session theo user
SELECT username, COUNT(*) AS total_active
FROM gv$session
WHERE status='ACTIVE'
GROUP BY username
ORDER BY total_active DESC;
4.3. Tổng session theo user + status
SELECT username, status, COUNT(*) AS total_sessions
FROM gv$session
GROUP BY username, status
ORDER BY total_sessions DESC;
4.4. Danh sách session đang bị BLOCK / đang BLOCK người khác
SELECT
inst_id,
blocking_session,
sid,
serial#,
sql_id,
wait_class,
seconds_in_wait,
username,
status,
schemaname,
osuser,
machine,
program,
logon_time
FROM gv$session
WHERE blocking_session IS NOT NULL
AND type='USER'
ORDER BY inst_id;
4.5. Xuất nhanh list blocking_session
SELECT DISTINCT
''''||blocking_session||''',' AS blocker_sid_list
FROM gv$session
WHERE blocking_session IS NOT NULL
AND type='USER'
ORDER BY 1;
Ví dụ bạn lấy được list: '4162','9011','13562', thêm vào câu lệnh tìm chi tiết session để kill
SELECT a.inst_id, a.sid, a.serial#, a.username, a.sql_id,
'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;' AS kill_cmd
FROM gv$session a
WHERE a.type='USER'
AND a.sid IN (4162,9011,13562);
5. Tìm/kill theo SQL_ID hoặc SID cụ thể
5.1. Tìm process/session theo 1 SQL_ID cụ thể (có kill_cmd)
SELECT
a.inst_id, a.sid, a.serial#, a.sql_id, a.username, a.status,
a.machine, a.program, a.event,
b.spid,
' alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;' AS kill_cmd
FROM gv$session a
JOIN gv$process b
ON b.addr=a.paddr AND b.inst_id=a.inst_id
WHERE a.type='USER'
AND a.sql_id IN ('3nr64fnzfa84z')
ORDER BY a.inst_id, a.sid;
5.2. Kill theo SID (và inst_id nếu RAC)
SELECT
a.inst_id, a.sid, a.serial#, a.sql_id, a.username, a.status,
a.machine, a.program, a.event,
b.spid,
'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;' AS kill_cmd
FROM gv$session a
JOIN gv$process b
ON b.addr=a.paddr AND b.inst_id=a.inst_id
WHERE a.type='USER'
AND (a.inst_id, a.sid) IN ( (2, 4162) ); -- ví dụ
6. Xem full SQL text theo SQL_ID
SELECT sql_id, sql_fulltext
FROM gv$sql
WHERE sql_id IN ('184b2tbutgm99');
7. Kiểm tra Longops
LONGOPS (Oracle “Long Operations”) là cơ chế Oracle theo dõi những thao tác chạy lâu và có thể đo được tiến độ (progress), rồi ghi vào view V$SESSION_LONGOPS (RAC thì có GV$SESSION_LONGOPS)
Các nhóm hay gặp:
+ Full table scan / index fast full scan trên bảng lớn
+ Sort / hash join lớn (workarea lớn)
+ DML/DDL lớn: CREATE INDEX, REBUILD INDEX, MOVE TABLE, SHRINK, CTAS, INSERT /*+ APPEND */
+ Parallel Execution (PX) chạy khối lượng lớn
+ RMAN backup/restore
+ Data Pump (expdp/impdp) (một số pha)
+ Gather stats trên object lớn (một số trường hợp)
+ Một số GC/RAC hoặc thao tác I/O lớn khác (tùy phiên bản/engine)
Không phải mọi SQL chạy lâu đều có longops: nếu Oracle không đo được tiến độ thì sẽ không hiện.
Dưới đây là câu lệnh xem LONGOPS đang chạy:
+ Xem LONGOPS trên hệ thống RAC nhiều instance
SELECT
l.inst_id,
s.username,
s.machine,
s.program,
s.module,
s.sql_id,
l.sid,
l.serial#,
l.opname, -- đang làm gì
l.target, -- object/target
ROUND(l.sofar * 100 / NULLIF(l.totalwork,0), 2) AS pct_done,
l.elapsed_seconds,
l.time_remaining,
(l.elapsed_seconds + l.time_remaining) AS total_seconds,
l.message,
'alter system kill session '''||l.sid||','||l.serial#||',@'||l.inst_id||''' immediate;' AS kill_cmd
FROM gv$session_longops l
JOIN gv$session s
ON s.inst_id = l.inst_id
AND s.sid = l.sid
WHERE l.totalwork > 0
AND l.sofar < l.totalwork
AND l.time_remaining > 0
AND s.type = 'USER'
AND s.username NOT IN ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE','GGADMIN')
ORDER BY total_seconds DESC;
+ LONGOPS (1 instance) - đang chạy + có tiến độ + kill_cmd
SELECT
l.sid,
l.serial#,
s.username,
s.machine,
s.program,
s.sql_id,
l.opname, -- đang làm gì
l.target, -- object/target
ROUND(l.sofar*100/NULLIF(l.totalwork,0),2) AS pct_done,
l.elapsed_seconds,
l.time_remaining,
(l.elapsed_seconds + l.time_remaining) AS total_seconds,
l.message,
'alter system kill session '''||l.sid||','||l.serial#||''' immediate;' AS kill_cmd
FROM v$session_longops l
JOIN v$session s
ON s.sid = l.sid
WHERE l.totalwork > 0
AND l.sofar < l.totalwork
AND l.time_remaining > 0
AND s.type = 'USER'
AND s.username NOT IN ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE','GGADMIN')
ORDER BY total_seconds DESC;
+ Nếu muốn có SPID để kill OS (dự phòng cho tình huống khi kill session Oracle không ảnh hưởng)
SELECT
l.sid,
l.serial#,
s.username,
s.machine,
s.program,
s.sql_id,
p.spid,
l.opname,
l.target,
ROUND(l.sofar*100/NULLIF(l.totalwork,0),2) AS pct_done,
l.elapsed_seconds,
l.time_remaining,
'alter system kill session '''||l.sid||','||l.serial#||''' immediate;' AS kill_cmd,
'kill -9 '||p.spid AS kill_os_cmd
FROM v$session_longops l
JOIN v$session s
ON s.sid = l.sid
JOIN v$process p
ON p.addr = s.paddr
WHERE l.totalwork > 0
AND l.sofar < l.totalwork
AND l.time_remaining > 0
AND s.type='USER'
AND s.username NOT IN ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE','GGADMIN')
ORDER BY l.time_remaining DESC;
8. Scheduler jobs đang chạy
SELECT * FROM dba_scheduler_running_jobs;
9. DBA_JOBS đang chạy
SELECT * FROM dba_jobs_running;
10. Tìm session đang lock table / object
SELECT
v.inst_id,
v.sid,
v.serial#,
l.oracle_username AS ora_user,
o.owner||'.'||o.object_name AS object_name,
o.object_type,
DECODE(l.locked_mode,
0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',
4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',
TO_CHAR(l.locked_mode)) AS lock_mode,
o.status,
TO_CHAR(o.last_ddl_time,'dd.mm.yy') AS last_ddl
FROM dba_objects o
JOIN gv$locked_object l
ON o.object_id = l.object_id
JOIN gv$session v
ON l.session_id = v.sid AND l.inst_id = v.inst_id
ORDER BY v.sid, v.inst_id;
11. Tìm ra những SQL “nặng” nhất trong 1 khoảng thời gian cụ thể
Khi DB chậm trong khoảng 09:00–10:00 (ví dụ), bạn muốn biết:
+ Trong khoảng đó, SQL nào ăn nhiều thời gian nhất (Elapsed time)?
+ SQL đó ăn CPU hay ăn IO?
+ Có chạy Parallel (PX) nhiều không?
+ Đọc/ghi dữ liệu bao nhiêu?
+ Xử lý bao nhiêu dòng?
Hoặc:
Khi bạn gặp tình huống:
+ Sáng nay 9h–10h DB chậm, giờ đã hết chậm rồi
+ Tối qua batch chạy làm nghẽn DB
+ Có lúc app timeout nhưng hiện tại không tái hiện
Lúc này nên dùng SQL AWR để lục lại các khoảng thời gian quá khứ.
WITH snap AS (
SELECT snap_id
FROM dba_hist_snapshot
WHERE snap_id BETWEEN 155429 AND 155430
),
stat AS (
SELECT
dhss.sql_id,
SUM(dhss.cpu_time_delta) AS cpu_time,
SUM(dhss.elapsed_time_delta) AS elapsed_time,
SUM(dhss.executions_delta) AS executions,
SUM(dhss.physical_read_bytes_delta) AS disk_read_bytes,
SUM(dhss.physical_write_bytes_delta)AS disk_write_bytes,
SUM(dhss.io_interconnect_bytes_delta) AS io_interconnect_bytes,
SUM(dhss.io_offload_elig_bytes_delta) AS offload_eligible_bytes,
SUM(dhss.io_offload_return_bytes_delta) AS cell_smart_scan_only_bytes,
SUM(dhss.optimized_physical_reads_delta) AS flash_cache_reads,
SUM(dhss.rows_processed_delta) AS rows_processed,
SUM(dhss.px_servers_execs_delta) AS px_servers
FROM dba_hist_sqlstat dhss
WHERE dhss.snap_id IN (SELECT snap_id FROM snap)
AND dhss.instance_number = 1
-- AND dhss.executions_delta > 0 -- bật nếu chỉ muốn SQL có execute trong khoảng này
GROUP BY dhss.sql_id
),
txt AS (
SELECT sql_id, SUBSTR(sql_text,1,4000) AS text
FROM dba_hist_sqltext
)
SELECT
s.sql_id,
t.text,
ROUND(s.elapsed_time/1e6,0) AS elapsed_time_s,
ROUND(s.cpu_time/1e6,0) AS cpu_time_s,
s.executions AS executions,
s.px_servers AS px_servers,
ROUND(s.disk_read_bytes/1048576,0) AS disk_read_mb,
ROUND(s.disk_write_bytes/1048576,0) AS disk_write_mb,
ROUND(s.io_interconnect_bytes/1048576,0) AS io_interconnect_mb,
ROUND(s.offload_eligible_bytes/1048576,0) AS offload_eligible_mb,
ROUND(s.cell_smart_scan_only_bytes/1048576,0) AS cell_smart_scan_only_mb,
s.flash_cache_reads AS flash_cache_reads,
s.rows_processed AS rows_processed
FROM stat s
LEFT JOIN txt t
ON t.sql_id = s.sql_id
WHERE (s.elapsed_time/1e6) > 3600 -- > 1 giờ trong khoảng snapshot
ORDER BY elapsed_time_s DESC;
