当前位置：首页 > news >正文

Oracle 死锁、指标汇总

news 来源：原创 2024/5/18 15:57:03

一、Oracle RAC 架构

待~

"SYS"."V_$SESSION" 与 "SYS"."GV_$SESSION"的区别
哪个列可以区分V$和GV$: 添加条件 S.INST_ID = USERENV('INSTANCE')

SELECT * FROM GV$SESSION S WHERE S.USERNAME = 'SCOTT' AND S.INST_ID = USERENV('INSTANCE')

查看当前是第几个实例

SELECT USERENV('INSTANCE') FROM DUAL;

二、Oracle 死锁的查询与处理

-- 1.查看EVENT字段中是否出现row block,则表示此SQL被阻塞
SELECT'alter system disconnect session ''' || s.sid || ', ' || s.serial# || ''' immediate;'  kill_session,s.inst_id,s.event, --是否含有row blocks.blocking_session, -- 阻塞别人SQL的那条sids.last_call_et,a.sql_text,s.machine,s.osuser,s.program,s.module,s.action,s.username,a.sql_id,a.child_number,s.logon_time,s.service_name,a.sql_fulltext
FROMgv$session s,gv$sql a  
WHEREs.sql_id = a.sql_id (+)AND   s.sql_child_number = a.child_number (+)AND   s.status = 'ACTIVE'AND   s.username <> 'SYS'AND   s.last_call_et > 10AND   s.inst_id = a.inst_id
ORDER BY  s.last_call_et DESC; -- 2.知道堵塞别人的sid后，根据sid和inst_id 查找出serial#
select * from gv$session where sid=1522 and inst_id=2;-- 3.然后去对应的实例上，替换'sid,serial#'的sid与serial#,杀死阻塞别人的session
alter system disconnect session 'sid,serial#' immediate;-- 4.确认有没有杀干净，正常情况下结果为空，否则用步骤3再杀一次
select a.spid,b.sid,b.serial#,b.username,b.osuser,a.program from gv$process a,gv$session b where a.ADDR=b.PADDR and b.STATUS='KILLED'
and a.inst_id = b.inst_id;-- 5.通过步骤1的SQL_ID，确认有没有同一SQL_ID，但status是INACTIVE的，用步骤3杀掉
select * from gv$session a where a.SQL_ID='fmcuc8whh75ys';

三、Oracle查询实时运行语句及时间花费

--查询实时运行的语句
SELECT'ps -ef | grep '|| spid ps,b.sid,b.username,a.sql_id,b.inst_id,b.event,round(b.last_call_et / 3600) "ses_T(小时)",trunc( ( (a.elapsed_time / DECODE(executions,0,1,executions) ) / 1000000),2) "etime(s)",round(a.buffer_gets / DECODE(a.executions,0,1,a.executions) ) per_bu,a.executions,a.sql_fulltext fullsql,a.sql_text,a.disk_reads,a.buffer_gets,b.osuser,b.machine,b.program,a.module,a.cpu_time,a.last_load_time,a.last_active_time
FROMgv$sqlarea a, gv$session b, gv$process p
WHEREexecutions >= 0AND   b.status = 'ACTIVE'AND   a.hash_value = b.sql_hash_valueAND   a.sql_id = b.sql_idAND   b.paddr = p.addrAND   a.inst_id = b.inst_idAND   b.inst_id = p.inst_id
ORDER BY round(b.last_call_et / 3600) desc;--( cpu_time / DECODE(executions,0,1,executions) ) DESC,a.buffer_gets DESC,a.executions DESC,a.sql_id;

四、Oracle各项指标查询

--1.Oracle正在执行的语句和拼接杀进程
SELECT 'ALTER SYSTEM KILL SESSION ''' || A.SID ||',' ||A.SERIAL#||''' IMMEDIATE;' AS KILL_SESSION,A.SID,A.SERIAL#,A.USERNAME,A.INST_ID,A.MACHINE,A.PADDR,B.SPID,C.SQL_TEXT,C.SQL_FULLTEXT       
FROM GV$SESSION A, GV$PROCESS B, GV$SQLAREA C 
WHERE A.PADDR = B.ADDR AND A.SQL_HASH_VALUE = C.HASH_VALUE --AND A.OSUSER = '系统用户名'AND A.INST_ID = B.INST_ID AND B.INST_ID = C.INST_ID;--1.1 当前用户正在执行的语句
SELECTa.sid,a.serial#,a.paddr,a.username,a.inst_id,a.machine,A.SQL_ID,A.PREV_SQL_ID,nvl(a.sql_id,a.prev_sql_id) sql_id,b.sql_text,b.sql_fulltext,b.executions,b.first_load_time,b.last_load_time,b.last_active_time,b.disk_reads,b.direct_writes,b.buffer_gets
FROM            
gv$session a, gv$sql b
WHERE a.username = sys_context('USERENV','CURRENT_USER') -- 获取当前的用户
AND a.status = 'ACTIVE'
AND nvl(a.sql_id,a.prev_sql_id) = b.sql_id
AND a.inst_id = b.inst_id;--2.锁表语句和拼接杀进程
SELECT 'ALTER SYSTEM KILL SESSION ''' || A.SID ||',' ||A.SERIAL#||''' IMMEDIATE;' AS KILL_SESSION,B.SESSION_ID,A.SERIAL#,A.EVENT,             --是否含有row blockA.BLOCKING_SESSION,  -- 阻塞别人SQL的那条sidB.LOCKED_MODE,B.ORACLE_USERNAME,A.USER#,B.OS_USER_NAME,A.MACHINE,A.TERMINAL,C.SQL_TEXT,C.ACTION
FROM GV$SESSION A, GV$LOCKED_OBJECT B, GV$SQLAREA C 
WHERE A.SID = B.SESSION_ID AND A.PREV_SQL_ADDR = C.ADDRESSAND A.INST_ID = B.INST_ID AND B.INST_ID = C.INST_IDAND A.STATUS = 'ACTIVE' AND A.USERNAME <> 'SYS'
ORDER BY A.SID, A.SERIAL#;--3.最耗费CPU
SELECT  TRUNC(CPU_TIME / 1000000 / decode(executions, 0, 1, executions), 2) CPU_SEC,BUFFER_GETS,DISK_READS,SORTS,EXECUTIONS,SQL_TEXT,ROWS_PROCESSED
FROM GV$SQLSTATS ORDER BY CPU_SEC DESC;--4.汇总指标
SELECTt.inst_id,t.sql_id,t.last_active_time,t.sql_profile,         --如果该字段有值，就是按固化走执行计划t.plan_hash_value,t.child_number      "执行计划版本号",trunc( (t.cpu_time / t.executions / 1000000),4)  "每次cpu时间",trunc( (t.elapsed_time - t.cpu_time) / t.executions / 1000000,4) "每次等待时间",t.executions        "总执行次数",round(t.rows_processed / t.executions,2) "平均返回行数",trunc(t.elapsed_time / t.executions / 1000000,4) "每次执行（秒）",trunc( (t.buffer_gets / t.executions / 1000000),4) "每次逻辑读",trunc( (t.disk_reads / t.executions / 1000000),4) "每次物理读",trunc( (t.cluster_wait_time / t.executions / 1000000),4) "每次集群等待",trunc( (t.user_io_wait_time / t.executions / 1000000),4) "每次io等待",trunc( (t.application_wait_time / t.executions / 1000000),4) "每次应用等待",trunc( (t.concurrency_wait_time / t.executions / 1000000),4) "每次并发等待",t.first_load_time   "首次硬解析时间",t.last_load_time    "上次硬解析时间",t.module,t.action,t.parsing_schema_name,trunc(t.elapsed_time / 1000000,4) "执行时间（秒）",trunc(t.cpu_time / 1000000,4) "cpu时间",t.parse_calls       "总解析次数",t.loads             "硬解析次数",t.buffer_gets,t.cluster_wait_time,t.user_io_wait_time,t.application_wait_time,t.concurrency_wait_time,t.plan_hash_value,t.sql_fulltextFROMgv$sql tWHEREt.executions > 0ORDER BYt.cpu_time DESC;--5.最耗缓存
SELECTsql_fulltext   sql,buffer_gets,executions,buffer_gets / executions "Gets/Exec",hash_value,address,last_active_time
FROMv$sqlarea
WHEREbuffer_gets > 10000
ORDER BYbuffer_gets DESC;--6.最多物理读取
SELECTsql_fulltext   sql,disk_reads,executions,disk_reads / executions "Reads/Exec",hash_value,address,last_active_time
FROMv$sqlarea
WHEREdisk_reads > 1000
ORDER BYdisk_reads DESC;--7.最多执行
SELECTsubstr(sql_text,1,40) sql,sql_fulltext,executions,rows_processed,rows_processed / executions "Rows/Exec",hash_value,address,last_active_time
FROMv$sqlarea
WHEREexecutions > 100
ORDER BYexecutions DESC;-- 8.最耗内存
SELECTsubstr(sql_text,1,40) sql,sharable_mem,executions,hash_value,address,last_active_time
FROMv$sqlarea
WHEREsharable_mem > 1048576
ORDER BYsharable_mem DESC;--9.阻塞等待语句
SELECTblocksession.sid          AS block_session_sid,blocksession.serial#      AS block_session_serial#,blocksession.username     AS block_session_username,blocksession.osuser       AS block_session_osuser,blocksession.machine      AS block_session_machine,blocksession.status       AS block_session_status,blockobject.object_name   AS blocked_table,waitsession.sid           AS wait_session_sid,waitsession.serial#       AS wait_session_serial#,waitsession.username      AS wait_session_username,waitsession.osuser        AS wait_session_osuser,waitsession.machine       AS wait_session_machine,waitsession.status        AS wait_session_status
FROMv$lock blocklock,v$lock waitlock,v$session blocksession,v$session waitsession,v$locked_object lockedobject,dba_objects blockobject
WHEREblocklock.block = 1AND blocklock.sid != waitlock.sidAND blocklock.id1 = waitlock.id1AND blocklock.id2 = waitlock.id2AND blocklock.sid = blocksession.sidAND waitlock.sid = waitsession.sidAND lockedobject.session_id = blocksession.sidAND lockedobject.object_id = blockobject.object_id;    --10.失效索引查询与重建    
SELECT'alter index '|| a.index_name|| ' rebuild online;',a.*
FROMuser_indexes a
WHEREa.status <> 'VALID'AND a.index_name NOT LIKE '%$$';

参考资料：

https://www.cnblogs.com/shouke/p/17437893.html

https://docs.oracle.com/database/121/REFRN/GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75.htm#REFRN30246

https://docs.oracle.com/database/121/REFRN/GUID-09D5169F-EE9E-4297-8E01-8D191D87BDF7.htm#REFRN30259

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-SESSION.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-LOCKED_OBJECT.html#GUID-3F9F26AA-197F-4D36-939E-FAF1EFD8C0DD