Parallel Query Tuning(并行化查询优化)

以下情况可用到并行查询优化：
full table scans,joining of large tables,large range-scans of indexes,insertion of massive amounts of data to a data warehouse,and so on.

The number of parallel query slaves for a single task is called the degree of parallelism.Normally, PQ would attempt to use parallel query slave processes equal to the value of degree of parallelism.But if a sorting operation is involved,the number of processes required is doubled.

It is very important to note here that PQ will utilize twice the number of processes defined in the degree if the SQL statement requires a sort operation.

there are the following ways to set this degree of parallelism:
1.At the table or index level
2.At the SQL statement level with a parallel hint
3.As default based on the number of CPUs or the number of storage devices that Oracle believes that you are using

set the degree of parallelism when creating a new table.

SQL> create table MYTABLE
(ColA number(2),
ColB number(2)
)
parallel (degree 4);

Table created.

You can see the value of the degree of parallelism for the table in the
USER_TABLES,ALL_TABLES,and DBA_TABLES views in a column titled Degree:

SQL> select Degree
from USER_TABLES
where TABLE_NAME='MYTABLE';

DEGREE
--------------------
         4

You can change the degree of parallelism for a table using the alter table command.

SQL> alter table MYTABLE parallel(degree 6);

Table altered.

SQL> select Degree
from USER_TABLES
where TABLE_NAME='MYTABLE'; 

DEGREE
--------------------
         6

set the degree of parallelism on an index to 4 while creating the index.Depending on your Oracle version,the data dictionary views USER_INDEXES,DBA_INDEXES,and ALL_INDEXES will have a column titled Degree,which will contain the value of the degree of parallelism for the index.

SQL> create index MYINDEX
on MYTABLE (ColA)
storage (initial 1M next 1M)
parallel (degree 4);//创建过程中initial需要4M的空间，在创建过程中每个parallel query slaves都会使用自己独立的临时表空间。

Index created.

SQL> select Degree
from USER_INDEXES
where Index_Name='MYINDEX';

DEGREE
----------------------------------------
4

You can also use the alter index command to change the degree of parallelism for an index, as shown here:

SQL> alter index MYINDEX parallel(degree 6);

Index altered.

SQL>  select Degree
from USER_INDEXES
where Index_Name='MYINDEX';

DEGREE
----------------------------------------
6

set the degree of parallelism on a table to 6,via a PARALLEL hint in a SQL statement.
select /*+PARALLEL (CM , 6)*/ Customer_Id Custid, Last_Contract_Yr Lcy
from CUSTOMER_MASTER CM
order by Customer_Id;

Please note that since the previous example users an "order by" clause to sort the result set, Oracle may attempt to allocate at least twelve parallel query slaves to this operation.

sets the degree of parallelism on a table to a value of default.

SQL> select degree from user_tables where table_name='TEST';

DEGREE
--------------------
         1

SQL> alter table test parallel;

Table altered.

SQL> select degree from user_tables where table_name='TEST';

SQL>
SQL>
SQL> select degree from user_tables where table_name='TEST';

DEGREE
--------------------
   DEFAULT

In Oracle 8.1.6,a new initialization parameter,PARALLEL_THREADS_PER_CPU,defines the default degree of parallelism.Its default value is platform-specific and is adequate in most cases.
It is very easy to determine this if you look at V$SESSION_WAIT for wait events that relate to parallel query slaves.

优先级：
1.SQL statement with the PARALLEL hint
2.Parallel degree set at the table or index
3.Default degree of parallelism

取消并行度：
alter table MYTABLE noparallel;

select /*+NOPARALLEL*/ count(*)
from CUSTOMER_MASTER;

--未完待续--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-662069/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-662069/