• 透过V视图session,open_cursor,lock 诊断及定位问题根源


    clip_image002[9]

    SELECT round(bitand(s.ownerid, 65535)) parent_session_sid,

    round(bitand(s.ownerid,16711680)/65536) parent_session_instid, s.SADDR, s.SID,

    s.SERIAL#, s.AUDSID, s.PADDR, s.
    USER#, s.USERNAME, s.COMMAND, s.OWNERID,

    s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.
    SCHEMA#, s.SCHEMANAME, s.OSUSER,

    s.PROCESS, s.MACHINE, s.TERMINAL, 
    UPPER(s.PROGRAM) PROGRAM, s.TYPE,

    s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.PREV_SQL_ADDR,

    s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PLSQL_ENTRY_OBJECT_ID,

    s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE,

    s.MODULE_HASH, s.ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE,

    s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#,

    s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE,

    s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS,

    s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER,

    s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.SEQ#,

    s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT,

    s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME,

    s.SECONDS_IN_WAIT, s.STATE, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS,

    s.SQL_TRACE_BINDS, stat.cpu 
    - stat.CPU_this_call_start cpu_this_call, stat.CPU,

    stat.UGA_memory, stat.PGA_memory, stat.Commits, stat.Rollbacks, si.Block_Gets,

    si.Consistent_Gets, si.Physical_Reads, si.Block_Changes, si.Consistent_Changes 
    FROM V$SESSION S, V$SESS_IO si,

    (
    select ss.sid stat_sid,

    sum(decode(sn.name, 'CPU used when call started', ss.value, 0))

    CPU_this_call_start, 
    sum(decode(sn.name, 'CPU used by this session', ss.value, 0)) CPU,

    sum(decode(sn.name, 'session uga memory', ss.value, 0)) uga_memory,

    sum(decode(sn.name, 'session pga memory', ss.value, 0)) pga_memory,

    sum(decode(sn.name, 'user commits', ss.value, 0)) commits,

    sum(decode(sn.name, 'user rollbacks', ss.value, 0)) rollbacks

    from v$sesstat ss, v$statname sn

    where ss.STATISTIC# = sn.STATISTIC#

    and (sn.name = 'CPU used when call started' or

    sn.name 
    = 'CPU used by this session' or

    sn.name 
    = 'session uga memory' or

    sn.name 
    = 'session pga memory' or

    sn.name 
    = 'user commits' or

    sn.name 
    = 'user rollbacks')

    group by ss.sid) stat

    WHERE ( (s.USERNAME is not nulland (NVL(s.osuser,'x'<> 'SYSTEM'and

    (s.type 
    <> 'BACKGROUND') ) and (si.sid(+)=s.sid)

    and (stat.stat_sid = s.sid)

    --先用上面的查询列出当前session的状况

    select p.spid thread_1,s.username,

    decode(nvl(p.background,
    0),1,bg.description,

    s.program ) program,

    ss.value
    /100 CPU,physical_reads disk_io

    from v$process p,

    v$session s,

    v$sesstat ss,

    v$sess_io si,

    v$bgprocess bg

    where s.paddr=p.addr

    and ss.sid=s.sid

    and ss.statistic#=12

    and si.sid=s.sid

    and bg.paddr(+)=p.addr

    order by ss.value desc;

    --进程消耗的CPU,IO资源

    select a.sid session_id,b.spid process_id

    from v$session a,v$process b

    where a.paddr = b.addr and a.sid = (select sid from v$mystat where rownum = 1);

    --当前的session_id

    SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_ID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID=227

    --透过SESSION_ID 找process_ID

    select

    o.sid, o.sql_text, o.address, o.hash_value, o.
    user_name, s.schemaname

    from v$open_cursor o, v$session s

    where o.saddr = s.saddr

    and o.sid = s.sid

    and ( O.SID = '275')

    --定位好session ID后,可以开始查看这个sid最近所open的游标,可以找到部分cursor

    SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE

    HASH_VALUE
    =TO_NUMBER('561877498'ORDER BY PIECE

    --依靠hash_value可以继续往下定位详细的sql语句.

    SELECT

    SID, EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT

    , MAX_WAIT

    , TIME_WAITED_MICRO

    FROM v$session_event

    Where sid = '278'

    ORDER BY SID, TIME_WAITED DESC

    --依靠sid,可以抓取这个session最近的event时间总和

    SELECT

    SID, SEQ#, EVENT, WAIT_TIME, SECONDS_IN_WAIT, STATE, p1, p1text, p2,

    p2text, p3, p3text 
    FROM v$session_wait

    WHERE sid = '278'

    ORDER BY SID, SECONDS_IN_WAIT DESC

    --这个是查找sid的wait event

    SELECT LK.SID, SE.USERNAME, SE.OSUSER, SE.MACHINE, DECODE(LK.TYPE, 'TX''Transaction''TM''DML''UL''PL/SQL User Lock', LK.TYPE) LOCK_TYPE, DECODE(LK.LMODE, 0'None'1'Null'2'Row-S (SS)'3'Row-X (SX)'4'Share'5'S/Row-X (SSX)'6'Exclusive', TO_CHAR(LK.LMODE)) MODE_HELD, DECODE(LK.REQUEST, 0'None'1'Null'2'Row-S (SS)'3'Row-X (SX)'4'Share'5'S/Row-X (SSX)'6'Exclusive', TO_CHAR(LK.REQUEST)) MODE_REQUESTED, TO_CHAR(LK.ID1) LOCK_ID1, TO_CHAR(LK.ID2) LOCK_ID2, OB.OWNER, OB.OBJECT_TYPE, OB.OBJECT_NAME, DECODE(LK.BLOCK, 0'No'1'Yes'2'Global') BLOCK, SE.LOCKWAIT

    FROM V$LOCK LK

    INNER JOIN V$SESSION SE ON LK.SID = SE.SID

    LEFT JOIN DBA_OBJECTS OB ON LK.ID1 = OB.OBJECT_ID

    WHERE LK.TYPE IN ('TM''UL''TX')

    AND (LK.SID = '278');

    --当前sid的lock状况,当然啦,如果把最后的条件去掉,那就是当前数据库的全局lock状况.

    SELECT * FROM v$session_longops;

    --表扫描状态

    /*******************************************************************/

    /************************下面是trace部分****************************/

    /*******************************************************************/

    ALTER SESSION SET timed_statistics=true;

    ALTER SESSION SET max_dump_file_size=unlimited;

    ALTER SESSION SET tracefile_identifier='my_trace_session';

    --几个相关的参数

    --正常情况,一般都是直接上10046

    ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

    ALTER SESSION SET EVENTS '10046 trace name context off';

    exec dbms_monitor.serv_mod_act_trace_enable(service_name=>' ', module_name=>' ');

    --使用 SERVICE_NAME,module_name来跟踪

    exec dbms_monitor.client_id_trace_enable(client_id=>' ');

    -- 使用CLIENT_IDENTIFIER来跟踪

    exec dbms_monitor.session_trace_enable(139);

    --如果定位得到session_id的话,就可以直接依靠session_ID跟踪

    CREATE OR REPLACE TRIGGER trace_test_user AFTER LOGON ON DATABASE

    BEGIN

    IF USER LIKE '%\_test' ESCAPE '\' THEN

    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';

    EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';

    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';

    END IF;

    END;

    /

    -- 代码来自《Optimazing Oracle Performance》 P116

    --触发器来做trace,一般属于最后的杀手锏.

    /***********通过OS PID,使用oradebug来做trace**************/

    SELECT S.USERNAME,

    P.SPID OS_PROCESS_ID,

    P.PID ORACLE_PROCESS_ID

    FROM V$SESSION S, V$PROCESS P

    WHERE S.PADDR = P.ADDR

    --先找出OS PID

    oradebug setospid 
    9999;

    --set os PID

    oradebug unlimit;

    -- 设置Trace文件大小

    oradebug event 
    10046 trace name context forever ,level 12;

    -- 开启级别为12的Trace

    Oradebug event 
    10046 trace name context off;

    --关闭trace

    /*使用DBMS_SYSTEM.SET_EV包, 也可以进行基于SID,SERIAL的跟踪*/

    EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 1004612'');

    -- 开启level 12的Trace

    EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 100460'');

    -- 关闭Trace

    /*DBMS_SUPPORT包默认情况下并没有包含在数据库中,需要通过运行$ORACLE_HOME/rdbms/admin/dbmssupp.sql 安装之后才能使用。*/

    --开启自身进程:

    EXEC SYS.DBMS_SUPPORT.START_TRACE(true, true);

    EXEC SYS.DBMS_SUPPORT.STOP_TRACE();

    --开启其他的进程

    EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, true, true);

    EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(:sid, :serial);

    /*******************************使用DBMS_MONITOR包************************************/

    --当前

    EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);

    EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();

    --其他的进程

    EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);

    EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);

    --依靠client_identifier来trace

    EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('client_name', true, true);

    EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('client_name');

    /*************************************数据库级的trace**********************************/

    EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(true, true);

    EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE(); 
    作者:Daaprk
    可以转载,但必须以超链接形式标明文章原始出处和作者信息.
  • 相关阅读:
    编写安全有效的 C# 代码
    模式匹配
    C#新特性
    转 C# .NET4.0 混合模式程序集异常
    win7 64位系统 注册 ocx控件
    TIFF图像文件格式详解
    GDALOGR读取数据示例 C#版本
    使用gdal C#封装库读取DEM数据
    编译C#环境下GDAL(支持HDF4、NetCDF)
    UML类图符号 各种关系说明以及举例
  • 原文地址:https://www.cnblogs.com/dap570/p/1742058.html
Copyright © 2020-2023  润新知