오라클 튜닝 테이블 실제 건수
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 스칼라서브쿼리 멀티로우 처리[편집]
SELECT REGEXP_REPLACE(EXTRACT(dbms_xmlgen.getXmlType('select to_char(level) c from dual connect by level <= 10'), '/ROWSET/ROW/C'
).getStringVal(), '<C>|</C><C>|</C>', ', '
)
, dbms_xmlgen.getXmlType('select to_char(level)||'','' C from dual connect by level <= 10').EXTRACT('//text()').getclobval()
FROM DUAL
CONNECT BY LEVEL <=5
;2 테이블 건수 조회 예시[편집]
SELECT A.OWNER,A.TABLE_NAME
, REGEXP_REPLACE(EXTRACT(dbms_xmlgen.getXmlType('SELECT COUNT(*)||'' '' C FROM '||A.OWNER||'.'||A.TABLE_NAME), '/ROWSET/ROW/C'
).getStringVal(), '<C>|</C><C>|</C>', ''
) C1
--, dbms_xmlgen.getXmlType('SELECT COUNT(*)||'','' C FROM '||A.OWNER||'.'||A.TABLE_NAME).EXTRACT('//text()').getclobval() C2
FROM DBA_TABLES A
WHERE OWNER = 'CYKIM'
;3 통계정보 차이 분석(실행주의:테이블 건수 실측 수행함)[편집]
WITH V1 AS (
SELECT A.OWNER, A.TABLE_NAME
, A.LAST_ANALYZED
, A.NUM_ROWS ANALYZED_ROWS
--, NUMTODSINTERVAL(SYSDATE - A.LAST_ANALYZED, 'DAY') DAY_GAP
, TRUNC(SYSDATE - A.LAST_ANALYZED) GAP_DAY
FROM DBA_TABLES A
WHERE 1 = 1
AND A.LAST_ANALYZED < TRUNC(SYSDATE - 30)
AND OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE USER_ID > 100)
ORDER BY GAP_DAY DESC, 1, 2
)
, V2 AS (
SELECT ROWNUM AS RNO
, A.*
, TO_NUMBER(REGEXP_REPLACE(EXTRACT(DBMS_XMLGEN.GETXMLTYPE(
'SELECT /*+ PARALLEL(1) */ COUNT(*) C FROM '||A.OWNER||'.'||A.TABLE_NAME
), '/ROWSET/ROW/C'
).GETSTRINGVAL()
, '<C>|</C><C>|</C>', ''
)
) CURRENT_ROWS
FROM V1 A
-- WHERE ROWNUM < 100
)
SELECT A.*, ABS(A.ANALYZED_ROWS - A.CURRENT_ROWS) AS GAP_ROWS
, ROUND(ABS(A.ANALYZED_ROWS - A.CURRENT_ROWS) / GREATEST(A.ANALYZED_ROWS,1) * 100, 2) CHANGED_PCT
FROM V2 A
;