'1. IT Story/Scripts'에 해당되는 글 75건

SELECT  A.AA CURRENT_PLAN
      , B.AA HIST_PLAN
      , CASE 
            WHEN A.AA <> B.AA OR A.AA IS NULL OR B.AA IS NULL 
                 THEN 'X' 
        END TP
FROM    (
            SELECT  /*+ NO_MERGE */
                    TO_CHAR(DBID) DBID, SQL_ID, PLAN_HASH_VALUE      
                  , ID
                  , LPAD(' ', DEPTH*2)
                    ||TRIM(OPERATION||' '||OPTIONS||' '||OBJECT_OWNER
                    ||CASE WHEN OBJECT_NAME IS NOT NULL THEN '.'||OBJECT_NAME END)
                    ||CASE WHEN PARTITION_START IS NOT NULL THEN ': '
                    ||PARTITION_START||' '||PARTITION_STOP END AS AA
            FROM    DBA_HIST_SQL_PLAN
            WHERE   SQL_ID  = :V_SQL_ID
            AND     DBID    = (
                        SELECT  DBID
                        FROM    V$DATABASE
                    )
        ) B
        FULL OUTER JOIN
        (
            SELECT
                    'CURRENT' DBID, SQL_ID, PLAN_HASH_VALUE      
                  , ID
                  , LPAD(' ', DEPTH*2)
                    ||TRIM(OPERATION||' '||OPTIONS||' '||OBJECT_OWNER
                    ||CASE WHEN OBJECT_NAME IS NOT NULL THEN '.'||OBJECT_NAME END)
                    ||CASE WHEN PARTITION_START IS NOT NULL THEN ': '
                    ||PARTITION_START||' '||PARTITION_STOP END AS AA
            FROM    V$SQL_PLAN
            WHERE   SQL_ID          = :V_SQL_ID
            AND     CHILD_NUMBER    = 0
        ) A
        ON (
           A.ID = B.ID
        )
ORDER BY
        A.ID;

1) Oracle 계정으로 접근

2) DB/Listener SHUTDOWN

 명령어: shutdown immediate

3) 환경변수설정

  명령어: . ./. profile or bash_profile

4) umask 수행

  명령어: umask 022

5) slibclean 수행

 - AIX OS의 경우에만 root 계정으로 slibclean을 실행하고 5분 후에 다시 한번 slibclean을 실행

 명령어: slibclean 

6) Relink 수행

 명령어: $ORACLE_HOME/bin/relink all >> relink.out

7) 로그 확인

 명령어: $ORACLE_HOME/install/relink.log

8) root 유저로 rdbms home의 root.sh 수행

 명령어: cd ~oracle/.profile (Oracle 환경변수 적용) 
           echo $ORACLE_HOME
           $ORACLE_HOME/root.sh

1) root 계정으로 접근 및 Grid 환경변수 적용

 명령어: cd ~oragrid -> . ./.profile (환경변수 설정)

2) Grid Shotdown

 명령어: crsctl stop crs

3) Grid 엔진 unlock

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/crs/install/roothas.pl - unlock

4) Oragrid 계정으로 접근

5) config.o 백업 

 명령어: ls -l $ORACLE_HOME/rdbms/lib/config.o
           mv $ORACLE_HOME/rdbms/lib/config.o $ORACLE_HOME/rdbms/lib/config.o_BAK
           ls -l $ORACLE_HOME/rdbms/lib/config.o*

6) Relink 수행

  명령어: $ORACLE_HOME/bin/relink all

7) 로그 확인

  명령어: $ORACLE_HOME/install/relink.log

8) root 계정으로 접근 및 root.sh 수행

  명령어: cd ~oragrid -> . ./.profile (환경변수 설정)
            $ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
            $ORACLE_HOME/crs/install/roothas.pl -patch

9) 상태 확인
 명령어 crsctl stat res -t

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;

1. DB LEVEL XID 확인 

SELECT  concat(datname,':', substr((age(datfrozenxid)/1000000000.0)::text,0,5), 'billion') AS msg 
FROM    pg_database 
where   datname = current_database() 
and     age(datfrozenxid) >= 1000000000 (20억 초과시 에러) 
ORDER BY  
        age(datfrozenxid) desc ; 


2. TABLE LEVEL XID 확인  

select  
        concat(current_database(), '.', c.relname, ':', substr((greatest( age(c.relfrozenxid) 

        , age(t.relfrozenxid) )/ 1000000000.0) ::text, 0, 5), 'billion' ) as msg , 
        greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as get_stat_disk_usge 
from    pg_class c 
left join pg_class t  
        on ( c.reltoastrelid = t.oid ) 
where   c.relkind in ('r','m') 
and     c.oid::regclass::text not like 'pg_%' 
and     c.oid::regclass::text not like 'public%' 
and     c.oid::regclass::text not like 'information_schema_%' 
and     greatest(age(c.relfrozenxid), age(t.relfrozenxid))  >= 1000000000 (20억 초과시 에러)