Oracle 死锁、指标汇总

2024-03-14 06:22:04
开发
16

一、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 block
    s.blocking_session, -- 阻塞别人SQL的那条sid
    s.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
FROM
    gv$session s,
    gv$sql a  
WHERE
    s.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 > 10
    AND   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
FROM
    gv$sqlarea a, gv$session b, gv$process p
WHERE
    executions >= 0
    AND   b.status = 'ACTIVE'
    AND   a.hash_value = b.sql_hash_value
    AND   a.sql_id = b.sql_id
    AND   b.paddr = p.addr
    AND   a.inst_id = b.inst_id
    AND   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 当前用户正在执行的语句
SELECT
        a.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 block
        A.BLOCKING_SESSION,  -- 阻塞别人SQL的那条sid
        B.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.ADDRESS
      AND A.INST_ID = B.INST_ID AND B.INST_ID = C.INST_ID
      AND 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.汇总指标
SELECT
        t.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_fulltext
    FROM
        gv$sql t
    WHERE
        t.executions > 0
    ORDER BY
        t.cpu_time DESC;

--5.最耗缓存
SELECT
    sql_fulltext   sql,
    buffer_gets,
    executions,
    buffer_gets / executions "Gets/Exec",
    hash_value,
    address,
    last_active_time
FROM
    v$sqlarea
WHERE
    buffer_gets > 10000
ORDER BY
    buffer_gets DESC;
    
    
--6.最多物理读取
SELECT
    sql_fulltext   sql,
    disk_reads,
    executions,
    disk_reads / executions "Reads/Exec",
    hash_value,
    address,
    last_active_time
FROM
    v$sqlarea
WHERE
    disk_reads > 1000
ORDER BY
    disk_reads DESC;
 
    
--7.最多执行
SELECT
    substr(sql_text,1,40) sql,
    sql_fulltext,
    executions,
    rows_processed,
    rows_processed / executions "Rows/Exec",
    hash_value,
    address,
    last_active_time
FROM
    v$sqlarea
WHERE
    executions > 100
ORDER BY
    executions DESC;


-- 8.最耗内存
SELECT
    substr(sql_text,1,40) sql,
    sharable_mem,
    executions,
    hash_value,
    address,
    last_active_time
FROM
    v$sqlarea
WHERE
    sharable_mem > 1048576
ORDER BY
    sharable_mem DESC;
      
--9.阻塞等待语句
SELECT
    blocksession.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
FROM
    v$lock blocklock,
    v$lock waitlock,
    v$session blocksession,
    v$session waitsession,
    v$locked_object lockedobject,
    dba_objects blockobject
WHERE
    blocklock.block = 1
    AND blocklock.sid != waitlock.sid
    AND blocklock.id1 = waitlock.id1
    AND blocklock.id2 = waitlock.id2
    AND blocklock.sid = blocksession.sid
    AND waitlock.sid = waitsession.sid
    AND lockedobject.session_id = blocksession.sid
    AND lockedobject.object_id = blockobject.object_id;    
 
--10.失效索引查询与重建    
SELECT
    'alter index '
    || a.index_name
    || ' rebuild online;',
    a.*
FROM
    user_indexes a
WHERE
    a.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

原文地址:https://blog.csdn.net/poyue8754/article/details/136666851 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1768039852663771136.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部

Oracle 死锁、指标汇总

相关推荐

最近更新

热门阅读