주식회사 누리아이티

정보자산의 보안강화를 위한 다계층 인증SW (BaroPAM) 전문기업.

▶ Tuxedo/오류 메시지(Oracle)

Oracle Data Dictionary

누리아이티 2017. 12. 21. 08:43

 

Data Dictionary Database의 가장 중요한 부분의 하나로 Data Dictionary에는 연관된 Database 정보를 제공하는 읽기전용 Table의 집합입니다.

또한, Data Dictionary는 온라인 데이터베이스 시스템의 운용 환경 정보를 집중 관리하기 위한 정보 저장 파일. 데이터베이스에 포함된 모든 데이터들의 객체들에 대한 정의와 사양에 관한 정보를 유지 관리하는 데이터 사전과 데이터베이스에 수록된 데이터에 실제로 접근하는데 필요한 정보를 관리하는 데이터 디렉터리로 구성되어 있습니다. 다음은 Data Dictionaray를 확인 함으로써 현재의 Database의 상태를 점검할 수 있습니다.

 

1) Version 확인

   SQL> SELECT * FROM  V$VERSION;




2) Parameter 확인

SQL> SELECT NAME, VALUE FROM  V$PARAMETER ORDER  BY NAME;



3) Data file 확인

SQL> SELECT FILE#, NAME FILE_NAME, ENABLED, CREATE_BYTES FROM V$DATAFILE ORDER BY FILE#;



4) Log file 확인

SQL> SELECT A.GROUP#, B.MEMBER, A.BYTES, A.ARCHIVED, A.STATUS FROM  V$LOG A, V$LOGFILE B

WHERE  A.GROUP# = B.GROUP#  ORDER  BY A.GROUP#;


5) Control file 확인

   SQL> SELECT NAME, STATUS FROM  V$CONTROLFILE;


6) S.G.A 확인

SQL> SELECT * FROM V$SGASTAT WHERE NAME IN ('free memory', 'db_block_buffers', 'log_buffer',

 'dictionary cache', 'sql area', 'library cache');


7) Library cache Hit Ratio 확인

SQL> SELECT SUM(RELOADS) / SUM(PINS) * 100   MISS, (1 - SUM(RELOADS) / SUM(PINS)) * 100   

HIT  FROM  V$LIBRARYCACHE ;

참고) Library cache MISS Ratio 1~2% 이상이면 shared_pool_size를 증가 시켜야 함.




8) Dictionary cache Miss/Hit Ratio Check

SQL> SELECT SUM(GETMISSES) / SUM(GETS) * 100    MISS, (1 -  SUM(GETMISSES) / SUM(GETS)) * 100   

HIT  FROM   V$ROWCACHE ;



참고) Dictionary cache MISS Ratio 10% 이상이면 shared_pool_size를 증가 시켜야 함.



9) Redo buffer 상태

    SQL> SELECT

                 B.NAME                                                    NAME

                ,NVL(MISSES           / DECODE(GETS          , 0, 1), 0)   MGR

                ,NVL(IMMEDIATE_MISSES / DECODE(IMMEDIATE_GETS, 0, 1), 0)   IMG

             FROM

                 V$LATCH      A

                ,V$LATCHNAME  B

             WHERE

                 B.NAME  IN  ('redo allocation', 'redo copy')

             AND B.LATCH# = A.LATCH#

             ORDER  BY

                 B.NAME  DESC;




참고) Ration 1% 이상이면 online Transaction이 매우 많이 발생하고 있슴.


10) DATABASE  BUFFER  상태

    SQL> SELECT

                 1 n, NAME, VALUE

             FROM

                 V$SYSSTAT

             WHERE

                 NAME LIKE 'change%'

             UNION  ALL

             SELECT  2 n, NAME, VALUE

                 FROM

                     V$SYSSTAT

                 WHERE

                     NAME LIKE 'consistent%'

             UNION  ALL

             SELECT

                    3 n, NAME, VALUE

                 FROM

                     V$SYSSTAT

                 WHERE

                     NAME LIKE 'db block%'

             UNION  ALL

             SELECT

                     4 n, NAME, VALUE

                 FROM

                     V$SYSSTAT

                 WHERE

                     NAME LIKE 'physical%'

             UNION  ALL

             SELECT

                     6 n, EVENT NAME, time_waited  VALUE

                 FROM

                     V$SYSTEM_EVENT

                 WHERE

                     EVENT = 'buffer busy waits'

             UNION  ALL

             SELECT  5 n,'free buffer waits' NAME,

                     ROUND(SUM(DECODE(EVENT,'free buffer waits', TIME_WAITED, 0)) /

DECODE(SUM(TIME_WAITED), 0, 1, SUM(TIME_WAITED)) * 100, 2)    VALUE

                 FROM

                     V$SYSTEM_EVENT

                 WHERE

                     EVENT NOT IN ('client message','pmon timer','smon timer','rdbms ipc

message')

                 ORDER BY 1,2 ;





11) SORT  PERFORMANCE

    SQL> SELECT

                 M.VALUE   "Memory Sorts"

                ,D.VALUE   "Disk Sorts"

                ,R.VALUE   "Rows Sorted"

                ,ROUND(D.VALUE / DECODE((M.VALUE + D.VALUE), 0, 1, (M.VALUE + D.VALUE)) * 10)   


"Disk Sorts %"

             FROM

                 V$SYSSTAT    M

                ,V$SYSSTAT    D

                ,V$SYSSTAT    R

             WHERE

                 M.NAME = 'sorts (memory)'

             AND D.NAME = 'sorts (disk)'

             AND R.NAME = 'sorts (rows)' ;


12) TABLESPACE  SIZE

    SQL> SELECT

                 TABLESPACE_NAME

                ,FILE_NAME

                ,BYTES / 1024 / 1024  SIZES

                ,STATUS

             FROM

                 DBA_DATA_FILES

             ORDER BY TABLESPACE_NAME;


SQL> SELECT 

                 T.TN                                   tn

                ,T.SIZES                                Tot

                ,(T.SIZES - F.SIZES)                    Used

                ,(T.SIZES - F.SIZES) / T.SIZES * 100    Pct

                ,F.SIZES                                Free

             FROM

                 (SELECT

                          TABLESPACE_NAME            TN

                         ,SUM(BYTES) / 1024 / 1024   SIZES

                      FROM

                          DBA_DATA_FILES

                      GROUP BY

                          TABLESPACE_NAME

                 )    T

                ,(SELECT

                          TABLESPACE_NAME            TN

                         ,SUM(BYTES) / 1024 / 1024   SIZES

                      FROM

                          DBA_FREE_SPACE

                      GROUP BY

                          TABLESPACE_NAME

                 )    F

             WHERE

                 T.TN = F.TN

             ORDER  BY  T.TN ;





SQL> SELECT 

                 TABLESPACE_NAME

                ,INITIAL_EXTENT / 1024    init

                ,NEXT_EXTENT    / 1024    next

                ,PCT_INCREASE

             FROM

                 DBA_TABLESPACES

             ORDER BY TABLESPACE_NAME;




13) DISK-READ 확인(Shared Pool)

    SQL> SELECT 

                 SUBSTR(SQL_TEXT,1,300) text

                ,DISK_READS,  EXECUTIONS

                ,DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)        "Average"

                ,DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) / 50   "Response"

             FROM

                 V$SQLAREA

             WHERE

                 DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) > 200

             AND SQL_TEXT NOT LIKE 'SELECT%'

             AND ROWNUM < 10

             ORDER BY DISK_READS  DESC ;



14) OBJECT COUNT

    SQL> SELECT  USERNAME

                ,COUNT(DECODE(O.TYPE#,  2, O.OBJ#, '')) Tab

,COUNT(DECODE(O.TYPE#,  1, O.OBJ#, '')) Ind

                ,COUNT(DECODE(O.TYPE#,  5, O.OBJ#, '')) Syn

,COUNT(DECODE(O.TYPE#,  4, O.OBJ#, '')) Vew

                ,COUNT(DECODE(O.TYPE#,  6, O.OBJ#, '')) Seq

,COUNT(DECODE(O.TYPE#,  7, O.OBJ#, '')) Prc

                ,COUNT(DECODE(O.TYPE#,  8, O.OBJ#, '')) Fun

,COUNT(DECODE(O.TYPE#,  9, O.OBJ#, '')) Pck

                ,COUNT(DECODE(O.TYPE#, 12, O.OBJ#, '')) Trg

,COUNT(DECODE(O.TYPE#, 10, O.OBJ#, '')) Dep

             FROM

                 SYS.OBJ$  O, SYS.DBA_USERS  U

             WHERE

                 U.USER_ID = O.OWNER# (+)

             GROUP BY USERNAME ;




15) TABLESPACE AND TYPE

    SQL> SELECT  OWNER, TABLESPACE_NAME, SEGMENT_TYPE, 

                 SUM(BYTES) / 1024  bytes

             FROM

                 DBA_EXTENTS

             WHERE

                 OWNER IN ('WHOLE', 'RIMS')

             GROUP BY  OWNER, TABLESPACE_NAME , SEGMENT_TYPE

ORDER BY OWNER, TABLESPACE_NAME ;





16) 5개 이상 Extents가 발생한 Object

    SQL> SELECT  OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME,

                 BYTES / 1024   sizes, EXTENTS

             FROM

                 DBA_SEGMENTS

             WHERE

                 SEGMENT_TYPE IN ('TABLE', 'INDEX')

             AND EXTENTS      >  5

             AND OWNER    IN ('WHOLE', 'RIMS')

             ORDER BY OWNER;



참고) Extents가 과도하게 발생한 Object Tablespace에 대한 설정 점검이 필요함.


 

17) DATAFILES DISK I/O

    SQL> SELECT  NAME, PHYRDS / 100  PR, PHYRDS * 100 / TRW.PHYS_READS   r_pct,

                 PHYWRTS / 100   PW, PHYWRTS * 100 / TRW.PHYS_WRTS       w_pct

             FROM

                 (SELECT  SUM(PHYRDS)  PHYS_READS , SUM(PHYWRTS)  PHYS_WRTS

                      FROM  V$FILESTAT

                 )             TRW

                ,V$DATAFILE    DF

                ,V$FILESTAT    FS

             WHERE

                 DF.FILE# = FS.FILE#

             ORDER BY NAME;





18) INDEX가 없는 TABLE

    SQL> SELECT  OWNER, TABLE_NAME

             FROM

                 ALL_TABLES

             WHERE

                 OWNER IN ('WHOLE', 'RIMS')

         MINUS

         SELECT  OWNER, TABLE_NAME

             FROM

                 ALL_INDEXES

             WHERE

                 OWNER IN ('WHOLE', 'RIMS');