In this Document
| Purpose |
| Scope |
| Details |
| What is parallelism best used for? |
| When and when not to Implement Parallel Execution |
| Important Parameter settings |
| What is DEFAULT Parallelism? |
| Best practices |
| Setting DOP on tables. |
| Setting DOP on Indexes. |
| Use Resource Manager. |
| Run PDML with parallel_force_local = TRUE in RAC environment. |
| Verify, Monitor, and Tune Parallel execution settings with the Health-Check script |
| 1. The Main Report. (pxhcdr_*_main.html) |
| 2. Table/Index Mismatch Report. (pxhcdr_*_dop.html) |
| 3. Resource Consumer Group html report. (pxhcdr_*_rscr.html) |
| 4. Monitored SQL zip (pxhcdr_*_monitor.zip) |
| 5. Log zip file |
| Additional Resources |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]Information in this document applies to any platform.
PURPOSE
As with many tunable options in Oracle, what works best in one environment may not be the best for another. This is certainly true when setting up and configuring parallelism in the database. In addition to properly setting database parameters, parallel execution performance is impacted by the availability of system resources, specifically IO, CPU, and memory. It's always good practice to start with the default values when first setting up parallelism. Determine if these setting are sufficient before making any changes.
SCOPE
DBAs that want to view, monitor, and tune parallel execution parameters.
DETAILS
What is parallelism best used for?
- Queries requiring large table scans, joins, or partitioned index scans
- Creation of large indexes
- Creation of large tables (including materialized views)
- Bulk insertions, updates, merges, and deletions
When and when not to Implement Parallel Execution
| Use PX when you have... | Do Not use PX on... |
|---|---|
|
|
This information was obtained from the the Oracle? Database Data Warehousing Guide
Important Parameter settings
Oracle Database automatically sets parallel execution parameters. In most cases the default values, listed below, are sufficient to start using parallelism in the database.
The following list is not complete; it lists the primary parameters used when configuring parallelism.
Default setting is MANUAL This is the behavior used in versions prior to 11.2.
If set to AUTO Oracle enables automatic degree of parallelism, statement queuing, and in-memory parallel execution. In this mode, Oracle determines if parallelism should be used and at what degree.
I/O Calibration statistics must exist when using auto dop. For additional information about AUTO DOP see:
Configuring and using Calibrate I/O (Doc ID 727062.1)
Automatic Degree of Parallelism in 11.2.0.2 [ID 1269321.1]
If set to LIMITED statement queuing and in-memory Parallel Execution are disabled. Automatic DOP will work only on objects with a DEFAULT DOP setting, all other objects retain MANUAL behavior.
Oracle will compute the value based on this formula.
Concurrent_parallel_users is derived from memory settings.
Use a value of 1 if automatic memory management is disabled.
Use a value of 2 if PGA automatic memory management is enabled.
Use a value of 4 if Global memory management or SGA memory target are used with PGA automatic memory management.
Explicitly setting this too high may overload the system during peak periods.
A low setting underutilizes resources which may cause some operations, expected run in parallel, to run serially.
Important parameter used in part to derive the database default parallelism value, DEFAULT DOP. Always use the default here.
Used in a RAC environment to limit parallel operations to the current instance.
Oracle will determine the DOP for statements based on the current database workload. Operation may be downgraded on busy instances using MANUAL parallel_degree_policy.
By default Oracle will shut down parallel servers after they have been inactive for a period of time. This parameter controls that behavior.
Set this value > 0 on systems that frequently have a number of active parallel processes. As an example, periodic querying of v$px_process shows 10 server processes busy most of the time.
Instead of shutting down and restarting the server processes it may be better to set PARALLEL_MIN_SERVERS = 10. This way the database starts the processes at instance startup and they remain available until the instance is shutdown.
Use the default as it is adequate for most applications. This parameter must be set to the same value on all RAC instances.
16384 bytes if COMPATIBLE is set to 11.2.0 or higher
4096 bytes if COMPATIBLE is less than 11.2.0 and PARALLEL_AUTOMATIC_TUNING is set to true
2148 bytes if COMPATIBLE is less than 11.2.0 and PARALLEL_AUTOMATIC_TUNING is set to false
Used only if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. This parameter sets the maximum degree used by all statements executed in parallel.
When set to CPU the maximum degree of parallelism for a statement is limited by the number of CPUs in the system.
Setting this to IO limits the DOP to the I/O capacity of the system. The value is calculated by dividing the total system throughput by the maximum I/O bandwidth per process.
You can also specify an INTEGER to set a max DOP.
Used only when PARALLEL_DEGREE_POLICY is set to AUTO.
This is a limit to the number of parallel servers that can run concurrently at any give time. When the value is reached any statement requesting to run in parallel will be placed in the statement queue
and remain there until servers become available. Operations that run serially are executed immediately as this parameter only influences parallel operations.
Used only when PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
This is the amount of time, in seconds, that Oracle will wait before considering the statement for AUTO DOP. An INTEGERcan also be specified. AUTO is 10 seconds.
Set this to 1 if testing AUTO DOP.
What is DEFAULT Parallelism?
The database DEFAULT DOP is derived from several of the parameters listed above. The Default DOP is used on a table or index that has a DEFAULT setting (user_tables.degree, user_indexes.degree):
- For a single instance, DEFAULT DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
- For a RAC configuration, DEFAULT DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
The default dop is set by specifying the PARALLEL clause with no degree listed.
ex:
SQL>alter table scott.emp parallel;
Table altered.
SQL> select degree,instances from user_tables where table_name = 'EMP';
DEGREE INSTANCES
---------------------------------------- ---------------------------------------
DEFAULT DEFAULT
It is not recommended to set a DEFAULT DOP on systems with a high degree of concurrency.
Best practices
Setting DOP on tables. Parallelism works best for large, resource intensive, operations. It is designed to breaks a large task into smaller tasks handled by parallel server processes.
Keeping this in mind, here are are some guidelines to help determine parallel setting based on the size of a table.
If a table is....
- 200 megabytes or smaller Do not enable parallelism
- 200 megabytes up to 5gigabytes Use a DOP of at least 4
- 5gigabytes or larger Use a DOP of 32
When setting a degree of parallelism make sure to define a power of 2 so that work can be evenly distributed among the execution servers.
Remember that these values are used when parallel_degree_policy is set to MANUAL or LIMITED. Oracle will determine the DOP when parallel_degree_policy is set to AUTO.
More information about this can be seen here VIDEO: Best practices for Parallel Execution
Setting DOP on Indexes.Table and Index DOP should match in most cases. Having different values for an index and table may produce unexpected plans and performance.
Use Resource Manager. Use Resource manager to control parallelism for systems with varying workload.
As an example create a directive for OLTP users so that no parallel processes are utilized. OLTP activities usually involves short and quick operations that would not benefit from parallel processings. Likewise, create a critical account group with a high or unlimited DOP so jobs and reports that are issued have the required servers available. Doing this properly allocates database resources based on business rules.
How to Limit the Parallel Degree for PX Queries with Resource Manager [ID 1321099.1]
How to Set Maximum Degree of Parallelism (DOP) by the Resource Manager, What is PARALLEL_MAX_DEGREE [ID 1508338.1]
If you have a RAC environment, we recommend you run parallel DML (PDML) on a single node. Running PDML cross-node significantly increases load and decreases performance. To run parallel DML, alter the session as follows:
session enable parallel dml;
alter session set parallel_force_local = true;
Verify, Monitor, and Tune Parallel execution settings with the Health-Check script
To check and review parallel setting in the database, it is highly recommended to run script pxhcdr.sql found in note 1460440.1.
1460440.1 Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports
The generated output includes 5 items.
- Main report
- Table/Index Mismatch report
- Resource Consumer Group html report
- Monitored SQL zip
- Log zip file
Key points to review from the generated output.
Review instance parameter settings related to parallelism. Compare them with the default values listed above.
* Observations section.
Check the More column for suggestions. Look for entries that report values as too high, low, or obsolete. Compare them with DEFAULT values if they differ and adjust based on observed performance.
DOP mismatch between tables and indexes will be reported here as well. Check the DOP html page for more information.
In this example of the main report the value of PARALLEL_MAX_SERVERS is too high based on the number of cpus reported on the system. The main page lists all init.ora setting making it easy to determine if the value needs to be altered.
* PX System Statistics section.
Review Servers Highwater which shows the maximum number of concurrent PX server processes. This can help in properly setting PARALLEL_MAX_SERVERS.
This section also shows the number of parallel servers that were working (Servers In Use) while the report was generated. To monitor the same information outside of the report run this:
select * from GV$PX_PROCESS_SYSSTAT;
* Instance Parameters section.
Check to see if resouce_limit is set to TRUE. Review the Resource Consumer Group html report if set to TRUE as parallelism behavior can be managed with resource limits.
Confirm that the value of CPU_COUNT is the default.
2. Table/Index Mismatch Report. (pxhcdr_*_dop.html)
* Tables with non-default DOP.
Review current DOP settings for the tables listed in this section to confirm that the DOP settings are in line with the size of the object.
* Tables and Indexes with DOP mismatch
A table and index with different DOP values can generate unexpected plans or show an increase in px waits.
It's best that the index and table DOP match based on the size of the object. Mismatches can cause the issues reported here:
Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level [ID 738464.1]
In this screen shot table CUST is 10GB is size with a DOP of 4. This is quite low for an object of that size. Another issue is that there is an index on this table defined with no parallelism.
Consider increasing the DOP for the table and index.
Table FACT, on the other hand, is 10MB is size with a DEFAULT DOP and the index on this table is set to 1, no parallel. In this case, it would be best to set table FACT to NOPARALLEL.
ex)
alter table fact noparallel;
alter index fact_idx noparallel;
The PROD table is 500MB with varying DOP settings on the table and DEFAULT settings on the index. The DOP on the table is 1, serial but Inst is set to 2. Using the recommendation noted above, set the DOP to 4 on the table and index.
ex)
alter table prod parallel (degree 4 instances 4);
alter index prod_idx parallel (degree 4 instances 4);
3. Resource Consumer Group html report. (pxhcdr_*_rscr.html)
Parallel operation can be controlled using resource manager. Use the output from this portion of the report to see if a resource plan is active and it that plan impacts parallel operation in the database.
This screenshot shows that plan "LIMIT_DOP" is active. Checking the plan directive we can see that parallel operations are limited to 4 parallel processes per statement for any user in defined in the OTHER_GROUPS.
4. Monitored SQL zip (pxhcdr_*_monitor.zip)
This portion of the report provides up to 25 SQL Monitor Reports for PX statements. The output provides parallel statistics for the captured sql.
Output below was generated from a --- select /*+ PARALLEL (emp,8) * from emp;
As shown, the select statement includes a parallel hint requesting 8 server processes. We can see that the operation was downgrade from 8 to 4 and that server processes were used from each node.
Reconciling the information in the monitored sql with that in the Resource Consumer Group html report shows that the downgrade was the result of a resource limit.
Sql Monitor information can also be found in the main report.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21754115/viewspace-1366417/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21754115/viewspace-1366417/
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
