통계정보 확인 / 복원 / 수집 / 변경 이력 확인

 Snapshot too old 가 날만큼 오래 걸리는 쿼리
 평소 안보이던건데.... dba_hist_sqlstat 에 과거 이력도 없다.
 최근 추가된건가 ? 데이타가 좀 이상하데.. 힌트도 좀 이상하고..
 힌트로 제어 시작 ... 요상하네... 의도한대로 잘 되지 않는다..
 음 결국 원인은 최근 추가된 테이블에 주말 통계정보 수집으로 row=0 인 

상태에서 월요일 100만건 가까이 데이타가 유입되고
 row=0 이 원인인지, non-equal join 이란 그런지,

그것도 아님 내가 부족한건지, 내가 의도한대로 힌트가 먹지 않는다.
 통계정보 수집으로 간단히 해결되었음

 

2022/05/13 오늘 동일 문제로 삽질

cardinality 힌트도 써보고 별거 별거 다 해봤는데, 내 맘같지 않군 ㅎㅎ

역시나 통계정보 수집해서 dba_tables.num_rows= 0 에 벗어나자 마자 Good Plan 으로 안착

 

-- 통계정보 확인 및 데이타 건수 확인해보기 
SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, T.LAST_ANALYZED, T.SAMPLE_SIZE, T.NUM_ROWS, T.IOT_TYPE
     , to_number(extractvalue(xmltype(DBMS_XMLGEN.getxml('select count(*) c from '||B.OWNER||'.'||B.table_name)),'/ROWSET/ROW/C')) Actual_count   
FROM   DBA_TABLES T
WHERE  T.OWNER ='SCOTT'
AND    T.NUM_ROWS = 0 
AND    T.TABLE_NAME ='EMP'
AND    T.LAST_ANALYZED > SYSDATE - 4
AND    T.PARTITIONED = 'NO'
AND    T.TEMPORARY  ='N' ;


-- 통계정보 이력 확인 및 복원 하기 
SELECT OWNER, TABLE_NAME, PARTITION_NAME, STATS_UPDATE_TIME
          , 'EXEC DBMS_STATS.RESTORE_TABLE_STATS('''||OWNER||''','''||TABLE_NAME||''','''||STATS_UPDATE_TIME||''',TRUE,TRUE,TRUE);' AS RESTORE_TABLE_STATS
FROM   DBA_TAB_STATS_HISTORY
WHERE  OWNER      ='OPUSADM'
AND    TABLE_NAME ='EMP'
ORDER BY STATS_UPDATE_TIME DESC ;


-- 테이블 통계정보 변경 내역 상세 확인  
-- ANALYZETIME >> 통계정보 수집 시간
-- SAVTIME     >> 신규 통계정보 수집으로 복구를 위한 SAVTIME 시간 , DBA_TAB_STATS_HISTORY
SELECT ob.owner, ob.object_name, ob.object_type, rowcnt, avgrln ,samplesize, analyzetime, A.*
FROM sys.WRI$_OPTSTAT_TAB_HISTORY A, dba_objects ob
WHERE owner=upper('SCOTT')
and object_name=upper('EMP')
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;

-- 통계정보 수동 수집 
EXEC  SYS.DBMS_STATS.GATHER_TABLE_STATS
    (
       OWNNAME      => 'SCOTT ',
       TABNAME      => 'EMP',
       ESTIMATE_PERCENT   => DBMS_STATS.AUTO_SAMPLE_SIZE,     
       GRANULARITY => 'AUTO',
       DEGREE => null,
       METHOD_OPT      => 'FOR ALL COLUMNS SIZE AUTO',
       CASCADE      => TRUE
    ) ;