Oracle SQL Developer에서 Execute Explain Plan의 결과 이해

Oracle SQL Developer에서 Execute Explain Plan의 결과 이해

---

쿼리를 최적화하려고하는데 Explain Plan 에서 반환 된 일부 정보를 이해하지 못합니다 . 누구든지 OPTIONS 및 COST 열의 중요성을 말할 수 있습니까? OPTIONS 열에는 FULL이라는 단어 만 표시됩니다. COST 열에서 더 낮은 비용이 더 빠른 쿼리를 의미한다고 추론 할 수 있습니다. 그러나 비용 값은 정확히 무엇을 나타내며 허용 가능한 임계 값은 무엇입니까?

---

EXPLAIN PLAN의 출력은 Oracle 쿼리 최적화 프로그램의 디버그 출력입니다. COST는 비용 기반 최적화 프로그램 (CBO)의 최종 출력이며, 그 목적은 쿼리를 실행하는 데 사용할 수있는 다양한 계획을 선택하는 것입니다. CBO는 각 계획에 대한 상대적 비용을 계산 한 다음 가장 낮은 비용으로 계획을 선택합니다.

(참고 : 경우에 따라 CBO는 가능한 모든 계획을 평가할 시간이 충분하지 않습니다. 이러한 경우 지금까지 발견 된 가장 낮은 비용으로 계획을 선택합니다.)

일반적으로 느린 쿼리의 가장 큰 원인 중 하나는 쿼리를 처리하기 위해 읽은 행의 수 (정확하게는 블록)이므로 비용은 부분적 으로 옵티 마이저 추정에 필요한 행 수를 기반 으로 합니다. 읽을 수 있습니다.

예를 들어 다음 쿼리가 있다고 가정 해 보겠습니다.

SELECT emp_id FROM employees WHERE months_of_service = 6;

( months_of_service열에는 NOT NULL 제약 조건이 있고 일반 인덱스가 있습니다.)

옵티마이 저가 선택할 수있는 두 가지 기본 계획이 있습니다.

• 계획 1 : "employees"테이블에서 모든 행을 읽고 각각에 대해 술어가 참 ( months_of_service=6) 인지 확인하십시오 .
• 계획 2 : 인덱스를 읽은 다음 months_of_service=6(이로 인해 ROWID 집합이 생성됨) 반환 된 ROWID를 기반으로 테이블에 액세스합니다.

"employees"테이블에 1,000,000 (1 백만) 개의 행이 있다고 가정 해 보겠습니다. months_of_service의 값이 1에서 12까지이고 어떤 이유로 든 상당히 균등하게 분포되어 있다고 상상해 봅시다.

비용 계획 한 전체 검사를 포함, 1,000,000 거의 동일하다 직원 테이블의 모든 행을 읽는 비용이 될 것입니다; 그러나 Oracle은 종종 다중 블록 읽기를 사용하여 블록을 읽을 수 있기 때문에 실제 비용은 더 낮을 것입니다 (데이터베이스 설정 방법에 따라 다름). 예를 들어 다중 블록 읽기 수가 10이라고 가정 해 보겠습니다. 전체 스캔은 1,000,000 / 10입니다. 초과 비용 = 100,000.

비용 계획 2 ROWID하여 INDEX RANGE SCAN 및 테이블 룩업을 수반 인덱스 스캔 비용 플러스 ROWID하여 테이블에 액세스하는 비용이 될 것이다. 인덱스 범위 스캔 비용이 어떻게 계산되는지는 다루지 않겠지 만 인덱스 범위 스캔 비용이 행당 1이라고 가정 해 보겠습니다. 12 개 케이스 중 1 개에서 일치 항목을 찾을 것으로 예상하므로 인덱스 스캔 비용은 1,000,000 / 12 = 83,333입니다. + 테이블 액세스 비용 (액세스 당 1 개의 블록 읽기를 가정하고 여기서 다중 블록 읽기를 사용할 수 없음) = 83,333; 전체 비용 = 166,666.

보시다시피 플랜 1 (전체 스캔)의 비용은 플랜 2 (인덱스 스캔 + rowid 별 액세스)의 비용보다 적습니다. 즉, CBO가 FULL 스캔을 선택한다는 의미입니다.

여기에서 옵티마이 저가 가정 한 내용이 사실이라면 실제로 플랜 1이 플랜 2보다 낫고 훨씬 더 효율적일 것입니다. 이는 FULL 스캔이 "항상 나쁘다"라는 신화를 반증합니다.

옵티 마이저 목표가 ALL_ROWS 대신 FIRST_ROWS (n) 인 경우 결과는 상당히 다를 수 있습니다.이 경우 옵티마이 저는 전체 쿼리에 대해 효율성이 떨어지는 대신 처음 몇 행을 더 빨리 반환하기 때문에 계획 2를 선호합니다. .

---

CBO는 의사 결정 트리를 작성하여 쿼리 당 사용 가능한 각 실행 경로의 비용을 추정합니다. 비용은 인스턴스에 설정된 CPU_cost 또는 I / O_cost 파라미터에 의해 설정됩니다. 그리고 CBO는 쿼리가 사용할 테이블과 인덱스의 기존 통계를 최대한 활용하여 비용을 추정합니다. 비용만을 기준으로 쿼리를 조정해서는 안됩니다. 비용을 통해 최적화 프로그램이 수행하는 작업을 이해할 수 있습니다. 비용없이 최적화 프로그램이 계획을 선택한 이유를 파악할 수 있습니다. 더 낮은 비용이 더 빠른 쿼리를 의미하지는 않습니다. 이것이 사실 인 경우가 있고 이것이 잘못된 경우가있을 것입니다. 비용은 테이블 통계를 기반으로하며 잘못된 경우 비용이 잘못된 것입니다.

쿼리를 조정할 때 각 단계의 카디널리티와 행 수를 살펴 봐야합니다. 이해가 되나요? 최적화 프로그램이 올바른 것으로 간주하는 카디널리티가 있습니까? 반환되는 행이 합리적입니까? 존재하는 정보가 잘못된 경우 최적기는 올바른 결정을 내리는 데 필요한 적절한 정보를 가지고 있지 않을 가능성이 높습니다. 이것은 cpu-stats뿐만 아니라 테이블 및 인덱스에 대한 통계가 오래되었거나 누락 되었기 때문일 수 있습니다. 최적화 프로그램을 최대한 활용하기 위해 쿼리를 튜닝 할 때 통계를 업데이트하는 것이 가장 좋습니다. 스키마를 아는 것도 튜닝 할 때 큰 도움이됩니다. 옵티마이 저가 언제 정말 나쁜 결정을 선택했는지 알고 작은 힌트로 올바른 경로를 가리키면 많은 시간을 절약 할 수 있습니다.

---

다음은 Oracle에서 EXPLAIN PLAN을 사용하기위한 참조입니다 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm ), 여기에서 찾을 수있는 열에 대한 특정 정보 : http : / /download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300

'FULL'에 대한 귀하의 언급은 쿼리가 데이터를 찾기 위해 전체 테이블 스캔을 수행하고 있음을 나타냅니다. 이는 특정 상황에서 괜찮습니다. 그렇지 않으면 인덱싱 / 쿼리 쓰기가 좋지 않음을 나타냅니다.

일반적으로 Explain 플랜을 사용하면 쿼리가 키를 사용하고 있는지 확인하기를 원하므로 Oracle은 가능한 최소한의 행에 액세스하여 원하는 데이터를 찾을 수 있습니다. 궁극적으로, 테이블의 아키텍처에 대해서만 지금까지 도달 할 수 있습니다. 비용이 너무 높으면 성능 기반이되도록 스키마 레이아웃을 조정하는 것을 고려해야 할 수 있습니다.

---

최근 Oracle 버전에서 COST는 옵티마이 저가 쿼리에 소요될 것으로 예상하는 시간을 나타내며 단일 블록 읽기에 필요한 시간 단위로 표시됩니다.

따라서 단일 블록 읽기에 2ms가 걸리고 비용이 "250"으로 표시되면 쿼리를 완료하는 데 500ms가 걸릴 것으로 예상 할 수 있습니다.

옵티마이 저는 추정 된 단일 블록 및 다중 블록 읽기 수와 계획의 CPU 소비량을 기반으로 비용을 계산합니다. 후자는 높은 CPU 비용 작업을 시도하고 방지하기 위해 다른 작업보다 먼저 특정 작업을 수행하여 비용을 최소화하는 데 매우 유용 할 수 있습니다.

이것은 옵티마이 저가 작업에 걸리는 시간을 어떻게 아는지에 대한 질문을 제기합니다. 최신 Oracle 버전에서는 "시스템 통계"모음을 사용할 수 있으며, 이는 테이블 또는 인덱스에 대한 통계와 혼동되지 않아야합니다. 시스템 통계는 하드웨어 성능을 측정 한 것으로, 주로 다음과 같습니다.

1. 단일 블록 읽기에 걸리는 시간
2. 다중 블록 읽기에 걸리는 시간
3. 다중 블록 읽기의 크기 (테이블 익스텐트가 최대 값보다 작기 때문에 가능한 최대 값 및 기타 이유).
4. CPU 성능

이러한 수치는 시스템의 운영 환경에 따라 크게 다를 수 있으며 "주간 OLTP"작업 및 "야간 일괄보고"작업 및 원하는 경우 "월말보고"에 대해 서로 다른 통계 집합을 저장할 수 있습니다.

이러한 통계 세트가 주어지면 주어진 쿼리 실행 계획을 다른 운영 환경에서 비용에 대해 평가할 수 있으며, 이로 인해 전체 테이블 스캔 사용을 촉진하거나 인덱스 스캔 사용을 촉진 할 수 있습니다.

비용이 완벽하지는 않지만 옵티마이 저는 모든 릴리스에서 자체 모니터링을 더 잘 수행하고 향후 더 나은 결정을 내리기 위해 예상 비용과 비교하여 실제 비용을 피드백 할 수 있습니다. 이것은 또한 예측하기 어렵게 만듭니다.

병렬 쿼리 작업은 여러 스레드에 걸쳐 총 시간을 소비하므로 비용이 반드시 벽시계 시간은 아닙니다.

이전 버전의 Oracle에서는 CPU 작업 비용이 무시되고 단일 및 다중 블록 읽기의 상대적 비용이 init 매개 변수에 따라 효과적으로 수정되었습니다.

---

FULL은 아마도 전체 테이블 스캔을 의미하며 이는 사용중인 인덱스가 없음을 의미합니다. 이것은 쿼리가 테이블의 모든 행을 사용하지 않는 한 일반적으로 무언가 잘못되었음을 나타냅니다.

비용은 서로 다른로드, 프로세서, 메모리, 디스크, IO의 합계를 나타내는 숫자이며 높은 숫자는 일반적으로 좋지 않습니다. 계획의 루트로 이동할 때 숫자를 합산하고 각 분기를 검사하여 병목 지점을 찾아야합니다.

You may also want to query v$sql and v$session to get statistics about SQL statements, and this will have detailed metrics for all kind of resources, timings and executions.

참고URL : https://stackoverflow.com/questions/860450/understanding-the-results-of-execute-explain-plan-in-oracle-sql-developer