Oracle查询长时间执⾏sql,Oracle11g查询长时间运⾏的SQL ⼀、⼤量的查询
某些时候,因为SQL的问题,导致数据库的session⼤量积压,服务器的磁盘读增⼤,CPU使⽤率剧增。⼀般这种SQL,都是⼀些全表扫描、多表关联、报表或者排序类的SQL。这中情况很有可能,是客户端查询造成的。⼀般程序⾥⾯都会设置客户端查询超时时间,⼀旦某条SQL查询时间超过了程序设置的超时时间,那么这个客户端的查询则被kill掉,但是下发到数据库的SQL仍然还在运⾏,直到查询出结果。因此,这些⼤量积压的SQL就是没⽤的SQL,需要我们将这些SQLkill掉。
某个session阻塞好⼏个session
select 'alter system kill session '||''''||s.SID||','||s.SERIAL#||''''||';',s.SID,s.blocking_session,s.MACHINE,s.OSUSER,s.PROGRAM,
s.USERNAME,s.last_call_et,a.SQL_ID,s.LOGON_TIME,a.SQL_TEXT,a.SQL_FULLTEXT,
w.EVENT,a.DISK_READS,a.BUFFER_GETS
from v$process p,v$session s,v$sqlarea a,v$session_wait w
where p.ADDR = s.PADDR and s.SQL_ID = a.sql_id and s.sid = w.SID
and s.STATUS = 'ACTIVE' and s.PROGRAM !='' and s.OSUSER !='oracle'
--and a.SQL_TEXT like 'select%'
order by s.last_call_et desc;
⼆、⼤量⾏锁
某些时候,session突然巨量增加,并且久久不释放。查询告警⽇志,可能发现告警中记录由死锁。这种情况,⼀般由DML语句造成(表的外键没索引,程序逻辑错乱,⽹络波动)
记录表/⾏锁的监控(上⾯《⼀》中的监控SQL也可使⽤查看)
1.建表
-- Create table
create table DB_BLOCK_RECORD
(
DB_USER        VARCHAR2(30),
BK_USER        VARCHAR2(30),
BK_SID          NUMBER not null,
BK_SERIAL      NUMBER,
BK_WAIT_EVENT  VARCHAR2(64),
BK_WAIT_CLASS  VARCHAR2(64),
BK_APP          VARCHAR2(48),
BK_MACHINE      VARCHAR2(64),
BK_OS_USER      VARCHAR2(30),
BK_SQL_ID      VARCHAR2(13),
BK_SQL_TEXT    VARCHAR2(1000),
WT_USER        VARCHAR2(30),
WT_SID          NUMBER not null,
WT_SERIAL      NUMBER,
WT_WAIT_EVENT  VARCHAR2(64),
WT_WAIT_CLASS  VARCHAR2(64),
WT_APP          VARCHAR2(48),
WT_MACHINE      VARCHAR2(64),
WT_OS_USER      VARCHAR2(30),
WT_SQL_ID      VARCHAR2(13),
WT_SQL_TEXT    VARCHAR2(1000), LOCK_TYPE      VARCHAR2(26),
MODE_HELD      VARCHAR2(40),
MODE_REQUESTED  VARCHAR2(40), LOCK_ID1        VARCHAR2(40) not null, LOCK_ID2        VARCHAR2(40) not null, BLOCKING_OTHERS VARCHAR2(40),
BK_TIME        DATE default sysdate not null )
tablespace DBADMIN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DB_BLOCK_RECORD
add constraint PK_DB_BLOCK_RECOR primary key (BK_SID, WT_SID, LOCK_ID1, LOCK_ID2)
using index
tablespace DBADMIN
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
;
2.创建存储过程
create or replace procedure proc_DB_BLOCK_RECORD as
v_num            number;
v_ROWS          number;
v_count          number;
cursor v_CURSOR is
SELECT  bs.username DB_User,
bs.username BK_User,bs.SID BK_SID,bs.SERIAL# BK_SERIAL,
bs.EVENT BK_WAIT_EVENT,bs.WAIT_CLASS BK_WAIT_CLASS,bs.program BK_App,bs.machine BK_Machine, bs.osuser BK_OS_User,
bs.SQL_ID BK_SQL_ID,sa.SQL_TEXT BK_SQL_TEXT,
ws.username WT_User,ws.SID WT_SID,ws.SERIAL# WT_SERIAL,
ws.EVENT  WT_WAIT_EVENT,ws.WAIT_CLASS WT_WAIT_CLASS,ws.program WT_App,ws.machine WT_Machine, ws.osuser WT_OS_User,
sa.SQL_ID WT_SQL_ID,sa.SQL_TEXT WT_SQL_TEXT, DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Row Locks',
'TM', 'Table Locks',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
)
mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
plsql12配置数据库连接
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking',          /**//* Not blocking any other processes */ 1, 'Blocking',              /**//* This lock blocks other processes */
2, 'Global',          /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others  ,sysdate
FROM v$lock hk, v$session bs, v$lock wk, v$session ws,v$sqlarea sa WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
quest != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
and ws.SQL_ID=sa.SQL_ID
ORDER BY 4,11,23,24;
begin
select count(1) into v_num from dba_blockers;

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。