How to Use Oracle Database SQL Parallel Query to Enhance Performance.

milly 8:15 am on January 13, 2010 | Answers: 1    
Tags: database (51), oracle (136), performance (16), query (9), sql (43)

Rate Knowledge:

 Loading ...

• milly 8:15 am on January 13, 2010
  

  There are three ways to make a query to execute in parallel.

  1. Enable the table(s) for parallel execution:
  

  alter table employee parallel ;
  alter table clients parallel ;

  
  Use this method if you generally want to accessing these tables in parallel.

  2. Use a parallel hint:
  

  select /*+ parallel(c) parallel(s) */
  c.state, sum(s.amount) revenue
  from clients c, sales s
  where s.client_id = c.id
  and s.trans_date
  between to_date('01-JAN-2007','DD-MON-YYYY')
  and to_date('31-DEC-2007','DD-MON-YYYY')
  and c.nation = 'Egypt'
  group by c.state;

  This method is useful for testing purposes, or if you have a particular statement or few statements that you want to execute in parallel, but most statements run in serial.

  3. Session specific:
  Use
  alter session force parallel query;
  This is useful if your application always runs in serial except for the particular session that you want to execute in parallel.
  A batch operation in an OLTP application is an example.

  All three methods are enabled in DEFAULT parallel capabilities where Oracle chooses the DOP. By default, Oracle will spawn two parallel server processes per each core on most systems, so if you run your query on an environment with 2 CPU cores the DOP will be 4. The original query which initially took 10 minutes to complete should complete within less than 3 minutes at DOP of 4, assuming sufficient resources are available.