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

Oracle

11

dba

1

nguyentronganhs viết ngày 19/12/2016

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;