Các câu lệnh SQL check hệ thống ORACLE
Các câu lệnh SQL check hệ thống ORACLE

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;