Oralce 异常问题排查sql

用以下语句找出长时间操作的SQL语句（历史）：

select longops.last_update_time,longops.sid,sql.sql_id,longops.elapsed_seconds,longops.opname,sql.sql_text from

v$session_longops longops , v$sql sql where longops.elapsed_seconds>6 and longops.sql_id=sql.sql_id

and last_update_time>to_date('2019-09-04 9:00','yyyy-mm-dd hh24:mi')

order by ELAPSED_SECONDS desc;

查看慢sql的慢子任务：

select * from v$session_longops longops where sql_id='dbtt0g974fwkc'

实时运行时慢sql

select cpu_time,elapsed_time,sql_id,loads,disk_reads,buffer_gets,user_io_wait_time,last_active_time

from v$sqlarea where last_active_time>to_date('2019-05-16 16:19','yyyy-mm-dd hh24:mi')

order by elapsed_time desc

查看慢sql的机器和id：

select machine,s.sid,s.serial# from v$session s where sql_id='g5cbvf5zthcnu'

alter system kill session '830,16390' ;

IO占用高的sql排序

select cast(buffer_gets/decode(EXECUTions,0,10000,EXECUTions) as int) 平均IO ,

EXECUTions 执行次数,buffer_gets 总逻辑IO,disk_reads 硬盘读取,sql_id,last_active_time

from v$sqlarea

where last_active_time>to_date('2019-09-04 09:19','yyyy-mm-dd hh24:mi')

order by 平均IO desc

查询当前Session的执行中sql的统计

SELECT

machine,b.sql_id,count(1) as numcount

FROM v$sqltext a,v$session b

where a.hash_value =b.sql_hash_value

and username='MODELHOME' and b.status<>'INACTIVE'

group by machine,b.sql_id

查询当前连接数

select username,count(username),machine from v$session where username is not null

and username='MEMBERSHIP'

-- and username='MODELHOME'

group by username,machine

order by machine;

根据CPU使用查sql（pid用linux进程查）

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

SELECT p.pid,p.spid,s.sid,p.username,s.TYPE,

         s.SERIAL#,s.SCHEMANAME,s.OSUSER,s.MACHINE,

         s.PROCESS,p.PROGRAM,s.MODULE,s.STATUS,

         s.terminal,logon_time

FROM v$process p, v$session s

WHERE p.addr=s.paddr

and spid ='*****';

根据sid查找完整sql语句：

select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = 'xxx')order by piece asc

查看sql详情

select * from v$sql WHERE sql_id = 'g1ttmbrtvb83y';

select * from v$sqltext a where a.sql_id='0jbm6d259mmu1' order by piece；

历史sql： select * from v$active_session_history where sql_id='fy9mrs8qyg1ky'

查看sql参数

select

s.sql_text,

b.name

,b.position,b.dup_position,b.last_captured,

b.value_string

from v$sql s, v$sql_bind_capture b

where s.hash_value=b.hash_value and s.sql_id = 'g9p6107pk2rxq';

根据sql id查询执行计划

SQL仍在Shared Pool中：select * from table (dbms_xplan.display_cursor('4wktu80k1xy5k' , 0, 'ALLSTATS LAST cost' ));

select * from table(dbms_xplan.display_cursor('52gamnh6g8u61',''))

SQL在AWR库中：select * from table(dbms_xplan.display_awr('52gamnh6g8u61',''))

查询死锁：

select l.oracle_username,o.owner,o.object_name,o.object_type,s.sql_id,s.sid,s.serial#,p.spid

from v$locked_object l, dba_objects o, v$session s, v$process p

where l.object_id = o.object_id

and l.session_id = s.sid

and s.paddr = p.addr order by o.object_id;

杀锁：alter system kill session '830,16390' ;

批量杀：

select distinct 'alter system kill session '''||s.sid||','||s.serial#||''' ;' as a

from v$locked_object l, dba_objects o, v$session s, v$process p

where l.object_id = o.object_id

and l.session_id = s.sid

and s.paddr = p.addr

select

_fulltext from v$sqlarea a

inner join v$session b on a.hash_value=b.sql_hash_value and a.address=b.sql_address

inner join V$PROCESS c on c.addr=b.paddr

where spid=31737;

查资源限制

SELECT * FROM V$RESOURCE_LIMIT;

select * from dba_profiles where profile='DEFAULT';

修改资源限制（慎用）：

alter profile DEFAULT limit LOGICAL_READS_PER_CALL 2000000;

alter profile DEFAULT limit CPU_PER_CALL 4500;

查询等待事件汇总

SELECT a.event,

SUM(a.wait_time + a.time_waited) total_wait_time

FROM v$active_session_history a

WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE

and a.event is not null

GROUP BY a.event

ORDER BY total_wait_time DESC;

查询特定等待事件的sql历史

select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash

where sample_time>=to_timestamp('2017-05-18 11:00:00','yyyy-mm-dd hh24:mi:ss')

and sample_time<=to_timestamp('2017-05-18 13:00:00','yyyy-mm-dd hh24:mi:ss')

and event='cursor: pin S wait on X'

group by event,blocking_session,sql_id

查询等待SQL

SELECT t1.sid,

t1.serial#,

t1.username,

t1.event,

t2.sql_text,

t1.sql_id,

t1.seconds_in_wait,

t1.program,

t1.blocking_session,

-- object_type || ': ' || object_name object,

t1.wait_class,

t1.state

FROM v$session t1

LEFT OUTER JOIN v$sqlarea t2 on (t1.sql_id = t2.sql_id)

--LEFT OUTER JOIN dba_objects t3 ON (object_id = row_wait_obj#)

where t1.wait_class <> 'Idle';

等待事件最长的sql

SELECT A.USER_ID,U.USERNAME,TO_NCHAR(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;

重新收集统计信息（数据更新过多，索引失效时使用）

begin

dbms_stats.gather_table_stats(ownname=>'modelhome',tabname=>'BN_PAYFEE_PLAN');

end;

或

analyze table BN_PAYFEE_PLAN compute statistics for all indexes;

自动收集方式详解

【数据库管理】数据库自动维护任务介绍 - 数据库其他综合 - 红黑联盟

自动统计信息收集gather_stats_job_ITPUB博客

收集任务窗口

select t1.window_name,t1.repeat_interval,t1.duration

from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name in ('ORA$AT_WGRP_OS');

select * from DBA_AUTOTASK_CLIENT_HISTORY where client_name='auto optimizer stats collection' order by window_start_time;

查询修改量（修改量决定是否需要重新做统计信息）

select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from dba_tab_modifications where TABLE_NAME='TMP_OBJECT';

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection',operation => NULL,window_name => NULL);

END;

SELECT *

FROM DBA_AUTOTASK_CLIENT

WHERE CLIENT_NAME = 'auto optimizer stats collection';

游标相关操作

查询： select *　from v$parameter where name ='open_cursors';

select count(*) from v$open_cursor;

select sid,value from v$sesstat a , v$statname b

where a.statistic# = b.statistic# and name='opened cursors current' order by 2 desc;

修改： alter system set open_cursors = 2000;

---

归档日志查询

select * from (

SELECT TRUNC(FIRST_TIME) "TIME",

SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE(GB)"

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(FIRST_TIME)

)a order by TIME

归档日志分析

oracle归档日志增长过快处理方法 - 张冲andy - 博客园

删归档

rman target / nocatalog log=/tmp/del_arch$(date +%Y-%m-%d).log <<EOF

DELETE noprompt force ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

关闭数据库

sqlplus / as sysdba

shutdown immediate; ---正常用 startup

startup mount

alter database archivelog;

alter database noarchivelog;

alter database open;

shutdown abort -- 强杀

archive log list

---

物化视图刷新相关（M系列的crm（已停止DTS同步）和 M3的自拼箱费用均摊功能在使用）

查看所有定时任务（可以看到物化视图的定时刷新）

select * from dba_jobs;

所有物化视图，以及刷新时间（刷新花费时间为INCREFRESHTIM）

select * from dba_mview_analysis;

物化视图刷新状态（重要：导致刷新慢等异常情况基本上是这个原因）

select mview_name,last_refresh_date, staleness from user_mviews;

处理方案Oracle物化视图失效的几种情况及测试_数据库技术_Linux公社-Linux系统门户网站

需要运行语句: ALTER MATERIALIZED VIEW MV_NAME COMPILE; 进行重新编译

Oracle 各版本下的物化视图刷新慢需要调整的系统参数

10 alter system set "_mv_refresh_use_stats" =FALSE;

10.2 alter system set "_mv_refresh_use_stats" =true;(test环境有效)

11： alter system set “_mv_refresh_use_hash_sj”=FALSE;

Mysql 查询限制：

查看设置：

show variables like 'max_execution_time';

（1）全局设置 单位ms

SET GLOBAL MAX_EXECUTION_TIME=1000;

（2）对某个session设置

SET SESSION MAX_EXECUTION_TIME=1000;

RDS(5.7版本)修改参数：loose_max_execution_time 单位：毫秒

PostgreSql 限制参数：

statement_timeout 单位：毫秒

USE `information_schema`;

SELECT * FROM PROCESSLIST WHERE info IS NOT NULL order by time desc