오라클 ASM 사용률 변화 확인/수집 및 현황 보고를 위한 쿼리

매월 정기적으로 해야하는 작업이 있다.
바로 공간 사용률 분석이다.
매월 수집하는 이 정보가 누적되면
월,분기,년 단위로 DB 공간 사용률 확인 및 앞으로 필요한 공간의 예측이 가능할 것이다.

SELECT
    GROUP_NAME,
    TOTAL AS TOTAL_GB,
    USED AS USED_GB,
    FREE AS FREE_GB,
    USE_PCT AS USE_PCT_PER,
    DISK_SIZE AS DISK_SIZE_GB,
    DISK_COUNT
FROM (
    SELECT
        DECODE(SUBSTR(NAME, 5, 3), 'DAT', '1', 'REC', '2', 'ACF', '3', 'OCR', '4', NULL) AS NUM,
        TO_CHAR(NAME) AS GROUP_NAME,
        TO_CHAR(ROUND(TOTAL_MB / TYPE_N / 1024)) AS TOTAL,
        TO_CHAR(ROUND((HOT_USED_MB + COLD_USED_MB) / TYPE_N / 1024)) AS USED,
        TO_CHAR(ROUND(FREE_MB / TYPE_N / 1024)) AS FREE,
        TO_CHAR(ROUND((HOT_USED_MB + COLD_USED_MB) / TOTAL_MB * 100)) AS USE_PCT,
        X.OS_MB AS DISK_SIZE,
        DECODE(SUBSTR(NAME, 5, 4), 'DATA', (SELECT TO_CHAR(COUNT(*)) FROM V$ASM_DISK WHERE MOUNT_STATUS = 'CLOSED' AND HEADER_STATUS = 'CANDIDATE'), '0') AS DISK_COUNT
    FROM (
        SELECT
            CASE
                WHEN TYPE = 'NORMAL' THEN 2
                WHEN TYPE = 'HIGH' THEN 3
                ELSE 1
            END AS TYPE_N,
            A.*
        FROM V$ASM_DISKGROUP A
    ) G
       (
        SELECT
            DECODE(SUBSTR(A.DG_NAME, 5, 3), 'DAT', '5', 'REC', '6', 'ACF', '7', 'OCR', '8', NULL) AS NUM,
            A.*
        FROM (
            SELECT
                'DISK_SIZE' AS DISK_SIZE,
                TO_CHAR(SUBSTR(NAME, 1, INSTR(NAME, '_', -1) - 1)) AS DG_NAME,
                TO_CHAR(ROUND(OS_MB / 1024)) AS OS_MB
            FROM V$ASM_DISK
            GROUP BY SUBSTR(NAME, 1, INSTR(NAME, '_', -1) - 1), OS_MB
        ) A
        WHERE A.DG_NAME IS NOT NULL
    ) X 
    WHERE G.NAME = X.DG_NAME
) A
ORDER BY NUM;

쿼리를 직접 타이핑하다보니 오타가 있을 수 있다.
그럴 경우 쿼리를 쪼개서 수행시켜 보고 오타난 부분을 수정해서 사용하기 바란다.

위 쿼리의 결과는 아래와 같다

GROUP_NAME	TOTAL_GB	USED_GB	FREE_GB	USE_PCT_PER	DISK_SIZE_GB	DISK_COUNT
XXX_DATA	28599	272520	13078	95	500	31
XXX_RECO	5600	494	5105	9	500	0
XXX_OCR	15	1	14	3	10	0

 

 

각 컬럼의 의미

• GROUP_NAME : ASM DISK GROUP 이름.
• TOTAL_GB : 할당된 전체 공간. GB단위
• USED_GB : 사용중인 공간. GB단위
• FREE_GB : 여유 공간. GB단위
• USE_PCT_PER : 사용중인 공간. % 단위
• DISK_SIZE_GB : ASM DISK별 사이즈. GB단위
• DISK_COUNT : 여분 DISK 갯수.

DECODE를 사용한 이유는
GROUP NAME의 오름차순,내림차순 정렬이 아닌
중요도 순으로 정렬하기 위해
ASM DISK GROUP을 중요도순으로 번호를 부여하고 이 번호를 정렬했다.
회사마다 ASM이름이 다르겠으나
일반적으로 DATA,RECO,OCR 과 같은 형식으로 분류하여 사용할 것이다.
본인 환경에 맞게 수정하여 사용하면 된다.