행위

오라클 튜닝 테이블 실제 건수

DB CAFE

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
;