oracle问题诊断,常用Oracle问题诊断sql脚本

*********************************查询session信息以及process信息*********************************

--根据username查询sid和后台spid

select a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.username=&USERNAME;

--根据后台spid查询sid

select username,sid,serial#,STATUS,OSUSER,MACHINE,PROGRAM from v$session where paddr = (select addr from v$process where spid = &SPID);

--根据sid查询后台spid

select spid from v$process where addr = (select paddr from v$session where sid = &SID);

--查询自己session的后台spid

select a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.sid=(select distinct sid from v$mystat);

************************************************************************************************

*********************************查找产生大量物理读的进程*********************************

select * from (select * from (select st.sid,st.value,sn.name,s.username,s.logon_time

from v$sesstat st,v$statname sn ,v$session s

where st.sid=s.sid AND st.statistic#=sn.statistic# and st.value>100000 and s.username is not null

and sn.name like '%physical read%' order by 2 desc));

**************************************************************************************

*********************************查找产生redo log过快的进程*********************************

col machine format a20

col osuser format a20

set lines 150

select sysdate,

se.username,

se.sid,

se.serial#,

se.SQL_HASH_VALUE,

se.status,

se.machine,

se.osuser,

round(st.value / 1024 / 1024) redosize,

sa.sql_text

from v$session se, v$sesstat st, v$sqlarea sa

where se.sid = st.sid

and st.STATISTIC# =

(select STATISTIC# from v$statname where NAME = 'redo size')

--and se.username is not null

and st.value > 10 * 1024 * 1024

and se.SQL_ADDRESS = sa.ADDRESS

and se.SQL_HASH_VALUE = sa.HASH_VALUE

order by redosize;

********************************************************************************************

*********************************获取大批量数据操作测试的耗费时间和产生redo量*********************************

set serveroutput on;

DECLARE

start_time NUMBER;

end_time NUMBER;

start_redo_size NUMBER;

end_redo_size NUMBER;

BEGIN

start_time := dbms_utility.get_time;

SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s

WHERE m.STATISTIC#=s.STATISTIC#

AND s.NAME='redo size';

--transaction start

用户脚本

--transaction end

end_time := dbms_utility.get_time;

SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s

WHERE m.STATISTIC#=s.STATISTIC#

AND s.NAME='redo size';

dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');

dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');

END;

/

--如果用户的脚本无法嵌入到如上的匿名块中，则使用如下脚本获取时间点和redo值前后相减即可：

SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size';

--transaction start

执行用户脚本

--transaction end

SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size';

**************************************************************************************************************

*********************************查看某用户当前执行的sql语句*********************************

set pages 500

set lines 160

break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodup

col machine format a20

col text format a64

col user format a10

col logontime format a10

col sid format 99999

col serial# format 99999

select b.sql_text text,a.sid sid,a.serial# serial#,a.username "user",a.machine machine,to_char(logon_time,'dd/hh24:mi') "logontime"

from v$session a,v$sqltext b

where a.username like upper('&1')

and b.address = a.sql_address

and b.hash_value = a.sql_hash_value

order by a.sid,a.serial#,b.piece;

*********************************************************************************************

*********************************查询执行计划并格式化输出*********************************

--直接查询library cache中的sql真实的执行计划(9i以上)，sql_hash_value 从 v$session 中查到：

select '| Operation                         | PHV/Object Name               |  Rows | Bytes|   Cost |'

as "Optimizer Plan:" from dual

union all

select

rpad('| '||substr(lpad(' ',1*(depth-1))||operation||

decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||

rpad(decode(id, 0, '------------- '

, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)

||' ',1, 30)), 31, ' ')||'|'||

lpad(decode(cardinality,null,'  ',

decode(sign(cardinality-1000), -1, cardinality||' ',

decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',

decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',

trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||

lpad(decode(bytes,null,' ',

decode(sign(bytes-1024), -1, bytes||' ',

decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',

decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',

trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||

lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',

decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',

trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"

from v$sql_plan sp

where sp.hash_value=&SQL_HASH_VALUE;

--或者预生成执行计划：

EXPLAIN PLAN set statement_id='MYSQL1' FOR

--(表示为以下sql语句生成执行计划,不会执行该语句)

&SQL语句

--格式化输出：

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

--查找执行计划版本超过10个的sql语句

select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss where

sa.address=ss.KGLHDPAR and sa.version_count > 10 order by sa.version_count ;

******************************************************************************************

*********************************根据sid或spid查询执行的sql语句*********************************

--根据sid查询执行的sql语句

select se.username,

se.sid,

se.serial#,

se.osuser,

se.machine,

se.program,

se.logon_time,

sa.sql_text

from v$session se, v$sqlarea sa

where se.SQL_ADDRESS = sa.ADDRESS

and se.SQL_HASH_VALUE = sa.HASH_VALUE

and se.sid = '&SID';

--根据spid查询执行的sql语句

select se.username,

se.sid,

se.serial#,

se.osuser,

se.machine,

se.program,

se.logon_time,

sa.sql_text

from v$session se, v$sqlarea sa, v$process pr

where se.SQL_ADDRESS = sa.ADDRESS

and se.SQL_HASH_VALUE = sa.HASH_VALUE

and se.PADDR=pr.ADDR

and pr.spid = '&SPID';

************************************************************************************************

*********************************查询各种等待事件对应的sql语句*********************************

set pages 500

set lines 160

break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodup

col machine format a20

col text format a64

col user format a10

col logontime format a10

col sid format 99999

col serial# format 99999

select b.sql_text text,

a.sid sid,

a.serial# serial#,

a.username "user",

a.machine machine,

to_char(logon_time,'dd/hh24:mi')  "logontime"

from v$session a,v$sqltext b, v$session_wait c

where a.sid = c.sid

and c.event = '&EVENT_NAME'

and b.address = a.sql_address

and b.hash_value = a.sql_hash_value

order by a.sid,a.serial#,b.piece;

***********************************************************************************************

#######################################关于锁#######################################

补充：latch

到对象

*******************************************查询锁****************************************

--查看整个instance的锁情况

select * from dba_locks;

--查看整个instance的dml锁情况

select * from v$lock where type in ('TX','TM');

select * from v$lock where type in ('TX','TM') and sid='&SID';

--查看session锁定的对象

select * from v$locked_object;

--查询锁的holder和waiter：

select decode(request, 0, 'Holder:', 'Waiter:') || sid,

id1,

id2,

lmode,

request,

type

from v$lock

where (id1, id2, type) in

(select id1, id2, type from v$lock where request > 0)

order by id1, request;

--查询表是否有锁：

select oracle_username,owner,object_name,object_type,session_id,locked_mode

from v$locked_object v, dba_objects d

where v.object_id = d.object_id

and object_name=upper('&1')

order by object_name ;

--查找所有被锁的对象：

select oracle_username,owner,object_name,object_type,session_id,locked_mode,l.type,l.block

from v$locked_object v, dba_objects d,v$lock l

where l.block>0 and v.session_id=l.sid

and d.object_id=v.object_id

order by object_name,l.block ;

--查看DML LOCK情况和锁定的对象情况：

select a.sid,

decode(a.type,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'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',

a.type) lock_type,

decode(a.lmode,

0, 'None',           /* Mon Lock equivalent */

1, 'Null',           /* N */

2, 'Row-S (SS)',     /* L */

3, 'Row-X (SX)',     /* R */

4, 'Share',          /* S */

5, 'S/Row-X (SSX)',  /* C */

6, 'Exclusive',      /* X */

to_char(a.lmode)) mode_held,

decode(a.request,

0, 'None',           /* Mon Lock equivalent */

1, 'Null',           /* N */

2, 'Row-S (SS)',     /* L */

3, 'Row-X (SX)',     /* R */

4, 'Share',          /* S */

5, 'S/Row-X (SSX)',  /* C */

6, 'Exclusive',      /* X */

to_char(a.request)) mode_requested,

a.ctime        lock_time,

to_char(a.id1) lock_id1,

c.object_name  lock_object_name,

c.object_type  lock_object_type,

to_char(a.id2) lock_id2

from v$lock a,dba_objects c

where (id1,id2) in

(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and b.id2=a.id2 )

and a.type in ('TX','TM')

and a.id1=c.object_id(+);

--存在多个BLOCKER时，查出源头的BLOCKER：

SELECT *

FROM V$LOCK

WHERE SID IN (SELECT SID SESSION_ID

FROM V$LOCK

WHERE BLOCK > 0

MINUS

SELECT W.SID SESSION_ID

FROM V$SESSION_WAIT W

WHERE W.EVENT = 'enqueue');

--查看BLOCKER对应的SESSION的状态和等待事件：

SELECT S.SID,

S.USERNAME,

S.STATUS,

W.EVENT,

L.TYPE,

L.ID1,

L.ID2,

L.LMODE,

L.CTIME,

L.BLOCK

FROM V$SESSION S, V$SESSION_WAIT W, V$LOCK L

WHERE S.SID = W.SID

AND S.SID = L.SID

AND L.BLOCK > 0;

--查出WAITER等待的记录行：

--首先查出WAITER等待的资源：

SELECT ROW_WAIT_OBJ# ,

ROW_WAIT_FILE# ,

ROW_WAIT_BLOCK# ,

ROW_WAIT_ROW#

FROM V$SESSION

WHERE SID IN (SELECT DISTINCT SID FROM V$LOCK WHERE REQUEST > 0 )

AND ROW_WAIT_OBJ# <> -1;

--再根据OBJECT_ID得出具体的对象属主和名称：

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=< ROW_WAIT_OBJ#>

--根据以上得到的OBJECT_ID,FILE_ID,BLOCK_ID,ROW#，就构成标准的ROWID，查出记录行：

SELECT *

FROM < OWNER > . < OBJECT_NAME >

WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1,

ROW_WAIT_OBJ#,

ROW_WAIT_FILE#,

ROW_WAIT_BLOCK#,

ROW_WAIT_ROW#);

******************************************************************************************

#######################################关于等待事件#######################################

*******************************************查询等待事件****************************************

select sw.seq#,

sw.sid || ',' || s.serial# sids,

s.username,

sw.event,

sw.P1,

sw.p2,

sw.p3,

sw.wait_time "WAIT",

sw.state,

sw.seconds_in_wait sec,

s.status,

to_char(s.logon_time, 'dd/hh24:mi:ss') logon_time,

s.MACHINE,

s.TERMINAL,

s.PROGRAM

--,sa.SQL_TEXT

from v$session s, v$session_wait sw

--,v$sqlarea sa

where sw.sid = s.sid

--and s.SQL_ADDRESS=sa.ADDRESS

--and s.SQL_HASH_VALUE=sa.HASH_VALUE

and s.username is not null

and sw.event not like '%SQL*Net%'

and sw.event not like 'PX Deq%'

and sw.event not like 'rdbms ipc message'

order by sw.event, s.username;

*************************************************************************************************

*********************************cache buffers chains等待事件的处理*********************************

查询等待事件的类型是否是latch free：

select sw.sid || ',' || s.serial# sids,

s.username,

sw.event,

sw.P1,

sw.p2,

sw.p3,

sw.p1raw,

sw.wait_time "WAIT",

sw.state,

sw.seconds_in_wait sec,

s.status,

to_char(s.logon_time, 'dd/hh24:mi:ss') log_time

from v$session s, v$session_wait sw

where s.username is not null

and sw.sid = s.sid

and sw.event not like '%SQL*Net%'

and sw.event not like 'PX Deq%'

order by sw.event;

如果是latch free，则其中p2字段的值表示latch number，据此可以查出是什么原因引起的latch free：

select * from v$latchname where latch#=&P2;

如果等待的latch是cache buffers chains，则需要根据p1raw查出被争用的hot block和segment名称：

--在后台sys用户下执行，查找热块

select /*+ RULE */

e.owner || '.' || e.segment_name segment_name,

e.extent_id extent#,

x.dbablk - e.block_id + 1 block#,

x.tch,

l.child#

from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e

where x.hladdr = '&P1RAW'

and e.file_id = x.file#

and x.hladdr = l.addr

and x.dbablk between e.block_id and e.block_id + e.blocks - 1

order by x.tch desc;

column segment_name format a30

select distinct e.owner,e.segment_name,e.segment_type

from dba_extents e,

(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b

where e.relative_fno=b.dbarfil

and e.block_id<=b.dbablk

and e.block_id+e.blocks>b.dbablk;

--查找产生热块的sql：

column segment_name format a35

select /*+ rule */ hash_value,sql_text from v$sqltext

where (hash_value,address ) in (

select a.hash_value,a.address from v$sqltext a ,

(select distinct e.owner,e.segment_name,e.segment_type

from dba_extents e,

(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b

where e.relative_fno=b.dbarfil

and e.block_id<=b.dbablk

and e.block_id+e.blocks>b.dbablk ) b

where a.sql_text like '%'||b.segment_name||'%'

and b.segment_type='TABLE')

order by hash_value,address,piece;

找到latch holder所在session的sid和serial#，考虑是否可以kill掉，缓解数据库的压力：

--这个latchhold变化得非常快，每刷新一次都会变化

select a.username, a.sid, a.serial#, a.status, b.pid, b.laddr, b.name

from v$session a, v$latchholder b

where a.sid = b.sid;

***************************************************************************************************

*********************************db file sequential read等待事件的分析*********************************

--当等待事件为db file sequential read时，P1对应file_id，P2对应&block_id

--通过下面这个语句可以查询到正在等待什么对象

select owner,segment_name,segment_type

from dba_extents

where file_id = &file_id

and &block_id between block_id and block_id+blocks-1;

*******************************************************************************************************

*********************************db file scattered read等待事件的分析*********************************

--当等待事件是db file scattered read时，用以下语句检查执行计划：

select hash_value,child_number,

lpad(' ',2*depth)||operation||' '||options||decode(id,0,substr(optimizer,1,6)||' Cost='||to_char(cost)) operation,

object_name object,cost,cardinality,round(bytes/1024) kbytes

from v$sql_plan

where hash_value in

(select a.sql_hash_value from v$session a,v$session_wait b

where a.sid=b.sid

and b.event='db file scattered read')

order by hash_value,child_number,id;

*******************************************************************************************************

*********************************mon_rbs 占用大量回滚段的监控*********************************

select s.sid,

s.serial#,

s.machine,

s.OSUSER,

s.username,

s.status,

round(s.last_call_et / 60) "IDLE_Min",

round((sysdate - to_date(t.start_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60) "Trans_Min",

r.usn,

round(r.RSSIZE / 1024 / 1024) rbssize_M,

round(r.OPTSIZE / 1024 / 1024) optsize_M,

s.logon_time,

s.program,

q.sql_text,

q.hash_value

FROM V$session s, V$transaction t, V$rollstat r,v$sqlarea q

WHERE s.saddr = t.ses_addr

AND t.xidusn = r.usn

AND s.sql_address=q.address

AND s.sql_hash_value=q.hash_value

AND ((((r.curext = t.start_uext - 1) OR

((r.curext = r.extents - 1) AND t.start_uext = 0))

and s.last_call_et /60 > 30

and r.rssize>r.optsize

and r.rssize > 50*1024*1024)

or r.rssize >100*1024*1024)

order by last_call_et desc;

**********************************************************************************************

*********************************mon_xatrans 分布式事务锁的监控*********************************

select a.local_tran_id,statu from dba_2pc_pending a where state='prepared';

处理：

rollback force '&LOCAL_TRAN_ID';

commit;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('&LOCAL_TRAN_ID');

commit;

************************************************************************************************

*********************************mon_swait 等待事件的监控*********************************

select sw.seq#,sw.sid||','||s.serial# sids,s.username,sw.event,sw.P1,sw.p2,sw.p3,sw.wait_time "WAIT",

sw.state,sw.seconds_in_wait sec,s.status,to_char(s.logon_time,'dd/hh24:mi:ss') logon_time

from v$session s,v$session_wait sw

where

sw.sid =s.sid

and s.username is not null

and sw.event not like '%SQL*Net%'

and sw.event not like 'PX Deq%'

and sw.event not like 'rdbms ipc message'

and sw.event not like 'queue messages'

order by sw.event,s.username ;

******************************************************************************************

*********************************mon_sqlarea 未使用绑定变量的sql监控*********************************

select substr(sql_text, 1, 50) "SQL", count(*) cnt, sum(sharable_mem) "TotExecs"

FROM v$sqlarea

WHERE executions =1

GROUP BY substr(sql_text, 1, 50)

HAVING count(*) > 5000

ORDER BY 2;

*****************************************************************************************************

*********************************mon_sharepool 占用大量内存的sql监控*********************************

select se.sid,se.SERIAL#,pr.SPID,se.osuser,se.MACHINE,sq.SHARABLE_MEM/1024/1024 ,se.PROGRAM,sq.SQL_TEXT

from v$sqlarea sq,v$session se,v$process pr

where se.PADDR=pr.ADDR

and ((se.SQL_ADDRESS=sq.ADDRESS and se.SQL_HASH_VALUE=sq.HASH_VALUE)

or

(se.PREV_SQL_ADDR=sq.ADDRESS and se.PREV_HASH_VALUE=sq.HASH_VALUE))

and sq.SHARABLE_MEM>20*1024*1024

order by sq.SHARABLE_MEM/1024/1024;

*****************************************************************************************************

*********************************mon_redo 产生大量redo log的session和sql监控*********************************

select se.username,

se.sid,

se.serial#,

pr.spid,

se.status,

se.machine,

se.osuser,

round(st.value / 1024 / 1024) redosize,

sa.sql_text

from v$session se, v$sesstat st, v$sqlarea sa ,v$process pr

where se.sid = st.sid

and st.STATISTIC# =

(select STATISTIC# from v$statname where NAME = 'redo size')

and se.username is not null

and st.value > 50 * 1024 * 1024

and se.SQL_ADDRESS = sa.ADDRESS

and se.SQL_HASH_VALUE = sa.HASH_VALUE

and se.paddr=pr.addr

order by redosize;

*************************************************************************************************************

*********************************mon_temp 占用大量temp表空间的session和sql监控*********************************

select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_text

from v$sort_usage su, v$session se ,v$sqlarea sa

where su.session_addr=se.saddr

and se.SQL_ADDRESS = sa.ADDRESS

and se.SQL_HASH_VALUE = sa.HASH_VALUE

and su.extents>10;

select su.segtype, sum(su.extents) from v$sort_usage su group by su.segtype;

***************************************************************************************************************