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/，如需轉載，請註明出處，否則將追究法律責任。