Oracle 11g 파티션 테이블에서 동시 통계 수집

Oracle 11g 파티션 테이블에서 동시 통계 수집

저는 오라클 11g에서 DWH를 개발하고 있습니다.값별로 구분된 큰 테이블(2억 5천만 개 이상의 행)이 몇 개 있습니다.각 파티션은 서로 다른 공급 소스에 할당되며, 모든 파티션은 다른 파티션과 독립적이므로 로드 및 처리가 동시에 가능합니다.

데이터 배포가 매우 고르지 않고 수백만 행의 파티션이 있고 100 행 이하의 파티션이 있습니다. 하지만 저는 파티션 구성 방식을 선택하지 않았고 변경할 수도 없습니다.

데이터 용량을 고려할 때, 모든 파티션에 항상 최신 통계가 있음을 보장해야 합니다. 그 이후의 세부 사항이 데이터에 최적으로 접근할 수 없다면 영원히 지속될 것이기 때문입니다.

따라서 동시 ETL 스레드마다 우리는

1. 파티션 잘라내기
2. 스테이징 영역에서 데이터 로드:

SELECT /*+ APPEND */ INTO big_table PARTITION(part1) FROM temp_table WHERE partition_colum = PART1

(이렇게 하면 직접적인 경로가 있고 테이블 전체를 잠그지 않습니다.)

3. 수정된 파티션에 대한 통계를 수집합니다.

첫 번째 에서는 는을 했습니다.APPROX_GLOBAL_AND_PARTITION.

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part1,
                              estimate_percent=>1,
                              granularity=>'APPROX_GLOBAL_AND_PARTITION',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

그러나 작은 파티션을 로드할 때 AUMPRUS_GLOBAL 부분이 지배적이었고(여전히 GLOBAL보다 훨씬 빠름) 작은 파티션의 경우 로드 10초, 통계 20분과 같은 단점이 있었습니다.

따라서 11g의 증분 통계 기능으로 전환할 것을 제안 받았습니다. 즉, 수정한 파티션을 지정하지 않고 모든 매개 변수를 자동으로 유지하며 Oracle이 마법을 수행하여 어떤 파티션이 터치되었는지 자동으로 파악할 수 있습니다.실제로 작동합니다. 작은 파티션의 속도가 정말 빨라졌습니다.기능을 켠 후 통화가

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              estimate_percent=>dbms_stats.auto_sample_size,
                              granularity=>'AUTO',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

파티션을 더 이상 통과하지 못하고 샘플 백분율을 지정하지 않습니다.

하지만, 우리는 단점을 가지고 있고, 이전의 단점보다 더 심각할 수도 있고, 이것은 우리가 가지고 있는 높은 수준의 병렬성과 관련이 있습니다.

두 개의 큰 파티션이 동시에 시작된다고 가정해 보겠습니다. 거의 동시에 로드 단계도 완료됩니다.

1. 첫 번째 스레드는 삽입 문을 종료하고 커밋하며 통계 수집을 시작합니다.통계 절차는 두 개의 파티션이 수정되었음을 알려줍니다(이것은 맞으며, 하나는 가득 찼으며 두 번째 파티션은 잘려 있으며 트랜잭션이 진행 중임). 두 파티션 모두에 대한 통계를 올바르게 업데이트합니다.

2. 결국 두 번째 파티션이 종료되고, 통계를 수집하면 이미 업데이트된 모든 파티션이 표시되고 아무것도 수행하지 않습니다(그 동안 두 번째 스레드가 데이터를 커밋했기 때문에 이는 올바르지 않습니다).

결과는 다음과 같습니다.

PARTITION NAME | LAST ANALYZED        | NUM ROWS | BLOCKS | SAMPLE SIZE
-----------------------------------------------------------------------
PART1          | 04-MAR-2015 15:40:42 | 805731   | 20314  | 805731
PART2          | 04-MAR-2015 15:41:48 | 0        | 16234  | (null)

결과적으로 최적의 계획이 아닌 경우가 종종 발생합니다(즉, 세션을 삭제하고, 통계를 수동으로 새로 고치고, 프로세스를 다시 수동으로 시작하는 것을 의미합니다.

한 번에 한 개 이상의 스레드가 같은 테이블에서 통계를 수집할 수 없도록 모임 전용 잠금 장치까지 시도했지만 아무것도 바뀌지 않았습니다.

IMHO는 통계 절차가 두 번째로 호출될 때 두 번째 파티션에서 마지막 커밋을 확인해야 하며 마지막 통계 수집 시간보다 최신 상태임을 확인해야 하기 때문에 이상한 동작입니다.하지만 그런 일은 없는 것 같습니다.

내가 뭘 잘못하고 있나요?오라클 버그인가요?증분 통계 기능이 켜져 있고 동시성이 높은 상태에서 모든 통계가 항상 최신 상태임을 보장하려면 어떻게 해야 합니까?

저는 이 기능으로 좋은 타협점을 찾을 수 있었습니다.

PROCEDURE gather_tb_partiz(
    p_tblname IN VARCHAR2,
    p_partname IN VARCHAR2)
IS
  v_stale all_tab_statistics.stale_stats%TYPE;
BEGIN
  BEGIN
    SELECT stale_stats
    INTO v_stale
    FROM user_tab_statistics
    WHERE table_name = p_tblname
    AND object_type = 'TABLE';
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_stale := 'YES';
  END;
  IF v_stale = 'YES' THEN
    dbms_stats.gather_table_stats(ownname=>myschema, 
                                  tabname=> p_tblname,
                                  partname=>p_partname,
                                  degree=>dbms_stats.auto_degree,
                                  granularity=>'APPROX_GLOBAL AND PARTITION') ;
  ELSE
    dbms_stats.gather_table_stats(ownname=>myschema,
                                 tabname=>p_tblname,
                                 partname=>p_partname,
                                 degree=>dbms_stats.auto_degree,
                                 granularity=>'PARTITION') ;
  END IF;
END gather_tb_partiz;

각 ETL이 끝날 때 추가/삭제/수정된 행 수가 테이블을 오래된 것으로 표시하지 않을 정도로 낮으면(기본적으로 10%, STALE_PERCENT 매개변수로 조정 가능) 파티션 통계만 수집하고 그렇지 않으면 전역 및 파티션 통계를 수집합니다.

이를 통해 글로벌 파티션을 다시 수집할 필요가 없으므로 작은 파티션의 ETL을 빠르게 유지하고 큰 파티션을 안전하게 유지할 수 있습니다. 이후 쿼리에는 새 통계가 표시되고 최적의 계획이 사용될 가능성이 높기 때문입니다.

증분 통계는 어쨌든 활성화되므로 글로벌을 다시 계산해야 할 때마다 파티션 수준 통계를 집계하고 전체 검색을 수행하지 않으므로 상당히 빠릅니다.

증분이 활성화된 경우 "APROX_GLOBAL AND PARTITION"과 "GLOBAL AND PARTITION"은 기본적으로 전체 스캔을 수행하지 않고 집계 통계와 히스토그램이라는 동일한 작업을 수행하기 때문에 차이가 있는지 잘 모르겠습니다.

증분 통계량을 사용하려고 했지만 분석할 파티션의 이름을 명시적으로 지정한 적이 있습니까?

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part,
                              degree=>dbms_stats.auto_degree);

테이블의 경우 오래된(어제의) 글로벌 통계는 완전히 잘못된 파티션 통계(0 행)만큼 해롭지 않습니다.저는 우리가 사용하는 2가지 약간의 대안적인 접근법을 제안할 수 있습니다.

• 모든 파티션이 로드된 직후 ETL 도구로 별도의 GLOBAL 통계 수집을 실행합니다.시간이 너무 오래 걸리는 경우 dbms_stats로 estimate_percent를 사용하여 재생합니다.auto_degree는 1% 이상일 가능성이 높습니다.
• 모든 데이터를 DW에 로드한 후 낮 시간에 별도의 데이터베이스 작업 실행에서 글로벌(다른 모든 오래된) 통계를 수집합니다.

핵심은 신선도와 약간 차이가 있을 뿐인 진부한 통계가 거의 없다는 것입니다.통계에 0개의 행이 표시되면 모든 쿼리가 삭제됩니다.

달성하려는 작업을 고려할 때, 각 파티션을 로드하는 프로세스가 끝날 때가 아니라 모든 파티션에 대해 특정 시간 간격으로 통계를 실행해야 합니다.이 테이블이 라이브 테이블이고 24시간 데이터 로드가 일정하다면 어려울 수 있지만, 이 테이블들은 LARG DW 테이블이기 때문에 실제로 그런 것인지는 의문입니다.따라서 모든 파티션 로드가 끝날 때 통계를 수집하는 것이 가장 좋습니다. 이렇게 하면 데이터가 변경되거나 통계가 누락된 파티션에 대한 통계가 수집되고 파티션 수준 통계 및 시놉시스를 기반으로 글로벌 통계를 업데이트할 수 있습니다.

그러나 이렇게 하려면 테이블(11gR1)에 대해 증분 기능을 설정해야 합니다.

EXEC DBMS_STATS.SET_TABLE_PREFS('<Owner>','BIG_TABLE','INCREMENTAL','TRUE');

부하가 마다 Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ ΔΔ Δ Δ Δ GATHER_TABLE_STATS 가 없습니다파티션 이름을 지정할 필요가 없습니다.또한 세분화 매개변수를 지정하지 마십시오.

EXEC DBMS_STATS.GATHER_TABLE_STATS('<Owner>','BIG_TABLE');

DBMS_STATS를 사용하여 테이블 선호도를 설정하여 증분 통계를 수집했는지 확인해주세요.이 오라클 블로그에서는 각 행이 영향을 받은 후 통계가 수집된다고 설명합니다.

증분 통계 유지 관리는 전역 또는 테이블 수준 통계를 변경할 파티션에 대한 통계를 수집해야 합니다.예를 들어, 테이블에서 행을 하나만 삽입하거나 업데이트한 후 열에 대한 최소 최대값이 변경될 수 있습니다.

BEGIN 
DBMS_STATS.SET_TABLE_PREFS(myschema,'BIG_TABLE','INCREMENTAL','TRUE'); 
END;

저는 좀 녹슬지 않아서, 우선 질문이 있습니다: 파티션 로드를 직렬화해보셨나요?그렇다면 통계는 얼마나 오래 그리고 얼마나 잘 운영됩니까?통계 수집에 비해 로딩 시간이 매우 짧기 때문에 이는 일시적인 해결책으로도 작용할 수 있습니다.

추가 힌트는 redo 크기에 영향을 미치는데, 이는 트랜잭션이 무언가를 추적할 뿐이므로 통계가 새로운 데이터를 계산하지 않을 수 있습니다. http://oracle-base.com/articles/misc/append-hint.php

직접 경로 삽입은 파티션 끝에 행을 추가하고 마지막에 메타데이터를 업데이트하므로 이미 실행 중인 스레드 수집 통계가 업데이트되지 않은 데이터를 읽을 수 있습니다.따라서 버그가 아닐 수 있으며 스레드를 잠그는 것은 아무 것도 달성하지 못합니다.

예를 들어 테이블/파티션을 LOGGGING(로깅)으로 일시적으로 전환하여 이 동작을 테스트할 수 있으며, 동작 방식을 확인할 수 있습니다(물론 속도가 느리지만 테스트입니다).할 수 있겠어요?

편집: 증분 통계는 어떻게 수집되었든 간에 증분 값에 의존하므로 병렬 통계 수집을 비활성화하더라도 어쨌든 작동해야 합니다. https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics

언급URL : https://stackoverflow.com/questions/28858892/concurrent-statistics-gathering-on-oracle-11g-partiitioned-table