'IT & 인생'에 해당되는 글 473건

1. v$active_session_history 기준, Session 조회

set pagesize 300
set lines 200
set echo off
set feedback off
set timing off
set verify off
col spid format a8
col no format 99
col sid format 99999
col sql_id format a13
col stime format a15
col serial# format 9999999
col event format a25
col module format a12
col command format a6
col waiting_on format a50
col block format 99999
col TIME for 9999.99
col s for a1

prompt *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
prompt *                                    < Active Session History  >                                    *
ACCEPT begin_time CHAR PROMPT "Enter the begin_time(YYYYMMDD/HH24/MI, default : 1 hour before) : "
ACCEPT end_time CHAR PROMPT   "Enter the end_time  (YYYYMMDD/HH24/MI, default :  current time) : "
select session_id sid,
       to_char(SAMPLE_TIME, 'YYMMDD HH24:MI:SS') STIME,
       SQL_ID,
       decode(SQL_OPCODE, '0', 'NO', '1', 'Create Tab', '2', 'Insert', '3', 'Select', '6', 'Update', '7'

, 'Delete', '9', 'Create Idx', '10', 'Drop Idx', '15', 'Alter Tbl', '24', 'Create Proc', '32', 'Create Link', '33'

, 'Drop Link', '36', 'Create RBS', '37', 'Alter RBS', '38', 'Drop RBS', '40', 'Alter TBS', '41', 'Drop TBS'

, '42', 'Alter Sess', '45', 'Rollback', '47', 'PL/SQL', '62', 'Anal Table', '63', 'Anal Index', '85', 'Truncate') Command,
       substr(module, 1, 12) module,
       decode(WAIT_TIME, 0, 'W', 'R') s,
       TIME_WAITED/10000 TIME,
       blocking_session block,
       rtrim(event)||': '||(SELECT OBJECT_NAME
        FROM   DBA_OBJECTS
        WHERE  OBJECT_ID = CURRENT_OBJ#) as waiting_on
from   v$active_session_history
where  sample_time between nvl(to_timestamp('&begin_time', 'YYYYMMDD/HH24/MI'), sysdate-2/24) and nvl(to_timestamp('&end_time', 'YYYYMMDD/HH24/MI'), sysdate)
and    event not like '%pmon timer%'
and    event not like '%rdbms ipc%'
and    event not like '%smon timer%'
and    event not like '%SQL*Net message%'
and    event not like '%lock manager wait for%'
and    event not like '%slave wait%'
and    event not like '%io done%'
and    event not like '%queue messages%'
and    event not like '%wakeup time%'
order by STIME, sid;
prompt *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
exit

2. v$session기준, Session 조회

set lines 300
set pages 700
set echo off
set feedback off
set verify off
set pause off
set timing off
col username format a10
col sid format 9999
col serial# format 999999
col spid format a8
col osuser format a8
col status format a8
col machine format a9
col module format a15
col command format a6
col program format a9
col event format a23
col l_read format 999999999
col s_ratio format 999
col time for 9999
col sql_id for a13
col hash_value for 9999999999
select /*+ ordered */ s.username, s.sid, s.serial#, p.spid, substr(s.osuser,1,8) osuser, 
case 
when s.command=1                then 'CREATE TABLE'
when s.command=2                then 'INSERT'
when s.command=3                then 'SELECT'
when s.command=6                then 'UPDATE'
when s.command=7                then 'DELETE'
when s.command=9                then 'CREATE INDEX'
when s.command=10               then 'DROP INDEX'
when s.command=11               then 'ALTER INDEX'
when s.command=12               then 'DROP TABLE'
when s.command=13               then 'CREATE SEQUENCE'
when s.command=14               then 'ALTER SEQUENCE'
when s.command=15               then 'ALTER TABLE'
when s.command=16               then 'DROP SEQUENCE'
when s.command=22               then 'DROP VIEW'
when s.command=24               then 'CREATE PROCEDURE'
when s.command=25               then 'ALTER PROCEDURE'
when s.command=28               then 'RENAME'
when s.command=39               then 'CREATE TABLESPACE'
when s.command=40               then 'ALTER TABLESPACE'
when s.command=41               then 'DROP TABLESPACE'
when s.command=42               then 'ALTER SESSION'
when s.command=44               then 'COMMIT'
when s.command=45               then 'ROLLBACK'
when s.command=46               then 'SAVEPOINT'
when s.command=47               then 'PL/SQL'
when s.command=48               then 'SET TRANSACTION'
when s.command=49               then 'ALTER SYSTEM'
when s.command=50               then 'EXPLAIN'
when s.command=62               then 'ANALYZE TABLE'
when s.command=63               then 'ANALYZE INDEX'
when s.command=64               then 'ANALYZE CLUSTER'
when s.command=71               then 'CREATE MATERIALIZED VIEW LOG'
when s.command=72               then 'ALTER MATERIALIZED VIEW LOG'
when s.command=73               then 'DROP MATERIALIZED VIEW LOG'
when s.command=74               then 'CREATE MATERIALIZED VIEW'
when s.command=75               then 'ALTER MATERIALIZED VIEW'
when s.command=76               then 'DROP MATERIALIZED VIEW'
when s.command=85               then 'TRUNCATE TABLE'
when s.command=86               then 'TRUNCATE CLUSTER'
when s.command=91               then 'CREATE FUNCTION'
when s.command=92               then 'ALTER FUNCTION'
when s.command=93               then 'DROP FUNCTION'
when s.command=94               then 'CREATE PACKAGE'
when s.command=95               then 'ALTER PACKAGE'
when s.command=96               then 'DROP PACKAGE'
when s.command=97               then 'CREATE PACKAGE BODY'
when s.command=98               then 'ALTER PACKAGE BODY'
when s.command=99               then 'DROP PACKAGE BODY'          
ELSE 'UNKWON' 
end command
,s.status, substr(s.event,1,23) event, 
si.consistent_gets + si.block_gets L_READ,
-- substr(s.machine,1,8) machine, 
substr(s.module,1,9) module,
s.last_call_et time,
s.sql_id
from v$session s, v$process p,  v$sess_io si
where s.paddr= p.addr and s.status <> 'INACTIVE' 
      and s.sid=si.sid(+) 
      and s.type <> 'BACKGROUND' 
      and s.event not like 'jobq slave wait%'
      and s.event not like 'single-task message%'
      and s.event not like 'Streams AQ%'
      and s.event not like 'pipe get%'
      and s.event not like 'wakeup time manager%'
      and s.event not like 'rdbms ipc message%'
      and s.event not like 'queue messages%'      
order by s.username asc, s.sid, s.serial# ;

 SELECT sysmetric_history.sample_time,

         cpu / 60 AS cpu,

         bcpu / 60 AS bcpu,

         DECODE (SIGN ( (cpu + bcpu) / 60 - cpu_ora_consumed),

                 -1, 0,

                 ( (cpu + bcpu) / 60 - cpu_ora_consumed))

            AS cpu_ora_wait,

         scheduler / 60 AS scheduler,

         uio / 60 AS uio,

         sio / 60 AS sio,

         concurrency / 60 AS concurrency,

         application / 60 AS application,

         COMMIT / 60 AS COMMIT,

         configuration / 60 AS configuration,

         administrative / 60 AS administrative,

         network / 60 AS network,

         queueing / 60 AS queueing,

         clust / 60 AS clust,

         other / 60 AS other

    FROM (SELECT TRUNC (sample_time, 'MI') AS sample_time,

                 DECODE (

                    session_state,

                    'ON CPU', DECODE (session_type,

                                      'BACKGROUND', 'BCPU',

                                      'ON CPU'),

                    wait_class)

                    AS wait_class

            FROM v$active_session_history

           WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI') -- 일자 조정 필요

                 AND sample_time <= TRUNC (SYSDATE, 'MI'))               --ASH

                                                          PIVOT (COUNT (*)

                                                           FOR wait_class

                                                           IN  ('ON CPU' AS cpu,

                                                               'BCPU' AS bcpu,

                                                               'Scheduler' AS scheduler,

                                                               'User I/O' AS uio,

                                                               'System I/O' AS sio,

                                                               'Concurrency' AS concurrency,

                                                               'Application' AS application,

                                                               'Commit' AS COMMIT,

                                                               'Configuration' AS configuration,

                                                               'Administrative' AS administrative,

                                                               'Network' AS network,

                                                               'Queueing' AS queueing,

                                                               'Cluster' AS clust,

                                                               'Other' AS other)) ash,

         (SELECT TRUNC (begin_time, 'MI') AS sample_time,

                 VALUE / 100 AS cpu_ora_consumed

            FROM v$sysmetric_history              -- 해당 최대 BEGIN_TIME까지만 조회 가능

           WHERE GROUP_ID = 2 AND metric_name = 'CPU Usage Per Sec') sysmetric_history

   WHERE ash.sample_time(+) = sysmetric_history.sample_time

ORDER BY sample_time;

/* Formatted on 2015-02-25 15:29:21 (QP5 v5.163.1008.3004) */

-- v$active_session_history,v$sysmetric_history

-- With CPU wait

-- With Oracle pre 11g

  SELECT sysmetric_history.sample_time,

         cpu,

         bcpu,

         DECODE (SIGN ( (cpu + bcpu) - cpu_ora_consumed),

                 -1, 0,

                 ( (cpu + bcpu) - cpu_ora_consumed))

            AS cpu_ora_wait,

         scheduler,

         uio,

         sio,

         concurrency,

         application,

         COMMIT,

         configuration,

         administrative,

         network,

         queueing,

         clust,

         other

    FROM (  SELECT TRUNC (sample_time, 'MI') AS sample_time,

                   SUM (

                      DECODE (

                         session_state,

                         'ON CPU', DECODE (session_type, 'BACKGROUND', 0, 1),

                         0))

                   / 60

                      AS cpu,

                   SUM (

                      DECODE (

                         session_state,

                         'ON CPU', DECODE (session_type, 'BACKGROUND', 1, 0),

                         0))

                   / 60

                      AS bcpu,

                   SUM (DECODE (wait_class, 'Scheduler', 1, 0)) / 60 AS scheduler,

                   SUM (DECODE (wait_class, 'User I/O', 1, 0)) / 60 AS uio,

                   SUM (DECODE (wait_class, 'System I/O', 1, 0)) / 60 AS sio,

                   SUM (DECODE (wait_class, 'Concurrency', 1, 0)) / 60

                      AS concurrency,

                   SUM (DECODE (wait_class, 'Application', 1, 0)) / 60

                      AS application,

                   SUM (DECODE (wait_class, 'Commit', 1, 0)) / 60 AS COMMIT,

                   SUM (DECODE (wait_class, 'Configuration', 1, 0)) / 60

                      AS configuration,

                   SUM (DECODE (wait_class, 'Administrative', 1, 0)) / 60

                      AS administrative,

                   SUM (DECODE (wait_class, 'Network', 1, 0)) / 60 AS network,

                   SUM (DECODE (wait_class, 'Queueing', 1, 0)) / 60 AS queueing,

                   SUM (DECODE (wait_class, 'Cluster', 1, 0)) / 60 AS clust,

                   SUM (DECODE (wait_class, 'Other', 1, 0)) / 60 AS other

              FROM v$active_session_history

             WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI')                -- 간격조정

                   AND sample_time <= TRUNC (SYSDATE, 'MI')

          GROUP BY TRUNC (sample_time, 'MI')) ash,

         (SELECT TRUNC (begin_time, 'MI') AS sample_time,

                 VALUE / 100 AS cpu_ora_consumed

            FROM v$sysmetric_history -- 해당 최대 BEGIN_TIME까지만 조회 가능

           WHERE GROUP_ID = 2 AND metric_name = 'CPU Usage Per Sec') sysmetric_history

   WHERE ash.sample_time(+) = sysmetric_history.sample_time

ORDER BY sample_time;

/* Formatted on 2015-02-25 15:45:53 (QP5 v5.163.1008.3004) */

--Without CPU Wait

-- v$active_session_history

SELECT sample_time,

       cpu / 60 AS cpu,

       bcpu / 60 AS bcpu,

       scheduler / 60 AS scheduler,

       uio / 60 AS uio,

       sio / 60 AS sio,

       concurrency / 60 AS concurrency,

       application / 60 AS application,

       COMMIT / 60 AS COMMIT,

       configuration / 60 AS configuration,

       administrative / 60 AS administrative,

       network / 60 AS network,

       queueing / 60 AS queueing,

       clust / 60 AS clust,

       other / 60 AS other

  FROM (SELECT TRUNC (sample_time, 'MI') AS sample_time,

               DECODE (

                  session_state,

                  'ON CPU', DECODE (session_type,

                                    'BACKGROUND', 'BCPU',

                                    'ON CPU'),

                  wait_class)

                  AS wait_class

          FROM v$active_session_history

         WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI') -- 일자 조정 필요

               AND sample_time <= TRUNC (SYSDATE, 'MI'))                 --ash

                                                        PIVOT (COUNT (*)

                                                         FOR wait_class

                                                         IN  ('ON CPU' AS cpu,

                                                             'BCPU' AS bcpu,

                                                             'Scheduler' AS scheduler,

                                                             'User I/O' AS uio,

                                                             'System I/O' AS sio,

                                                             'Concurrency' AS concurrency,

                                                             'Application' AS application,

                                                             'Commit' AS COMMIT,

                                                             'Configuration' AS configuration,

                                                             'Administrative' AS administrative,

                                                             'Network' AS network,

                                                             'Queueing' AS queueing,

                                                             'Cluster' AS clust,

                                                             'Other' AS other));

1. Hang 발생시 해당 명령어로 접근 후, oradebug 수행

 # sqlplus –prelim “/ as sysdba”

   SQL>alter system set events ‘immediate trace name ashdump level 10’;

   SQL> oradebug setmypid

   SQL> oradebug unlimit

   SQL> oradebug dump ashdump 10 (level 10) --ASHDUMP 생성

   SQL> oradebug tracefile_name

2. DB 정상화 후, ASHDUMP파일을 SQL Loader로 테이블 생성 

# sqlldr userid=test/test control='/home/oracle/ashldr.ctl'

data='/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28661.trc‘

3. ASHDUMP 내역을 확인하여 이슈 분석

 SQL> select * from ashdump;