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를 증가 시켜야 함.
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');
'▶ Tuxedo > 오류 메시지(Oracle)' 카테고리의 다른 글
| Oracle 성능애 영향을 미치는 것들-ROLLBACK_SEGMENTS (0) | 2017.12.21 |
|---|---|
| Oracle 성능애 영향을 미치는 것들-DBWR_IO_SLAVES (0) | 2017.12.21 |
| Oracle 성능애 영향을 미치는 것들-SORT_AREA_RETAINED_SIZE (0) | 2017.12.21 |
| Oracle 성능애 영향을 미치는 것들-SORT_AREA_SIZE (0) | 2017.12.21 |
| Oracle 성능애 영향을 미치는 것들-SHARED_POOL_RESERVE_SIZE (0) | 2017.12.21 |