[SQL] 이유는 하나의 SQL 쿼리에 대한 더 많은 CPU를 소비하는 오라클 11g를 강제 수없는 것
SQL이유는 하나의 SQL 쿼리에 대한 더 많은 CPU를 소비하는 오라클 11g를 강제 수없는 것
나는 거대한 테이블에서 실행되는 몇 가지 엄청난 쿼리가 있습니다. 이 쿼리는 CPU가 병목 현상, 그리고 시간 동안 실행 것으로 보인다. 나는 오라클이 내부적으로 쿼리의 실행을 paralellize하는 11g 릴리스 2의 새로운 기능을 많이 가지고 있음을 이해합니다. 그러나 더 내가 쿼리에 넣어 힌트의 종류, 나는 데이터베이스 상자에 1 개 이상의 CPU를 사용하는 것 캔트 어떤 문제가 없습니다. 난 그냥 100 %로 하나 개의 CPU를 밀어 결국 다음 시간 동안 앉아서, 내가이 쿼리를 실행하지만 매번, 8 개 CPU를 매우 존경 Solaris 시스템을 가지고있다.
내가 시도 힌트는 다음과 같습니다
SELECT /*+ PARALLEL */ ...
SELECT /*+ PARALLEL(5) */ ...
SELECT /*+ PARALLEL(10) */ ...
상자에 전체 CPU 사용량을 볼 때 그 어느 것도 작업에 나타나지 않았다. 항상 100 %에서 하나 개의 CPU를 말뚝 것 같았다. 불행하게도, 심지어 계획을 실행하는 데 영원히 걸릴 것 같다 설명합니다. 나는 시도하고 다른이 다른 힌트와 함께 계획을 설명하고 도움이되는지 볼 수 있습니다. 그것은 일부 쿼리가 실행이 시간에있는 경우에도 단순히 취소 paralleable 것을 가능?!? 이 쿼리의 기본 테이블은 335,000,000 행이 있습니다.
SQL 쿼리 텍스트 :
http://pastie.org/8634380
시스템 매개 변수 :
http://pastie.org/8634383
편집하다:
세부 계획 설명 - 아니 병렬 처리 :
http://pastebin.com/HkZgbPpf
관련 시스템 매개 변수 최적화 :
http://pastie.org/8639841
또한 편집 : 우리는 계획이 2 시간 이상 소요 EXPLAIN 이유를 이해하기 위해 오라클에게 도달했습니다. 우리는 여러 가지가 계획을 설명 실행하려고 아웃 타이밍.
해결법
-
==============================
1.오라클 병렬 처리에 대해 이해하는 가장 중요한 점은 복잡 것입니다. 병렬 처리를 최적화 많은 장기 실행 쿼리를 테스트 매개 변수 및 회의를 많이 확인, 설명서를 읽고, 오라클 지식을 많이 필요로한다.
오라클 병렬 처리에 대해 이해하는 가장 중요한 점은 복잡 것입니다. 병렬 처리를 최적화 많은 장기 실행 쿼리를 테스트 매개 변수 및 회의를 많이 확인, 설명서를 읽고, 오라클 지식을 많이 필요로한다.
올바른 질문을 물어
병렬 문제가 정말 세 가지 질문을 포함한다 :
최고의 도구를 사용하여
가장 좋은 도구로 바로 이동 - SQL은 활성 보고서 모니터링. 는 HTML 보고서를 당신의 SQL_ID 찾기 및 생성 : 선택 dbms_sqltune.report_sql_monitor (SQL_ID => 'your_sql_id'을 입력 => '활성') 듀얼 ;.에서 이 실행 계획의 각 단계에 소요 된 많은 시간을 알 수있는 유일한 방법입니다. 그리고 효과적으로 사용하고, 어디에 얼마나 많은 병렬 당신을 말할 것이다. 예를 들면 :
또 다른 좋은 옵션 종류 => '텍스트'입니다. 그것은 아주 많은 정보를 가지고 있지 않지만 그것은 공유 봐 쉽게로 빠른입니다.
SQL은 모니터링도 요구 DOP 및 할당 된 DOP를 포함한다 :
선택 100 줄 병렬로 인해 캐시되지 않은 순서의 한 단계에서 다음 모든은 정지를 아름답게 실행할 수 있지만. 는 계획, 추적, 또는 시간 동안 AWR 보고서를 설명하고 문제를 볼 수 없습니다에서 당신은 응시 할 수 있습니다. 활성 보고서는 찾을 느린 단계는 거의 사소한 수 있습니다. 시간 문제 거짓말을 추측을 낭비하지 마십시오.
그러나 다른 도구는 여전히 필요합니다. 에 대한 계획을 설명 ... 그리고 (dbms_xplan.display) 테이블에서 선택 *로 생성 된 설명 할 계획; 정보의 몇 가지 주요 부분을 제공 할 것입니다. 특히 노트 섹션은 쿼리 병렬 처리를 요청하지 않은 이유는 여러 가지 이유를 포함 할 수 있습니다.
그런데 왜 나는 병렬 서버의 수를 얻었 는가?
관련 정보는 매우 유용하지만, 때때로 부정확하거나 오해의 소지가있는 여러 가지 매뉴얼을 통해 전염됩니다. 많은 신화와 병렬 처리에 대해 많은 나쁜 조언이있다. 그리고이 기술은 각 릴리스와 크게 변경됩니다.
당신이 신뢰할 수있는 소스를 모두 합친 때, 병렬 서버의 수에 영향을 미치는 요인의 목록은 놀라 울 정도로 크다. 아래 목록은 내가 가장 중요한 요소는 무슨 생각으로 대략 주문한다 :
이 목록은 확실히 완료되지이며, 12C 기능을 포함하고 있지 않습니다. 그리고 운영 체제 및 하드웨어 문제를 해결하지 않습니다. 그리고 그것은 끔찍하게 어려운 질문, 응답하지 않습니다 "병렬 최고 수준의 무엇을?" (짧은 답변 :. 더 일반적으로 더 나은이지만, 다른 프로세스의 비용) 잘하면 적어도 당신에게 이러한 문제가 얼마나 어려운 감각, 찾고 시작하기에 좋은 장소를 제공합니다.
-
==============================
2.오라클은 기본적으로 기본 테이블에 액세스 ROWID의 세트를 제공하기 위해 여러 비트 맵 인덱스를 결합하는 수단입니다 여기에 star_transformation를 사용하고 그래서. 테이블에 액세스 할 ROWID의 사용 전체 테이블 스캔 (파티션의 일부 또는 바람직하게는 스캔) 할 것이라는 방식이 아니라 병렬 매우 의무 동작이다.
오라클은 기본적으로 기본 테이블에 액세스 ROWID의 세트를 제공하기 위해 여러 비트 맵 인덱스를 결합하는 수단입니다 여기에 star_transformation를 사용하고 그래서. 테이블에 액세스 할 ROWID의 사용 전체 테이블 스캔 (파티션의 일부 또는 바람직하게는 스캔) 할 것이라는 방식이 아니라 병렬 매우 의무 동작이다.
오라클은 행의 비교적 적은 수의 이러한 조건이 모두 일치 할 것으로 추정하기 때문에 star_transformation를 사용하고 있습니다 - 1500 만 행의 추정치가 정확한지 아닌지는 듣고 재미있을 것입니다. 335분의 15은 행이 완전히 무작위 블록에 흩어져 끝나게하지 않는 인덱스 기반의 방법을 사용하기로 결정이 적절한 때문에 그것의 얼굴에, 테이블 행의 4.4 %에 관한 것입니다 그리고 당신은 30 %를 액세스하는 블록.
어쨌든, 난 오라클은 star_transformation를 선택하는 것입니다 경우 다음 병렬 힌트는 무관하게하는 느낌이 듭니다.
star_transformation에 대한 대안은 내가 전체 테이블 스캔 및 병렬 처리를위한 힌트에 먼저 도움이 될 것이라고 생각 때문에, 큰 테이블에 전체 테이블 스캔을 사용하는 것입니다. 또한 일시적으로뿐만 아니라 비활성화 스타 변환에 대한 ALTER 세션 명령을 실행할 수 있지만 힌트의 중요한 부분은 당신이 전에 병렬로 수영을 원하는 정확히 접근 방법 말을하는 것입니다.
또한 참고 : 덧붙여, 당신은 계획에서 볼 수있는 스타의 변화에 기인하는 임시 테이블 - 차원 테이블이 먼저 해당 행을 찾기 위해 스캔, 오라클은 조인 백 동안 다시 유용한 부분 집합을 찾을 필요가 없습니다 임시 테이블에 저장하여 쿼리의 단계입니다. 이 동작을 비활성화 할 수 있지만, 그것은 아마 여기 잘하고있어.
또 다른 참고 : (가) 계획을 설명하는 또 다른 모습을했다 - 0시 27분 44초의 그 시간을 0시 27분 45초 등은 누적됩니다. 그들은 예 / B28 / EUDIQSBV의 TABLE ACCESS BY INDEX ROWID에 대한 0시 27분 43초을 위해 포함되어 있습니다. 당신이 고려 것을 가지고가는 경우에, 당신은 해시가 몇 초 각을 조인 것을보고, 성능 돼지은 3 주위 시간 해시 그룹입니다. 그것은 추정에 따라 임시 공간 4GB의를 사용하고. 그건 아마도 심각한 드레인 - 그것은하고있어 얼마나 많은 패스를보기 위해 V $ SQL_WORKAREA를 사용하여 실행을 모니터링하지만, PQ는 한 스토리지 대역폭을 가질뿐만 아니라 그에 대한 답변입니다.
-
==============================
3.병렬 작업은 일부 특정 경우에 가치를 제공 할 수 있고, 당신은 그냥 쿼리에 PARALLEL 힌트를 넣어 경우이 오라클은 아무것도 애드혹을 평행 의미하지 않습니다. 예를 들어 아래를 참조하시기 바랍니다 :
병렬 작업은 일부 특정 경우에 가치를 제공 할 수 있고, 당신은 그냥 쿼리에 PARALLEL 힌트를 넣어 경우이 오라클은 아무것도 애드혹을 평행 의미하지 않습니다. 예를 들어 아래를 참조하시기 바랍니다 :
SQL> select * from t 2 where id = 14 3 / Execution plan ---------------------------------------------------------- Plan hash value: 1859958591 -------------------------------------------------------------------------------- ---------------------------- | Id | Operation | Name | Rows | Byt es | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------------------------- | 0 | SELECT STATEMENT | | 5210 | 21 67K| 478 (1)| 00:00:06 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 5210 | 21 67K| 478 (1)| 00:00:06 | |* 2 | INDEX RANGE SCAN | T_FK_I | 5210 | | 20 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=14) Statistics ---------------------------------------------------------- 17 recursive calls 0 db block gets 10607 consistent gets 0 physical reads 0 redo size 3734464 bytes sent via SQL*Net to client 994894 bytes received via SQL*Net from client 7256 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5295 rows processed SQL> select /*+ parallel(4) */ * from t 2 where id = 14 3 / Execution plan ---------------------------------------------------------- Plan hash value: 1859958591 -------------------------------------------------------------------------------- ---------------------------- | Id | Operation | Name | Rows | Byt es | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------------------------- | 0 | SELECT STATEMENT | | 5210 | 21 67K| 478 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 5210 | 21 67K| 478 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_FK_I | 5210 | | 20 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=14) Note ----- - Degree of Parallelism is 1 because of hint Statistics ---------------------------------------------------------- 17 recursive calls 0 db block gets 10607 consistent gets 0 physical reads 0 redo size 3734464 bytes sent via SQL*Net to client 994894 bytes received via SQL*Net from client 7256 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5295 rows processed SQL> select /*+ full(t) */ * from t 2 where id = 14 3 / Execution plan ---------------------------------------------------------- Plan hash value: 565085413 -------------------------------------------------------------------------------- --------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime | -------------------------------------------------------------------------------- --------- | 0 | SELECT STATEMENT | | 5210 | 2167K| 3858 (1)| 0 0:00:47 | |* 1 | TABLE ACCESS FULL| T | 5210 | 2167K| 3858 (1)| 0 0:00:47 | -------------------------------------------------------------------------------- --------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=14) Statistics ---------------------------------------------------------- 17 recursive calls 1 db block gets 19468 consistent gets 507 physical reads 0 redo size 3734334 bytes sent via SQL*Net to client 994894 bytes received via SQL*Net from client 7256 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5295 rows processed SQL> select /*+ parallel(4) full(t) */ * from t 2 where id = 14 3 / Execution plan ---------------------------------------------------------- Plan hash value: 298470658 -------------------------------------------------------------------------------- ----------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------- ----------------------------------------- | 0 | SELECT STATEMENT | | 5210 | 2167K| 1070 (1) | 00:00:03 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 5210 | 2167K| 1070 (1) | 00:00:03 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 5210 | 2167K| 1070 (1) | 00:00:03 | Q1,00 | PCWC | | |* 4 | TABLE ACCESS FULL| T | 5210 | 2167K| 1070 (1) | 00:00:03 | Q1,00 | PCWP | | -------------------------------------------------------------------------------- ----------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("ID"=14) Note ----- - Degree of Parallelism is 4 because of hint Statistics ---------------------------------------------------------- 12 recursive calls 65 db block gets 17262 consistent gets 14401 physical reads 0 redo size 3736075 bytes sent via SQL*Net to client 994894 bytes received via SQL*Net from client 7256 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5295 rows processed난 그냥 여전히 인덱스 액세스를 가지고 쿼리에 PARALLEL 힌트를 추가 할 때 당신이 볼 수 있듯이. 그리고 당신은 질문에 대한 대답은 당신이 믿는 가난한 그래서 당신의 현재 계획입니다 먼저 실행 계획 및 통계를 분석하고 얻을해야합니까? 이 최고 일 수 있습니다. 당신은 또한 병렬 전체 검사가 훨씬 더 논리적했던 볼 수 있듯이 (포함 디스크 읽기), 그렇다면 병렬 처리 수준은 당신이 어떤 승리 대신 성능 저하를 얻을 수 있습니다 무관 읽습니다. 그리고 마지막으로는 없습니다 이상 - 당신은 분할과 같은 oprion을 고려 했는가? 당신이 안정적이고 예측 가능한 기준에 따라 데이터의 단지 작은 부분을 얻을 필요가 있다면 그것은 매우 도움이 될 수 있습니다.
from https://stackoverflow.com/questions/21127987/why-cant-i-seem-to-force-oracle-11g-to-consume-more-cpus-for-a-single-sql-query by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
| [SQL] SQL 서버 결정적 사용자 정의 함수 (0) | 2020.04.12 |
|---|---|
| [SQL] 데이터베이스 내에서 주소 표준화 (0) | 2020.04.12 |
| [SQL] DB 설계는 하위 유형을 사용하거나하지 않으려면? (0) | 2020.04.12 |
| [SQL] 드롭 다운리스트 데이터 소스 (0) | 2020.04.12 |
| [SQL] SQL 서버 : 5 열 이상 동적 피벗 (0) | 2020.04.12 |