|
*********************************查询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;
***************************************************************************************************************
|
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
