首页 运维知识 关于Oracle11g 查询长时间运行的SQL

关于Oracle11g 查询长时间运行的SQL

一、大量的查询 某些时候,因为SQL的问题,导致数据库的session大量积压,服务器的磁盘读增大,CPU使用率剧增。一般这种SQL,都是一些全表扫描、多表关联、报表或者排序类的S…

一、大量的查询

某些时候,因为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 !=’plsqldev.exe’ and s.OSUSER !=’oracle’
–and a.SQL_TEXT like ‘select%’
order by s.last_call_et desc;
关于Oracle11g 查询长时间运行的SQL插图

二、大量行锁

某些时候,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,
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
AND wk.request != 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;
v_rows := 0;
if v_num > 0 then
for varA in v_CURSOR loop
insert /*+  IGNORE_ROW_ON_DUPKEY_INDEX(DB_BLOCK_RECORD,PK_DB_BLOCK_RECOR) */
into DBADMIN.DB_BLOCK_RECORD
values
(
varA.DB_User,varA.BK_User,varA.BK_SID,varA.BK_SERIAL,varA.BK_WAIT_EVENT,varA.BK_WAIT_CLASS,varA.BK_App,
varA.BK_Machine,varA.BK_OS_User,varA.BK_SQL_ID,varA.BK_SQL_TEXT,
varA.WT_User,varA.WT_SID,varA.WT_SERIAL,varA.WT_WAIT_EVENT,varA.WT_WAIT_CLASS,
varA.WT_App,varA.WT_Machine,varA.WT_OS_User,varA.WT_SQL_ID,varA.WT_SQL_TEXT,
varA.lock_type,varA.mode_held,
varA.mode_requested,varA.lock_id1,varA.lock_id2,varA.blocking_others,varA.sysdate
);
–insert into DBADMIN.DB_BLOCK_RECORD_temp
–values(varA.BK_SID,varA.BK_SERIAL,varA.WT_SID,varA.WT_SERIAL,varA.lock_id1,varA.lock_id2);
v_rows := v_rows+1;
end loop;
commit;
—select  from DBADMIN.DB_BLOCK_RECORD_temp where rownum<=v_rows;
–DBMS_OUTPUT.PUT_LINE(v_ROWS);
DBMS_OUTPUT.PUT_LINE(v_ROWS);
else
DBMS_OUTPUT.PUT_LINE(0);
end if;
end;

3.定时任务

cat db_block_record.sh
#!/bin/bash
source /home/oracle/.bash_profile
while true
do
sleep 1
v_log=/u01/dba_scripts/db_block_record/log/db_block_record.log
v_date=`date +”%Y-%m-%d %H:%M:%S”`
v_out=`sqlplus -s dbadmin/QazWsx12  <<EOF
set colsep’ ‘;
set feedback off;
set heading off;
set pagesize 0;
set termout off;
set trimout on;
set trimspool on;
set serveroutput on;
exec dbadmin.proc_DB_BLOCK_RECORD
EOF`
echo “$v_out”
    if [ “$v_out” -gt 0 ]
then
echo “$v_date “—-” $v_out”” waiter,please check!” >> “$v_log”
fi
done
免责声明:文章内容不代表本站立场,本站不对其内容的真实性、完整性、准确性给予任何担保、暗示和承诺,仅供读者参考,文章版权归原作者所有。如本文内容影响到您的合法权益(内容、图片等),请及时联系本站,我们会及时删除处理。

作者: 小小编

为您推荐

dell R710 更换raid卡后,raid卡信息没有了,处理方案

dell R710 更换raid卡后,raid卡信息没有了,处理方案

1.将一台服务器(A)的硬盘依次拔出,按相同顺序插入另一台同样配置的服务器(B) 2.启动服务器(B) 3.按提示键盘按...
PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

下载 并安装 PL SQL Developer 13,默认支持中文语言 ========================...
关于一条sql语句在mysql中是如何执行的

关于一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在my...
关于sql注入姿势总结(mysql)

关于sql注入姿势总结(mysql)

前言 学习了sql注入很长时间,但是仍然没有系统的了解过,这次总结一波,用作学习的资料。 从注入方法分:基于报错、基于布...
关于Oracle SQL外连接

关于Oracle SQL外连接

SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 连接...

发表回复

返回顶部