오라클이 인덱스 범위 검색을 사용하도록 강제하는 방법은 무엇입니까?

오라클이 인덱스 범위 검색을 사용하도록 강제하는 방법은 무엇입니까?

저는 14억 개의 레코드(인덱스 포함) 테이블에 대해 실행하는 매우 유사한 일련의 쿼리를 가지고 있는데, 유일한 문제는 해당 쿼리의 최소 10%가 다른 쿼리보다 100배 이상 많은 시간이 걸린다는 것입니다.

설명 계획을 실행했더니 빠른 쿼리(약 90%)를 확인했습니다.Oracle은 인덱스 범위 검색을 사용하고 있지만 느린 경우에는 전체 인덱스 검색을 사용합니다.

Oracle이 인덱스 범위 검사를 수행하도록 강제하는 방법이 있습니까?

Oracle이 인덱스 범위 검색을 사용하도록 "강제"하려면 옵티마이저 힌트를 사용합니다.INDEX_RS_ASC예:

CREATE TABLE mytable (a NUMBER NOT NULL, b NUMBER NOT NULL, c CHAR(10)) NOLOGGING;

INSERT /*+ APPEND */ INTO mytable(a,b,c) 
SELECT level, mod(level,100)+1, 'a'  FROM dual CONNECT BY level <= 1E6;

CREATE INDEX myindex_ba ON mytable(b, a);
EXECUTE dbms_stats.gather_table_stats(NULL,'mytable');

SELECT /*+ FULL(m)         */ b FROM mytable m WHERE b=10; -- full table scan
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan
SELECT /*+ INDEX_FFS(m)    */ b FROM mytable m WHERE b=10; -- index fast full scan

이렇게 하면 실제로 쿼리 실행 속도가 빨라지는지 여부는 인덱싱된 값의 선택성이나 테이블에 있는 행의 실제 순서와 같은 여러 요인에 따라 달라집니다.예를 들어 쿼리를 다음으로 변경하는 경우WHERE b BETWEEN 10 AND <xxx>내 컴퓨터의 실행 계획에 다음 비용이 표시됩니다.

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    752
INDEX_RS_ASC        29    325     865   1943
INDEX_FFS          597    598     599    601

인덱스된 열을 선택하는 것뿐만 아니라 쿼리를 매우 약간 변경하는 경우b그러나 다른 색인이 아닌 열도 비용이 크게 변동됩니다.

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    754
INDEX_RS_ASC      3352  40540  108215 243563
INDEX_FFS         3352  40540  108215 243563

저는 다음과 같은 접근법을 제안합니다.

• 느린 설명문에 대한 설명 계획 가져오기
• INDEX 힌트를 사용하여 인덱스 사용에 대한 설명 계획을 얻습니다.

INDEX 계획의 비용이 더 많이 든다는 것을 알게 될 것입니다.이것이 오라클이 인덱스 플랜을 선택하지 않는 이유입니다.비용은 Oracle이 보유한 통계와 다양한 가정을 바탕으로 추정한 것입니다.

계획의 예상 비용이 더 크지만 실제로 실행 속도가 더 빠르다면 예상이 잘못된 것입니다.당신의 일은 견적이 잘못된 이유를 파악하고 수정하는 것입니다.그러면 Oracle은 본 명세서 및 기타 명세서에 대한 올바른 계획을 자체적으로 선택하게 됩니다.

잘못된 이유를 파악하려면 계획에서 예상되는 행 수를 확인합니다.여러분은 아마 이들 중 하나가 몇 배나 큰 것이라는 것을 알게 될 것입니다.이는 불균일하게 분포된 열 값, 오래된 통계량, 서로 코레이트되는 열 등으로 인해 발생할 수 있습니다.

이 문제를 해결하기 위해 Oracle이 더 나은 통계를 수집하고 더 나은 시작 가정을 제시하도록 할 수 있습니다.그러면 정확한 비용을 추정하고 가장 빠른 계획을 세울 것입니다.

만약 당신이 더 많은 정보를 올린다면 저는 더 많은 의견을 말할 수 있을 것입니다.

최적화 도구가 결정을 내리는 이유를 알고 싶다면 10053 추적을 사용해야 합니다.

SQL> alter session set events '10053 trace name context forever, level 1';

그런 다음 샘플 빠른 쿼리와 샘플 느린 쿼리에 대한 설명 계획을 실행합니다.사용자 덤프 디렉토리에는 CBO가 통과하는 의사 결정 트리를 자세히 설명하는 추적 파일이 있습니다.이러한 파일에서 인덱스 범위 검색 대신 전체 인덱스 검색을 선택하는 이유를 찾을 수 있습니다.

추적 파일이 읽기 쉽다고 말하는 것은 아닙니다.그들을 이해하기 위한 최고의 자료는 볼프강 브라이틀링의 훌륭한 백서 "CBO의 후드 아래 보기"(PDF)입니다.

오라클 SQL 힌트를 사용할 수 있습니다.특정 인덱스 또는 제외 인덱스를 사용하도록 강제할 수 있습니다. 문서 확인

http://psoug.org/reference/hints.html http://www.adp-gmbh.ch/ora/sql/hints/index.html

like /*+ 인덱스(sys.sysix_sys) */ from scott.sysemp_sys를 선택합니다.

Oracle이 힌트를 무시하는 것을 보았습니다.

최근 DBA는 "optimizer_index_cost_adj"를 사용하여 인덱스를 활용했습니다.이 매개 변수는 Oracle 매개 변수이지만 세션 수준으로 사용할 수 있습니다.

100이 기본값이고 10을 매개변수로 사용했습니다.

언급URL : https://stackoverflow.com/questions/2473614/how-to-force-oracle-to-use-index-range-scan