테이블사이즈와 데이터사이즈를 확인해보고자 아래처럼 쿼리를 작성했습니다.
우선, 아래쿼리로 확인하면 되는게 맞는지 확인좀 부탁드리구요.
두번째는 테이블사이즈보다 데이터사이즈가 크게 보이는게 몇개 있습니다.
이게 분석시간차이에 따른 오차로 이렇게 보이는게 맞나요? (분석된 후 DATA가 변경되서..)
SELECT GUBUN "구분"
, ROW_NUMBER() OVER(ORDER BY GUBUN DESC, SIZE2 DESC) "NO"
, SEG_NAME "세그먼트명"
, NUM_ROWS "데이터건수(근사치)"
, SIZE2 "SEGMENT크기(byte)"
, SIZE1 "DATA크기(byte)"
, CASE WHEN SIZE2 <= 1024 THEN SUBSTR(' '||SIZE2||' BYTE',-10)
WHEN SIZE2 <= 1024000 AND SIZE2 > 1024 THEN SUBSTR(' '||ROUND(SIZE2/1024,1)||' KB',-10)
WHEN SIZE2 <= 1024000000 AND SIZE2 > 1024000 THEN SUBSTR(' '||ROUND(SIZE2/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND(SIZE2/1024/1024/1024,1)||' GB',-10) END "SEGMENT크기"
, CASE WHEN SIZE1 <= 1024 THEN SUBSTR(' '||SIZE1||' BYTE',-10)
WHEN SIZE1 <= 1024000 AND SIZE1 > 1024 THEN SUBSTR(' '||ROUND(SIZE1/1024,1)||' KB',-10)
WHEN SIZE1 <= 1024000000 AND SIZE1 > 1024000 THEN SUBSTR(' '||ROUND(SIZE1/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND(SIZE1/1024/1024/1024,1)||' GB',-10) END "Data크기(근사치)"
, CASE WHEN INIT_SIZE <= 1024 THEN SUBSTR(' '||INIT_SIZE||' BYTE',-10)
WHEN INIT_SIZE <= 1024000 AND INIT_SIZE > 1024 THEN SUBSTR(' '||ROUND(INIT_SIZE/1024,1)||' KB',-10)
WHEN INIT_SIZE <= 1024000000 AND INIT_SIZE > 1024000 THEN SUBSTR(' '||ROUND(INIT_SIZE/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND(INIT_SIZE/1024/1024/1024,1)||' GB',-10) END "초기크기"
, CASE WHEN NEXT_SIZE <= 1024 THEN SUBSTR(' '||NEXT_SIZE||' BYTE',-10)
WHEN NEXT_SIZE <= 1024000 AND NEXT_SIZE > 1024 THEN SUBSTR(' '||ROUND(NEXT_SIZE/1024,1)||' KB',-10)
WHEN NEXT_SIZE <= 1024000000 AND NEXT_SIZE > 1024000 THEN SUBSTR(' '||ROUND(NEXT_SIZE/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND(NEXT_SIZE/1024/1024/1024,1)||' GB',-10) END "확장크기"
, CASE WHEN MAX_SIZE <= 1024 THEN SUBSTR(' '||MAX_SIZE||' BYTE',-10)
WHEN MAX_SIZE <= 1024000 AND MAX_SIZE > 1024 THEN SUBSTR(' '||ROUND(MAX_SIZE/1024,1)||' KB',-10)
WHEN MAX_SIZE <= 1024000000 AND MAX_SIZE > 1024000 THEN SUBSTR(' '||ROUND(MAX_SIZE/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND(MAX_SIZE/1024/1024/1024,1)||' GB',-10) END "제한크기"
, SIZE2 - SIZE1 "낭비공간(byte)"
, CASE WHEN (SIZE2 - SIZE1) <= 1024 THEN SUBSTR(' '||(SIZE2 - SIZE1)||' BYTE',-10)
WHEN (SIZE2 - SIZE1) <= 1024000 AND (SIZE2 - SIZE1) > 1024 THEN SUBSTR(' '||ROUND((SIZE2 - SIZE1)/1024,1)||' KB',-10)
WHEN (SIZE2 - SIZE1) <= 1024000000 AND (SIZE2 - SIZE1) > 1024000 THEN SUBSTR(' '||ROUND((SIZE2 - SIZE1)/1024/1024,1)||' MB',-10)
ELSE SUBSTR(' '||ROUND((SIZE2 - SIZE1)/1024/1024/1024,1)||' GB',-10) END "낭비공간"
, LAST_ANALYZED "분석된시간"
FROM (
SELECT B.SEGMENT_TYPE GUBUN
, A.TABLE_NAME SEG_NAME
, A.NUM_ROWS
, DECODE(A.AVG_ROW_LEN,0,0,GREATEST(4,CEIL(A.NUM_ROWS/((ROUND(((1958-(A.INI_TRANS*23))*((100-A.PCT_FREE)/100))/A.AVG_ROW_LEN))))*2048)) SIZE1 --데이터사이즈
, B.BYTES SIZE2 -- TABLE사이즈
, A.INITIAL_EXTENT INIT_SIZE
, A.NEXT_EXTENT NEXT_SIZE
, A.MAX_EXTENTS MAX_SIZE
, A.LAST_ANALYZED
FROM USER_TABLES A
, USER_SEGMENTS B
WHERE 1 = 1
AND A.TABLE_NAME = B.SEGMENT_NAME
AND B.SEGMENT_TYPE = 'TABLE'
UNION ALL
SELECT B.SEGMENT_TYPE GUBUN
, A.INDEX_NAME SEG_NAME
, A.NUM_ROWS
, NULL SIZE1 -- 데이터사이즈
, B.BYTES SIZE2 -- INDEX사이즈
, A.INITIAL_EXTENT INIT_SIZE
, A.NEXT_EXTENT NEXT_SIZE
, A.MAX_EXTENTS MAX_SIZE
, A.LAST_ANALYZED
FROM USER_INDEXES A
, USER_SEGMENTS B
WHERE A.INDEX_NAME = B.SEGMENT_NAME
AND B.SEGMENT_TYPE = 'INDEX'
) ORDER BY 1 DESC, 5 DESC
; |