查看當(dāng)前高耗CPU的SQL語(yǔ)句
1.查找SPID
先在linux系統(tǒng)中通過(guò)top命令查看產(chǎn)用資源較多的pid號(hào)，及spid
2.查看sql_id
將上一步查出的spid代入where條件
select sid,serial#,machine,username,program,sql_hash_value,sql_id, to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session where paddr in (select addr from v$process where spid in ('5648612','256523'));
3.查看sql文本
如果上一步sql_id或者 hash_value不為空，則可用v$sqlarea查出當(dāng)前正在使用的sql：
select sql_text from v$sqltext_with_newlines where hash_value = &hash_value order by piece;
此處輸入的哈希值即上一步獲取到的sql_id
或者
select * from v$sql where sql_id=''
4.腳本
直接通過(guò)輸入pid獲取sql文本：
SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC
鎖占用相關(guān)
查看被鎖（占用）的表的sid
select SESS.sid, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME,
AO.OBJECT_NAME, LO.LOCKED_MODE, SESS.SQL_id
from V$LOCKED_OBJECT LO, DBA_OBJECTs AO, V$SESSION SESS
where AO.OBJECT_ID = LO.OBJECT_ID
and lo.session_id = sess.sid;
殺掉會(huì)話(huà)
alter system kill session '173,16112';
查看等待
SELECT sid,
username,
event,
blocking_session,
seconds_in_wait,
wait_time,
SQL_id
FROM v$session
WHERE state IN ('WAITING') AND wait_class <> 'Idle';
https://www.cnblogs.com/cure-t-x-y/p/4244098.html
查看等待事件
查詢(xún)數(shù)據(jù)庫(kù)等待時(shí)間和實(shí)際執(zhí)行時(shí)間的相對(duì)百分比
select *
from v$sysmetric a
where a.METRIC_NAME in
('Database CPU Time Ratio', 'Database Wait Time Ratio')
and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);
查詢(xún)數(shù)據(jù)庫(kù)中過(guò)去30分鐘引起最多等待的sql語(yǔ)句
select ash.USER_ID,
u.username,
sum(ash.WAIT_TIME) ttl_wait_time,
s.SQL_TEXT
from v$active_session_history ash, v$sqlarea s, dba_users u
where ash.SAMPLE_TIME between sysdate - 60 / 2880 and sysdate
and ash.SQL_ID = s.SQL_ID
and ash.USER_ID = u.user_id
group by ash.USER_ID, s.SQL_TEXT, u.username
order by ttl_wait_time desc
查詢(xún)數(shù)據(jù)庫(kù)中的等待事件
select event, count(*)
from v$session_wait
group by event
order by count(*) desc
查詢(xún)數(shù)據(jù)庫(kù)過(guò)去15分鐘最重要的等待事件
select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
from v$active_session_history ash
where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
group by event
order by total_wait_time desc
在過(guò)去15分鐘哪些用戶(hù)經(jīng)歷了等待
select s.SID,
s.USERNAME,
sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
from v$active_session_history ash, v$session s
where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
and ash.SESSION_ID = s.SID
group by s.SID, s.USERNAME
order by total_wait_time desc;
查詢(xún)等待時(shí)間最長(zhǎng)的對(duì)象
select a.CURRENT_OBJ#,
d.object_name,
d.object_type,
a.EVENT,
sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
from v$active_session_history a, dba_objects d
where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
and a.CURRENT_OBJ# = d.object_id
group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
order by total_wait_time desc;
查詢(xún)過(guò)去15分鐘等待時(shí)間最長(zhǎng)的sql語(yǔ)句
select a.USER_ID,
u.username,
s.SQL_TEXT,
sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
from v$active_session_history a, v$sqlarea s, dba_users u
where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
and a.SQL_ID = s.SQL_ID
and a.USER_ID = u.user_id
group by a.USER_ID, s.SQL_TEXT, u.username
order by total_wait_time desc;
消耗更多的IO的SQL
select *
from (select s.PARSING_SCHEMA_NAME,
s.DIRECT_WRITES,
substr(s.SQL_TEXT, 1, 500),
s.DISK_READS
from v$sql s
order by s.DISK_READS desc)
where rownum < 20
查看哪些會(huì)話(huà)正在等待IO資源
SELECT username, program, machine, sql_id
FROM V$SESSION
WHERE EVENT LIKE 'db file%read';
查看正在等待IO資源的對(duì)象
SELECT d.object_name, d.object_type, d.owner
FROM V$SESSION s, dba_objects d
WHERE EVENT LIKE 'db file%read'
and s.ROW_WAIT_OBJ# = d.object_id
查看redo日志切換頻率
Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#)
From v$log_history
Group By round(FIRST_TIME, 'DD'), THREAD#
Order By 1, 2
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1
監(jiān)控高占用cpu的sql(最常用)
SELECT a.logon_time,
sid,
serial#,
a.username,
a.command,
a.status,
a.program,
a.machine,
a.client_identifier,
b.sql_text,
b.cpu_time,
b.sharable_mem,
round((b.disk_reads + b.buffer_gets) / b.executions) AS resource_cost
FROM v$session a,
v$sqlstats b
WHERE a.sql_id = b.sql_id
AND b.executions > 0
AND a.wait_class <> 'Idle'
ORDER BY resource_cost DESC;
查看當(dāng)前占用CPU時(shí)間高的SQL語(yǔ)句(常用)
select a.username,a.command,a.status,a.program,a.machine,a.client_identifier,b.sql_text,b.cpu_time,b.sharable_mem,
round((b.disk_reads + b.buffer_gets) / b.executions) as resource_cost
from v$session a, v$sqlstats b
where a.sql_id = b.sql_id
and b.executions > 0
and a.wait_class <> 'idle'
order by resource_cost desc;
定位當(dāng)前慢查詢(xún)
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "執(zhí)行次數(shù)",
round(sa.ELAPSED_TIME / 1000000, 2) "總執(zhí)行時(shí)間",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均執(zhí)行時(shí)間",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用戶(hù)ID",
u.username "用戶(hù)名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
查詢(xún)正在執(zhí)行中的select會(huì)話(huà)(用處是把查詢(xún)出來(lái)的select會(huì)話(huà)手動(dòng)殺掉，從而是CPU快速下降-只針對(duì)查詢(xún))
select v$session.SID,
v$session.SERIAL#,
v$session.MACHINE,
v$sqlarea.SQL_FULLTEXT,
v$sqlarea.SQL_TEXT,
'alter system kill session ' || '''' || v$session.SID || ',' || v$session.SERIAL# ||
''' immediate;'
from v$sqlarea, v$session
where v$sqlarea.ADDRESS = v$session.SQL_ADDRESS
and v$sqlarea.HASH_VALUE = v$session.SQL_HASH_VALUE
and v$session.STATUS = 'ACTIVE'
--and v$session.PROGRAM = 'JDBC Thin Client'
and UPPER(v$sqlarea.SQL_TEXT) like 'SELECT %';
查看連接IP地址
使用DBMS_SESSION 過(guò)程包
BEGIN
DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
創(chuàng)建觸發(fā)器
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
查看連接信息
select username,
program,
machine,
client_info,
sys_context('userenv', 'ip_address') as ipadd
from v$session s
where username is not null
order by username, program, machine;
自動(dòng)斷開(kāi)連接
空閑3分鐘自動(dòng)斷開(kāi)
SELECT * FROM DBA_PROFILES;
CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;
alter PROFILE DEFAULT LIMIT IDLE_TIME 3;
連接時(shí)間超過(guò)多少就斷開(kāi)
alter PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED;
12c后有新參數(shù)max_idle_time：
alter system set max_idle_time=2;
即2分鐘空閑時(shí)間不操作，會(huì)主動(dòng)被殺掉
殺掉會(huì)話(huà)
殺掉一個(gè)用戶(hù)所有會(huì)話(huà)
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.paddr,
s.STATUS,
'ALTER SYSTEM KILL SESSION ''' || s.sid || ', ' || s.serial# || '''' || ';' AS KILL
FROM gv$session s
JOIN gv$process p
ON p.addr = s.paddr
AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND S.USERNAME = '用戶(hù)名';
阻塞信息查詢(xún)
使用V$SESSION查看單實(shí)例阻塞信息
SELECT LPAD(' ',5*(LEVEL-1))||S."USERNAME" AS user_name ,
LPAD(' ',5*(LEVEL-1))||S."SID" AS session_id,
S."SERIAL#",
S."SQL_ID", S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT"
FROM V$SESSION S
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR S.SID IN(SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S."BLOCKING_SESSION" IS NULL
CONNECT BY PRIOR S."SID" = S."BLOCKING_SESSION";
使用v$lock查看當(dāng)前阻塞：
select
(select username||':'||sid||':'||serial# from v$session where sid=a.sid) ||
' locked ' ||
(select username ||':'||sid||':'||serial# from v$session where sid=b.sid)
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
找到目前的阻塞源：
select b.SQL_TEXT,a.SID,a.SERIAL# from v$session a, v$sqlarea b where a.SQL_HASH_VALUE = b.HASH_VALUE(+) and sid=&sid;
殺掉目前的阻塞源：
alter system kill session 'SID,SERIAL#'immediate;
生成kill語(yǔ)句腳本(自行修改)：
select 'alter system kill session ''' || s.SID || ',' || s.SERIAL# || ''' immediate;' killsql
from v$lock l, v$session s
where (id1, id2, l.type) in
(select id1, id2, type from v$lock where request > 0)
and l.sid = s.sid
and s.status='INACTIVE'
and s.type='USER'
and decode(request, 0, 'Holder:', ' Waiter:') = 'Holder:'
order by id1, ctime desc, request;
查看 Oracle 正在執(zhí)行的 sql 語(yǔ)句以及發(fā)起的用戶(hù)
SELECT b.sid oracleID,
b.username 用戶(hù)名,
b.serial#,
paddr,
sql_text 正在執(zhí)行的SQL,
b.machine 計(jì)算機(jī)名稱(chēng)
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value
oracle 查到鎖的源頭 blocking_session為源頭,
select last_call_et,v.event,
s.sql_id,
--- s.SQL_FULLTEXT,
s.SQL_TEXT,
v.inst_id,
V.SID,
V.CLIENT_IDENTIFIER,
v.blocking_session,
v.blocking_session_status,
'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;',
v.USERNAME,
s.CPU_TIME,
s.ELAPSED_TIME,
v.PROGRAM,
'kill -9 ' || p.spid,
v.CLIENT_INFO,
v.SQL_HASH_VALUE,
v.SQL_ADDRESS,
v.MACHINE,
v.TERMINAL, s.DISK_READS,s.BUFFER_GETS,s.SORTS,s.SHARABLE_MEM,s.PERSISTENT_MEM,s.RUNTIME_MEM,s.ROWS_PROCESSED
from gv$session v, gv$process p, gv$sql s
where v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username != 'SYS'
and p.addr = v.paddr
and s.ADDRESS = v.SQL_ADDRESS
and s.HASH_VALUE = v.SQL_HASH_VALUE
order by last_call_et desc;
殺掉源頭blocking_session,sid的值為上一條sql查到的blocking_session
select CLIENT_IDENTIFIER,v.inst_id,v.status,'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;', v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL from gv$session v where sid='XXX';
