Các câu SQL check hệ thống.
Check xem máy nào đang lockdata
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, 'ALTER SYSTEM KILL SESSION ''' || b.sid || ', ' || b.serial# || ''';' AS KILL_COMMAND, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
Check câu lệnh SQL chiếm nhiều thời gian
SELECT parsing_schema_name username, sql_fulltext sql, optimizer_cost cost,executions, ROUND (elapsed_time / executions / 1000000,2) AS "avg_time/exc(s)" FROM gv$sqlarea WHERE optimizer_cost > 100 AND last_load_time >= TRUNC (SYSDATE) AND executions > 1 AND elapsed_time / executions/1000000 > 1 AND parsing_schema_name NOT LIKE '%SYS%' ORDER BY optimizer_cost DESC
Tìm tác vụ đang chạy mãi không xong
SELECT S.SID, S.SERIAL#, 'ALTER SYSTEM KILL SESSION ''' || S.SID || ', ' || S.SERIAL# || ''';' AS KILL_COMMAND, Q.SQL_TEXT FROM GV$SESSION S, V$SQL Q WHERE S.USERNAME IS NOT NULL AND S.STATUS = 'ACTIVE' AND S.SQL_ID IS NOT NULL AND Q.SQL_ID = S.SQL_ID;
Tìm các SID đang xử dụng bao nhiêu % CPU
SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage" FROM v$session se, v$sesstat ss, v$statname st WHERE ss.statistic# = st.statistic# AND name LIKE '%CPU used by this session%' AND se.sid = ss.SID AND se.username IS NOT NULL ORDER BY value DESC;