Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1365479
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19









分类: Oracle

2012-07-03 23:03:52

Parallel query is suitable for a certain class of large problems: very large problems that have no other solution. Parallel query is my last path of action for solving a performance problem; it's never my first course of action. Parallel Query Settings I will not discuss the physical setup of parallel query operations. That topic is well covered in both the Oracle Concepts Guide and Data Warehousing Guide. As I mentioned earlier, my current favorite way to set up parallelism in Oracle is using the automatic tuning option first introduced in Oracle8i Release 2 (version 8.1.6): PARALLEL_AUTOMATIC_TUNING = TRUE. With this setting, the other parallel settings are automatically set. Now, all I need to do is set the PARALLEL option on the table (not PARALLEL , just PARALLEL) and Oracle will, when appropriate, parallelize certain operations on that table for me. The degree of parallelism (how many processes/threads will be thrown at a problem) will be decided for me and vary over time as the load on the system varies. I have found that, for most cases, this achieves my desired goal, which is usually to get the best performance, with the least amount of work, in a manner that is most manageable. Setting a single parameter is a great way to get there. For the novice user wanting to play with parallel query for the first time, parallel automatic tuning is a good way to get started. As you develop an understanding of what parallel query does and how it does it, try tweaking some of the other parallel settings: PARALLEL_ADAPTIVE_MULTI_USER Controls whether the degree of parallelism should vary over time as the load on the system does; should the algorithm for assigning resources "adapt" to the increase in load. PARALLEL_EXECUTION_MESSAGE_SIZE Sets the size of the message buffers used to pass information back and forth between the processes executing the parallel query. PARALLEL_INSTANCE_GROUP Applies only to Oracle RAC configurations (Oracle Parallel Server, OPS, in Oracle8i and earlier). Allows you to restrict the number of instances that will be used to perform a parallel operation (as opposed to the number of processes an instance will use). PARALLEL_MAX_SERVERS Sets the maximum number of parallel query slaves (like dedicated servers but for parallel operations) your instance will ever have. PARALLEL_MIN_PERCENT Useful if you would like to receive an error message when you request a specific degree of parallelism but insufficient resources exist to satisfy that request. You might use this to ensure that a process that takes days unless it gets what it wants doesn't run unless it gets what it wants. PARALLEL_MIN_SERVERS Sets the number of servers to start when the instance starts and to keep started permanently. Otherwise, you may need to start the parallel processes in response to a query. PARALLEL_THREADS_PER_CPU Determines the default degree of parallelism and contributes to the adaptive parallelism algorithms, to determine when to back off on the amount of parallel resources. RECOVERY_PARALLELISM For crash recovery, sets how many parallel threads should be used. This setting can be used to speed up the recovery from an instance crash. FAST_START_PARALLEL_ROLLBACK Sets how many processes would be available to perform a parallel rollback after the recovery takes place. This would be useful on systems where many long-running transactions are constantly processing (which would need correspondingly long rollback times in the event of an instance crash).设置了parallel_automatic_tuning=true能解决大部分的问题。在table后面直接加parallel即可。对于dml使用下面的方法
alter session enable parallel dml; is a prerequisite for parallem DML, without it - won't matter WHAT parameters you have set. for SELECT, the parallel parameters in the init.ora plus - parallel on the table, parallel hint -- would be enough.
阅读(1239) | 评论(0) | 转发(0) |