parallelism in oracle

1.What is Oracle Parallel Query?
              Oracle Parallel Query (formerly Oracle Parallel Query Option or PQO) allows one to break-up a given SQL statement so that its parts can run simultaneously on different processors in a multi-processor machine. Typical operations that can run in parallel are: full table scans, sorts, sub-queries, data loading etc.
Parallel Query can improve performance of certain types of operations dramatically and is commonly used in Decision Support and Data Warehousing applications.

2.What is the difference between Parallel Query and Parallel Server?
               Parallel Query allows one to break a SELECT or DML statements into multiple smaller chunks and have PQ slaves execute those smaller chunks on separate CPU's in a single box.
Oracle Real Application Clusters (RAC) allows one to take advantage of a multi-node clustered environment for availability and performance reasons. It is commonly used to access a very large database from different nodes of a cluster. More information about Oracle RAC can be obtained from the RAC FAQ.
If both RAC and OPQ are available one can split operations across multiple CPUs and multiple nodes in a cluster for even further performance improvements.

3.How does one invoke Parallel Query?
After setting the INIT.ORA parameters necessary for Parallel Query to work, do the following:
Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

ALTER TABLE table_name PARALLEL (DEGREE 8);

Put hints in SQL statements to indicate that Oracle should try to execute them in parallel:

SELECT --+ PARALLEL(table_alias, degree, nodes)
       *
  FROM table_name ...
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
  FROM table_name ...

4.How does one disable Parallel Query?

Per table, execute the following DDL commands:

ALTER TABLE table_name PARALLEL (DEGREE 1 INSTANCES 1);
ALTER TABLE table_name NOPARALLEL;
ALTER INDEX index_name PARALLEL (DEGREE 1 INSTANCES 1);
ALTER INDEX IND_XXX NOPARALLEL;

You can also remove the INIT.ORA parameters that allow Parallel Query to work.

5.What parameters can be set to control Parallel Query?

The following INIT.ORA initialization parameters can be set to control Parallel Query execution:

PARALLEL_MIN_SERVERS
Minimum number of parallel server processes

PARALLEL_MAX_SERVERS
Maximum number of parallel server processes

PARALLEL_AUTOMATIC_TUNING = TRUE
To enable intelligent defaults for parallel execution parameters (8i and above)


6.How does one monitor Parallel Query Execution?

Use the following Oracle data dictionary views to monitor parallel queries:
Prior to Oracle 8i:

SELECT * FROM sys.v$pq_sysstat;
Oracle 8i and above:
SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;

7.Can single-processor systems benefit from Parallel Query?

The more CPU's you have the bigger the benefit you can expect to get from using Parallel Query. Nevertheless, even a single-CPU system can benefit from using it. The reason for this is that the system can continue processing a second thread while waiting for I/O or network activity in another thread.
Before enabling it on a single CPU system, ensure the system is not too busy and start with a low value for PARALLEL_MAX_SERVERS (i.e. 2 or 4).

8.Can Parallel Query operations also cause performance problems?

If not carefully watched over, Parallel Query can completely saturate a machine. It is not difficult to bring some of the most powerful machines to their knees. One needs to carefully balance the number of people executing Parallel Query Operations and the degree of parallelism with the number of CPUs in the system.
Also note that executing things in parallel is not always faster. In fact, you need to test all queries to ensure that they will benefit from the increased parallelism before permanently enabling it.
Category: Frequently Asked Questions









exapmles

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;
Table created.

SQL> CREATE INDEX i ON t(owner);
Index created.

SQL> @gts t
Gather Table Statistics for table t...
PL/SQL procedure successfully completed.

Now let’s “force” the parallel query in my session, run the query and check the execution plan:
SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
Session altered.

SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';
SUM(OBJECT_ID)
--------------
     979900956

SQL> @x

---------------------------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |        |       |   186 (100)|
|   1 |  SORT AGGREGATE              |      |      1 |    12 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   6741 | 80892 |   186   (0)|
|*  3 |    INDEX RANGE SCAN          | I    |   6741 |       |    18   (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')



SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;
Session altered.

SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';
SUM(OBJECT_ID)
--------------
     979900956


SQL> @x

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | E-Rows |E-Bytes| Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |       |   128 (100)|        |      |            |
|   1 |  SORT AGGREGATE        |          |      1 |    12 |            |        |      |            |
|   2 |   PX COORDINATOR       |          |        |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |    12 |            |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |      1 |    12 |            |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   6741 | 80892 |   128   (0)|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T        |   6741 | 80892 |   128   (0)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER" LIKE 'S%')

                   So what happened above is that with “forced” parallel degree 2, the parallel full table scan plan
must have had a higher cost than the serial index range scan (186), but once I increased the parallelism “factor”
to 3, then the final cost of the parallel full table scan plan ended up being lower (128) than the best serial plan found.



However, there are some restrictions, quoting the Oracle documentation for accuracy, as shown below:

* A transaction can contain multiple parallel DML statements that modify different tables, but after a parallel DML
statement modifies a table, no subsequent serial or parallel statement (DML or query) can access the same table again in that transaction.

* Parallel DML operations cannot be done on tables with triggers. Relevant triggers must be disabled in order to parallel DML on the table.

* A transaction involved in a parallel DML operation cannot be or become a distributed transaction.

* Clustered tables are not supported.




If no errors are reported it is safe to start the redefinition using the following command.
-- Alter parallelism to desired level for large tables.

--ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified


--ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified
QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause
FORCE DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary

ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
DDL statements are executed in parallel mode if a parallel clause is specified.

ensure my all queries will be running in parallel without making any modification in sql queries.
                 or
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER TABLE ORDERS PARALLEL (DEGREE 4); for table execution
SELECT /*+ PARALLEL (ORDERS_RP, 4) */STATUS, COUNT(*) FROM ORDERS_RP GROUP BY STATUS; for select
INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */INTO TEMP_ORDERS SELECT /*+ PARALLEL (ORDERS, 4) */ * FROM

ORDERS; for insert