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

Oracle 11g 부터는 물리적 경로에 있는 alert log를 아래와 같은 절차를 통하여 SQL문으로 확인해 볼수 있다.

SET SERVEROUTPUT ON

SPOOL ALERT.LOG

DECLARE

   V_PERCENT    NUMBER := .05;

   V_BDUMP      VARCHAR2 (1000);

   V_NAME       VARCHAR2 (20);

   V_FILE       VARCHAR2 (20);

   V_LINE       VARCHAR2 (8000);

   V_HANDLE     UTL_FILE.FILE_TYPE;

   V_DIR_STMT   VARCHAR2 (100);

   V_SIZE       INTEGER;

   V_BSIZE      INTEGER;

   V_EXISTS     BOOLEAN;

BEGIN

   SELECT VALUE

     INTO V_BDUMP

     FROM V$PARAMETER

    WHERE NAME = 'background_dump_dest';

   DBMS_OUTPUT.PUT_LINE (

      '**************************** INFO *************************');

   DBMS_OUTPUT.PUT_LINE ('BDUMP: ' || V_BDUMP);

   SELECT VALUE

     INTO V_NAME

     FROM V$PARAMETER

    WHERE NAME = 'instance_name';

   DBMS_OUTPUT.PUT_LINE ('DB NAME: ' || V_NAME);

   V_FILE := 'alert_' || V_NAME || '.log';

   DBMS_OUTPUT.PUT_LINE ('FILENAME: ' || V_FILE);

   V_DIR_STMT := 'CREATE DIRECTORY ALERT AS ''' || V_BDUMP || '''';

   DBMS_OUTPUT.PUT_LINE ('COMMAND: ' || V_DIR_STMT);

   EXECUTE IMMEDIATE V_DIR_STMT;

   V_HANDLE := UTL_FILE.FOPEN ('ALERT', V_FILE, 'r');

   UTL_FILE.FGETATTR ('ALERT',

                      V_FILE,

                      V_EXISTS,

                      V_SIZE,

                      V_BSIZE);

   DBMS_OUTPUT.PUT_LINE ('ALERT SIZE: ' || V_SIZE / 1024 / 1024 || ' MB');

   UTL_FILE.FSEEK (V_HANDLE, NULL, V_SIZE * (1 - V_PERCENT));

   DBMS_OUTPUT.PUT_LINE (

      'FETCHED ALERT SIZE: ' || V_SIZE * (V_PERCENT / 1024 / 1024) || ' MB');

   DBMS_OUTPUT.PUT_LINE (

      '********************************************************');

   LOOP

      BEGIN

         UTL_FILE.GET_LINE (V_HANDLE, V_LINE);

         DBMS_OUTPUT.PUT_LINE (V_LINE);

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

            EXIT;

      END;

   END LOOP;

   UTL_FILE.FCLOSE (V_HANDLE);

   EXECUTE IMMEDIATE 'DROP DIRECTORY ALERT';

END;

/

SPOOL OFF;

SELECT ORIGINATING_TIMESTAMP,

         MODULE_ID,

         PROCESS_ID,

         MESSAGE_TEXT

    FROM X$DBGALERTEXT

ORDER BY ORIGINATING_TIMESTAMP DESC;

SELECT d.datname as "Name",

pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

d.datcollate as "Collate",

d.datctype as "Ctype",

pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",

CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

            ELSE 'No Access'

END as "Size",

t.spcname as "Tablespace",

pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"

FROM pg_catalog.pg_database d

JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid

ORDER BY 1;

SELECT  

        A.SEGMENT_NAME

      , A.SEGMENT_TYPE

      , NVL(B.SUM_VALUE,0)

FROM    (

             SELECT SEGMENT_NAME,

             SEGMENT_TYPE

             FROM DBA_SEGMENTS

             WHERE OWNER = 'TEST'

             AND SEGMENT_TYPE ='TABLE'

        ) A

      , (

             SELECT 

                    OBJECT_NAME

                  , OBJECT_TYPE

                  , SUM(VALUE) sum_value

             FROM   V$SEGMENT_STATISTICS

             WHERE  OWNER = 'TEST'

             AND    OBJECT_TYPE='TABLE'

             AND    (STATISTIC_NAME LIKE '%read%' OR STATISTIC_NAME LIKE '%write%' )

             GROUP BY OBJECT_NAME, OBJECT_TYPE

        ) B

WHERE   A.SEGMENT_NAME = B.OBJECT_NAME(+)

AND     A.SEGMENT_TYPE = B.OBJECT_TYPE(+)

ORDER BY A.SEGMENT_TYPE , A.SEGMENT_NAME;

/* Formatted on 2015-02-17 오후 12:40:23 (QP5 v5.163.1008.3004) */

  SELECT DECODE (G1,

                 1, 'SUB AVG',

                 SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1))

            SNAP_TIME,

         SNAP_TIME SNAP_TIME_RANGE,

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE)),

                  'FM999,999,999,999,999.9')

            "DB time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DB CPU', VALUE)),

                  'FM999,999,999,999,999.9')

            "DB CPU/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'background cpu time', VALUE)),

                  'FM999,999,999,999,999.9')

            "Background CPU Time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'background elapsed time', VALUE)),

                  'FM999,999,999,999,999.9')

            "Background Elap. Time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'SQL execute elapsed time', VALUE)),

                  'FM999,999,999,999,999.9')

            "SQL Exec Elap. Time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE)),

                  'FM999,999,999,999,999.9')

            "Parse Time Elapsed/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'hard parse elapsed time', VALUE)),

                  'FM999,999,999,999,999.9')

            "Hard Parse Elap. Time/SEC",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'hard parse (bind mismatch) elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "H Parse B Miss Elap. Time/SEC",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'hard parse (sharing criteria) elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "H Parse S Crit Elap. Time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'failed parse elapsed time', VALUE)),

                  'FM999,999,999,999,999.9')

            "Failed Parse Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'repeated bind elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "Repeated Bind Elap. Time/SEC",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'failed parse (out of shared memory) elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "F Parse O Mem Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'PL/SQL execution elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "PL/SQL Exec Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'PL/SQL compilation elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "PL/SQL Compile Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'inbound PL/SQL rpc elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "Inbound PL/SQL Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'sequence load elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "Sequence Load Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'Java execution elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "Java Exec Elap. Time/SEC",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'RMAN cpu time (backup/restore)', VALUE)),

            'FM999,999,999,999,999.9')

            "RMAN CPU Time/SEC",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'connection management call elapsed time', VALUE)),

            'FM999,999,999,999,999.9')

            "Conn Mngt Call Elap. Time/SEC",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DB CPU', VALUE_DIFF)),

                  'FM999,999,999,999,999,999,999')

            "DB CPU DIFF",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE_DIFF)),

                  'FM999,999,999,999,999,999,999')

            "DB time DIFF",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'background cpu time', VALUE_DIFF)),

                  'FM999,999,999,999,999,999,999')

            "Background CPU Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'background elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Background Elap. Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'SQL execute elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "SQL Exec Elap. Time DIFF",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE_DIFF)),

                  'FM999,999,999,999,999,999,999')

            "Parse Time Elapsed DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'hard parse elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Hard Parse Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'hard parse (bind mismatch) elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "H Parse B Miss Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'hard parse (sharing criteria) elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "H Parse S Crit Elap. Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'failed parse elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Failed Parse Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'failed parse (out of shared memory) elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "F Parse O Mem Elap. Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'repeated bind elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Repeated Bind Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME, 'PL/SQL execution elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "PL/SQL Exec Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'PL/SQL compilation elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "PL/SQL Compile Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'inbound PL/SQL rpc elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Inbound PL/SQL Elap. Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'sequence load elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Sequence Load Elap. Time DIFF",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'Java execution elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Java Exec Elap. Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME, 'RMAN cpu time (backup/restore)', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "RMAN CPU Time DIFF",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'connection management call elapsed time', VALUE_DIFF)),

            'FM999,999,999,999,999,999,999')

            "Conn Mngt Call Elap. Time DIFF"

    FROM (  SELECT STAT_NAME,

                   START_TIME || '-' || END_TIME SNAP_TIME,

                   ROUND (AVG (NVL (VALUE, 0)), 1) VALUE,

                   ROUND (AVG (NVL (VALUE_DIFF, 0)), 1) VALUE_DIFF,

                   GROUPING (START_TIME || '-' || END_TIME) G1,

                   GROUPING (STAT_NAME) G2

              FROM (SELECT STAT_NAME,

                           TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME,

                           TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME,

                           DECODE (

                              SNAP_TIME_C2,

                              NULL, 0,

                              ROUND (

                                 (CASE

                                     WHEN VALUE_2 < VALUE_1 THEN 0

                                     ELSE VALUE_2 - VALUE_1

                                  END)

                                 / (EXTRACT (

                                       DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1)

                                    * 86400

                                    + EXTRACT (

                                         HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1)

                                      * 3600

                                    + EXTRACT (

                                         MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1)

                                      * 60

                                    + EXTRACT (

                                         SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)),

                                 1))

                              VALUE,

                           (CASE

                               WHEN VALUE_2 < VALUE_1 THEN 0

                               ELSE VALUE_2 - VALUE_1

                            END)

                              VALUE_DIFF,

                           ROW_NUMBER ()

                           OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

                                 ORDER BY SNAP_ID)

                              RNUM,

                           SNAP_ID,

                           INSTANCE_NUMBER

                      FROM (  SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1,

                                     LEAD (

                                        SNAP.END_INTERVAL_TIME)

                                     OVER (

                                        PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME

                                        ORDER BY SNAP.SNAP_ID)

                                        SNAP_TIME_C2,

                                     STAT.STAT_NAME,

                                     STAT.VALUE VALUE_1,

                                     LEAD (

                                        STAT.VALUE)

                                     OVER (

                                        PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME

                                        ORDER BY SNAP.SNAP_ID)

                                        VALUE_2,

                                     SNAP.SNAP_ID,

                                     DBI.INSTANCE_NUMBER

                                FROM (SELECT DI.DBID,

                                             DI.INSTANCE_NUMBER,

                                             DI.STARTUP_TIME

                                        FROM DBA_HIST_DATABASE_INSTANCE DI

                                       WHERE     DI.DBID = (select dbid from v$database) --변경 

                                             AND DI.INSTANCE_NUMBER = (select  INSTANCE_NUMBER from v$instance)  --변경

                                             AND ROWNUM <= 1) DBI,

                                     DBA_HIST_SNAPSHOT SNAP,

                                     DBA_HIST_SYS_TIME_MODEL STAT

                               WHERE     DBI.DBID = SNAP.DBID

                                     AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER

                                     AND SNAP.SNAP_ID >= :3

                                     AND SNAP.SNAP_ID <= :4

                                     AND SNAP.DBID = STAT.DBID

                                     AND SNAP.INSTANCE_NUMBER =

                                            STAT.INSTANCE_NUMBER

                                     AND SNAP.SNAP_ID = STAT.SNAP_ID

                            ORDER BY SNAP.SNAP_ID)

                     WHERE SNAP_TIME_C2 <> SNAP_TIME_C1)

             WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL

          GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME))

   WHERE NOT (G1 = 1 AND G2 = 1)

GROUP BY DECODE (G1,

                 1, 'SUB AVG',

                 SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1)),

         SNAP_TIME

ORDER BY SNAP_TIME

SELECT   sl.sid,

           sl.serial#,

           SYSDATE,

           TO_CHAR (sl.start_time, 'DD-MON-YYYY:HH24:MI:SS') start_time,

           ROUND ( (sl.elapsed_seconds / 60), 2) min_elapsed,

           ROUND ( (sl.time_remaining / 60), 2) min_remaining,

           sl.opname,

           sl.MESSAGE

    FROM   v$session_longops sl, v$session s

   WHERE   s.sid = sl.sid AND s.serial# = sl.serial# AND sl.opname like 'Sort%' 

ORDER BY   sl.start_time DESC, sl.time_remaining ASC;

SELECT   s.sid "SID",

         s.username "User",

         s.program "Program",

         u.tablespace "Tablespace",

         u.contents "Contents",

         u.extents "Extents",

         u.blocks * 8 / 1024 "Used Space in MB",

         q.sql_text "SQL TEXT",

         a.object "Object",

         k.bytes / 1024 / 1024 "Temp File Size"

  FROM   v$session s,

         v$sort_usage u,

         v$access a,

         dba_temp_files k,

         v$sql q

 WHERE       s.saddr = u.session_addr

         AND s.sql_address = q.address

         AND s.sid = a.sid

         AND u.tablespace = k.tablespace_name;

1. MySQL Session 조회

SELECT   a.*

FROM    INFORMATION_SCHEMA.PROCESSLIST A

WHERE   1            =    1

AND     A.COMMAND     NOT IN ('Binlog Dump','Sleep')

#AND        A.DB         IN    ('ssgaddb') # real

;

2. MySQL Metadata LOCK 조회

: 메타 정보 잠금 MySQL 5.5.3 이후 추가된 새로운 유형의 잠금으로 DDL문에 대한 잠금

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,

       THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO

FROM performance_schema.metadata_locks

    INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID

WHERE PROCESSLIST_ID <> CONNECTION_ID();

2. MySQL LOCK 조회

: 일반적인 잠금은 객체에 대한 읽기나 쓰기에 대한 잠금

select * from information_schema.INNODB_LOCK_WAITS;

--테이블은 아직 LOCK을 얻지 못하고 기다리고 있는 트렌젝션을 관리

--테이블이 Lock당한 프로세스를 조회

select * FROM information_schema.INNODB_LOCKS;

--Lock을 건 프로세스를 조회

select * FROM information_schema.INNODB_TRX;

--오랜 시간 동안 Commit 되지 않은 트렌젝션을 보려면 이 테이블을 보면 확인 가능

1. DB LEVEL 사이즈 조회

SELECT

COUNT(*) NUM_OF_TABLE

   , TABLE_SCHEMA,CONCAT(ROUND(SUM(TABLE_ROWS)/1000000,2),'M') ROWS

   , CONCAT(ROUND(SUM(DATA_LENGTH)/(1024*1024*1024),2),'G') DATA

   , CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024*1024),2),'G') IDX

   , CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/(1024*1024*1024),2),'G') TOTAL_SIZE

   , ROUND(SUM(INDEX_LENGTH)/SUM(DATA_LENGTH),2) IDXFRAC

FROM     INFORMATION_SCHEMA.TABLES

GROUP BY

TABLE_SCHEMA

ORDER BY

SUM(DATA_LENGTH+INDEX_LENGTH) DESC LIMIT 10;

2. TABLE LEVEL 사이즈 조회

SELECT TABLE_NAME,

       ROUND(DATA_LENGTH/(1024*1024),2) AS 'TABLE SIZE(MB)',

       ROUND(INDEX_LENGTH/(1024*1024),2) AS 'INDEX SIZE(MB)'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'TESTDB'

GROUP BY TABLE_NAME, DATA_LENGTH, INDEX_LENGTH;