Why Is My Query Using a High Value for Degree of Parallelism
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterInformation in this document applies to any platform.
GOAL
You want to know why the parallel execution of your SQL statement is using a high value for degree of parallelism (DOP).
SOLUTION
Factors that may cause a high calculated value of DOP:
1. Object degree is set to DEFAULT
The default Degree of Parallelism (DOP) is dependent on CPU_COUNT, which can be the virtual number and not the physical number. This can lead to a high default DOP when there are a lot of virtual CPUs, especially in a RAC database, where the number of instances is also in the calculation. (Note: The specified object DOP is ignored when (1) parallel_degree_policy = AUTO, and (2) when and the object DOP is DEFAULT and parallel_degree_policy = LIMITED; in these cases, DOP is automatically determined based on various factors and IO calibration statistics. If IO calibration statistics do not exist, the manual behavior is used.)
col owner for a15
col table_name for a35
col index_name for a35
select owner, table_name, degree from dba_tables where degree like '%DEFAULT%' order by owner, table_name;
select owner, index_name, degree from dba_indexes where degree like '%DEFAULT%' order by owner, index_name;
Solution:
Either (a) alter the objects so they no longer have a DEFAULT degree or (b) set parallel_degree_limit to a specific integer (we recommend a value of 32 or lower; keep in mind that normally 2 slave sets, each of size DOP, are used for a query; sometimes more than 2 slave sets can be needed).
alter table
alter index
-- or --
-- set parallel_degree_limit to a specific integer
alter system set parallel_degree_limit = 16;
2. Parallel hint doesn't specify the DOP to use or parallel hint is invalid
-- this statement-level hint will cause DEFAULT parallelism to be calculated on all objects in the query (11.2+)
select /*+ parallel */ * from dba_objects;
-- this hint will cause DEFAULT parallelism to be calculated on the hinted object(s)in the query
select /*+ parallel(o) */ * from dba_objects o;
-- this is an invalid hint ,so DEFAULT parallelism will be used
select /*+ parallel 8 */ * from dba_objects;
Solution:
Change the hinted parallelism to a specific integer and fix invalid hints. (A degree explicitly set in a hint will override AutoDOP and DEFAULT calculations.)
select /*+ parallel(4) */ * from dba_objects;
-- specify the desired DOP for each object in the query
select /+* parallel(o,4) */ * from dba_objects o;
-- fix the invalid parallel hint
select /*+ parallel (8) */ * from dba_objects;
3. Parameter parallel_degree_policy is set to AUTO or LIMITED (11.2 or higher only)
As long as the requirements are met for AutoDOP (i.e. IO calibration statistics exist), AutoDOP will calculate the optimal DOP for you (regardless of degree on objects); sometimes this can be high.
Solution:
Limit the calculated AutoDOP by virtue of setting parallel_degree_limit to a specific integer. Note that when parallel_degree_policy = LIMITED, this limit will apply only to objects that have a DOP of DEFAULT; the limit will be ignored if the object DOP is an integer and parallel_degree_policy = LIMITED.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21754115/viewspace-1379049/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server 的max degree of parallelism引數SQLServerParallel
- RAC中的並行查詢 DOP(Degree of Parallelism)並行Parallel
- Automatic Degree of Parallelism in 11.2.0.2 (文件 ID 1269321.1)Parallel
- Why are Realistic Diplomas highly wanted?
- Why is gam_server using up 100% of my CPU on Red Hat Enterprise Linux 4GAMServerLinux
- Oracle 11g中的IO Calibrate(IO校準)--Automatic Degree of Parallelism(DOP)OracleParallel
- [GO-LANG] Why is my trivial program such a large binary?Go
- LOW_VALUE、HIGH_VALUE、ENDPOINT_VALUE轉換--UTL_RAW、DBMS_STATS.CONVERT_RAW_VALUE
- How to check why identical SQL Statements have high version countIDESQL
- oracle 格式化user_tab_col_ststistics(low_value,high_value)Oracle
- HDFS High Availability Using the Quorum Journal ManagerAI
- Dynamo: Amazon’s Highly Available Key-value StoreAI
- PARALLEL_DEGREE_POLICParallel
- 如何將列中的low_value和high_value轉換為實際的值
- WhyASMLIBandwhynot?ASM
- Storm Topology ParallelismORMParallel
- How to convert a numeric value or currency to English words using C#C#
- ORA-00937 Errors While Executing A Query Using Microsoft OLEDB DriversErrorWhileROS
- DBA_TAB_PARTITIONS中 high_value欄位long型轉varchar2
- ElasticSearch7.3學習(二十五)----Doc value、query phase、fetch phase解析Elasticsearch
- hadoop官網翻譯之HDFS High Availability Using the Quorum Journal ManagerHadoopAI
- Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.Server
- Why TypeScript?TypeScript
- Why NoSQL?SQL
- Oracle轉換user_tab_columns中high_value值為十進位制Oracle
- CUDA之Dynamic Parallelism詳解(二)Parallel
- Oracle user_tab_partitions.high_value欄位 LONG型別 讀取的幾種方法Oracle型別
- Why React HooksReactHook
- WHY review code?View
- Why Redis 4.0?Redis
- [譯] WebAssembly: How and whyWeb
- WhyRedis4.0?Redis
- why use dynamic SQL?SQL
- whystea2
- whystea1
- Mac 安裝 oh-my-zsh + autojump + zsh-autosuggestions + zsh-syntax-highlighting + solarized 配色MacZed
- My PromisePromise
- MY WEBWeb