Oracle SQL Developer 실행 설명 계획 결과 이해
쿼리를 최적화하려고 하는데 설명 계획에서 반환된 일부 정보를 잘 이해하지 못합니다.OPTIONS 및 COST 컬럼의 중요성에 대해 말씀해 주실 수 있습니까?OPTIONS 열에는 FULL이라는 단어만 표시되며 COST 열에서는 비용이 낮을수록 빠른 쿼리를 의미한다고 추론할 수 있습니다.그러나 비용 값은 정확히 무엇을 나타내며 허용되는 임계값은 무엇입니까?
EXPLY PLAN의 출력은 Oracle 쿼리 옵티마이저의 디버깅 출력입니다.COST는 CBO(Cost-based optimiser)의 최종 출력이며, 그 목적은 쿼리 실행에 사용할 수 있는 많은 다른 계획 중 어떤 것을 선택하는 것이다.CBO는 각 계획에 대해 상대적인 비용을 계산한 후 비용이 가장 낮은 계획을 선택합니다.
(주의: CBO는 가능한 모든 계획을 평가할 시간이 충분하지 않을 수 있습니다.이 경우 지금까지 발견된 비용 중 가장 낮은 계획을 선택할 뿐입니다.)
일반적으로 느린 쿼리의 가장 큰 원인 중 하나는 쿼리(더 정확히 말하면 블록)를 처리하기 위해 읽은 행의 수이기 때문에 비용은 부분적으로 옵티마이저가 읽어야 하는 행의 수에 기초합니다.
예를 들어 다음과 같은 조회가 있다고 가정합니다.
SELECT emp_id FROM employees WHERE months_of_service = 6;
(the)months_of_service컬럼에는 NOT NULL 제약조건이 있으며 컬럼에는 일반 인덱스가 있습니다.)
여기서 옵티마이저가 선택할 수 있는 두 가지 기본 계획이 있습니다.
- 1을 읽고,합니다(「Employee」).
months_of_service=6를 참조해 주세요. - 2:.여기서 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」
months_of_service=6(그 결과 ROWID 세트가 됩니다)그 후 반환된 ROWID를 기반으로 테이블에 액세스합니다.
「종업원」테이블에 100만 행이 있다고 가정합니다.months_of_service 값의 범위는 1 ~12이며 어떤 이유로든 균등하게 분포되어 있다고 가정해 보겠습니다.
풀 스캔을 수반하는 플랜 1의 비용은 종업원 테이블의 모든 행을 읽는 데 드는 비용입니다.이것은 대략 1,000,000에 상당합니다.그러나 Oracle은 멀티 블록 읽기를 사용하여 블록을 읽을 수 있는 경우가 많기 때문에 실제 비용은 (데이터베이스의 셋업에 따라) 낮아집니다.멀티 블록 읽기 카운트를 10으로 가정해 보겠습니다. 전체 스캔의 계산된 비용은 1,000,000 / 10, 초과 비용 = 100,000입니다.
INDEX RANGE 스캔과 ROWID에 의한 테이블 룩업을 수반하는 플랜2의 비용은 인덱스 스캔 비용과 ROWID에 의한 테이블 액세스 비용이 됩니다.인덱스 범위 스캔에 드는 비용은 자세히 설명하지 않겠지만 인덱스 범위 스캔 비용은 행당 1개라고 가정해 보겠습니다. 12건 중 1건에서 일치하는 항목을 찾을 것으로 예상되므로 인덱스 스캔 비용은 1,000,000 / 12 = 83,333입니다. 여기에 테이블 액세스 비용(여기에서는 멀티 블록 읽기 액세스당 1블록은 사용할 수 없습니다= 83),333; 총비용 = 166,666.
보시다시피 플랜 1(풀스캔)의 비용은 플랜 2(인덱스 스캔+행 ID에 의한 액세스)의 비용보다 낮습니다.즉, CBO는 풀스캔을 선택합니다.
여기서 낙관론자의 가정이 사실이라면, 실제로는 플랜 1이 플랜 2보다 훨씬 더 바람직하고 효율적이며, 이는 풀 스캔이 "항상 나쁘다"는 통념을 반증한다.
만약 옵티마이저 목표가 ALL_ROWS가 아닌 FIRST_ROWS(n)라면 결과는 상당히 달라질 것이다. 이 경우 옵티마이저는 종종 전체 쿼리에 대해 덜 효율적인 비용으로 처음 몇 행을 더 빨리 반환하기 때문에 플랜 2를 선호할 것이다.
CBO는 쿼리별로 사용 가능한 각 실행 경로의 비용을 추정하는 의사결정 트리를 구축합니다.비용은 인스턴스에서 설정된 CPU_cost 또는 I/O_cost 파라미터로 설정됩니다.또한 CBO는 쿼리가 사용할 테이블과 인덱스의 기존 통계를 최대한 활용하여 비용을 추정합니다.비용만을 기준으로 쿼리를 조정해서는 안 됩니다.코스트를 사용하면, 옵티마이저가 이 기능을 하는 이유를 이해할 수 있습니다.비용을 들이지 않고 최적기가 계획을 선택한 이유를 알 수 있습니다.비용이 낮다고 해서 쿼리 속도가 빨라지는 것은 아닙니다.이것이 맞는 경우도 있고 틀린 경우도 있을 것이다.비용은 표 통계를 기반으로 하며, 표가 틀리면 비용이 틀리게 됩니다.
쿼리를 조정할 때는 각 단계의 카디널리티와 행 수를 확인해야 합니다.말이 되나요?최적기가 가정하는 카디널리티가 올바른가?반환되는 행이 합리적인가?존재하는 정보가 올바르지 않은 경우, 옵티마이저는 올바른 결정을 내리는 데 필요한 적절한 정보를 가지고 있지 않을 가능성이 높습니다.이는 테이블 및 인덱스와 cpu-stats의 통계 정보가 오래되었거나 누락되었기 때문일 수 있습니다.최적기를 최대한 활용하기 위해 쿼리를 조정할 때 통계를 업데이트하는 것이 가장 좋습니다.자신의 스키마를 아는 것도 튜닝에 큰 도움이 됩니다.옵티마이저가 잘못된 결정을 언제 선택했는지 알고 작은 힌트로 올바른 경로를 가리키면 시간을 절약할 수 있습니다.
다음은 Oracle과 함께 EXPLY PLAN을 사용하기 위한 참조 자료입니다.http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm), specific specific 、 http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300 here here found found found found found found found found 。
'FULL'에 대한 언급은 쿼리가 데이터를 찾기 위해 전체 테이블 검색을 수행 중임을 나타냅니다.이는 특정 상황에서는 정상입니다. 그렇지 않으면 인덱싱/쿼리 쓰기가 제대로 되지 않습니다.
일반적으로 설명 계획에서는 쿼리가 키를 사용하고 있는지 확인해야 합니다. 따라서 Oracle은 최소한의 행에 액세스하여 원하는 데이터를 찾을 수 있습니다.궁극적으로는 테이블의 아키텍처에 한계가 있을 수 있습니다.비용이 너무 많이 드는 경우 스키마의 레이아웃을 조정하여 성능을 향상시킬 수 있습니다.
최근 Oracle 버전에서 COST는 단일 블록 읽기에 필요한 시간 단위로 최적기가 쿼리에 걸리는 시간을 나타냅니다.
따라서 단일 블록 읽기에 2ms가 걸리고 비용이 "250"으로 표시될 경우 쿼리가 완료되는 데 500ms가 걸릴 것으로 예상됩니다.
옵티마이저는 예상 싱글 블록 및 멀티 블록 읽기 수 및 계획의 CPU 소비량을 기반으로 비용을 계산합니다.후자는 높은 CPU 비용 처리를 시도하고 피하기 위해 다른 작업보다 먼저 특정 작업을 수행함으로써 비용을 최소화하는 데 매우 유용합니다.
따라서 낙관론자가 작업 시간을 어떻게 알 수 있는지에 대한 의문이 제기됩니다.최신 Oracle 버전에서는 "시스템 통계" 컬렉션을 테이블 또는 인덱스의 통계와 혼동하지 않습니다.시스템 통계는 하드웨어의 퍼포먼스를 측정하는 것으로, 주로 다음과 같습니다.
- 단일 블록 읽기 소요 시간
- 멀티 잠금 읽기 소요 시간
- 멀티 잠금 판독의 크기(테이블 익스텐트가 최대값보다 작거나 다른 이유로 인해 가능한 최대값과 다른 경우가 많습니다).
- CPU 퍼포먼스
이러한 수치는 시스템의 동작 환경에 따라 크게 다를 수 있습니다.또, 「낮의 OLTP」조작과 「밤의 배치 리포트」조작, 및 필요에 따라서 「월말 리포트」를 위해서, 다양한 통계 세트를 보존할 수 있습니다.
이러한 통계 집합이 주어지면 특정 쿼리 실행 계획에서 다른 운영 환경의 비용을 평가할 수 있으며, 이로 인해 전체 테이블 검색을 사용하거나 인덱스 검색을 사용할 수 있습니다.
비용은 완벽하지는 않지만, 옵티마이저는 출시 때마다 자체 모니터링 능력이 향상되어 예측 비용과 비교하여 실제 비용을 피드백하여 미래에 대한 더 나은 결정을 내릴 수 있습니다.이것은 또한 예측하는 것을 오히려 더 어렵게 만든다.
병렬 쿼리 작업은 여러 스레드에 걸쳐 총 시간을 소비하므로 비용이 반드시 벽 클럭 시간이라고는 할 수 없습니다.
이전 버전의 Oracle에서는 CPU 운영 비용이 무시되었으며 싱글 및 멀티블록 읽기 관련 비용은 초기 파라미터에 따라 효과적으로 고정되었습니다.
FULL은 아마도 전체 테이블 검색을 의미하며, 이는 사용 중인 인덱스가 없음을 의미합니다.이는 쿼리가 테이블 내의 모든 행을 사용하도록 되어 있지 않은 한 일반적으로 문제가 있음을 나타냅니다.
비용은 다양한 부하, 프로세서, 메모리, 디스크, IO 및 높은 숫자의 합계가 일반적으로 불량임을 나타내는 수치입니다.계획의 근본으로 이동할 때 이 수치를 합산하여 각 지점을 검사하여 병목 지점을 찾아야 합니다.
또한 v$sql 및 v$session을 쿼리하여 SQL 문에 대한 통계 정보를 얻을 수도 있습니다.이 정보에는 모든 종류의 리소스, 타이밍 및 실행에 대한 자세한 메트릭이 포함됩니다.
언급URL : https://stackoverflow.com/questions/860450/understanding-the-results-of-execute-explain-plan-in-oracle-sql-developer