BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Using Parallel Query Hints

Oracle Tips by Burleson Consulting

Invoking the parallel query with hints has several prerequisites. The most important prerequisite is that the execution plan for the query specify a full-table scan. If the output of the execution plan does not indicate a full-table scan, the query can be forced to ignore the index by using the FULL hint.

The number of processors dedicated to servicing a SQL request is ultimately determined by Oracle query manager, but the programmer can specify the upper limit on the number of simultaneous processes.

When using the cost-based optimizer, the PARALLEL hint can be embedded into the SQL to specify the number of parallel query slave processes. For instance, in the query that follows we invoke 35 parallel query slaves because we are on a 36-CPU database server:

select /*+ FULL(employee_table) PARALLEL(employee_table, 35) */
   employee_name
from
   employee_table
where
   emp_type = 'SALARIED';

If you are using an SMP or MPP database server with many CPUs, you can issue a parallel request and leave it up to each Oracle instance to use its default degree of parallelism. For example:

select /*+ FULL(employee_table)
           PARALLEL(employee_table, DEFAULT, DEFAULT) */
   employee_name
from
   employee_table
where
   emp_type = 'SALARIED';

Oracle also provides the parallel_automatic_tuning init.ora parameter to assist in setting the best degree of parallelism. When setting parallel_automatic_tuning, you only need to specify parallelism for a table, and Oracle will dynamically change the parallel_adaptive_multi_user parameter to override the execution plan in favor of maintaining an acceptable overall load on the database. You should also note that setting parallel_automatic_tuning will cause extra storage in the large pool because Oracle will allocate message buffers from the large pool instead of the shared pool.

Parallel Queries and Table Joins

When joining multiple tables, you can specify that each table retrieve its rows using a full-table scan. When tuning SQL, if you make the determination that a full-table scan is appropriate, you must then determine how the tables can be joined using parallel query, and you can also experiment with different join techniques such as nested loop, hash, and sort merge joins.

TIP: When evaluating the performance of SQL parallel queries, there is no substitute for timing the query. There are so many factors involved in overall response time that it is easier to time the execution of the query with different parallel join plans and choose the one with the fastest response time.

Within the Oracle plan_table, we see that Oracle keeps the parallelism in a column called other_tag. The other_tag column will tell you the type of parallel operation that is being performed within your query.

For parallel queries, it is important to display the contents of the other_tag in the execution. Some SQL professionals keep a special version of plan.sql called pq_plan.sql for displaying details about parallel execution.

pq_plan.sql

set echo off
set long 2000
set pagesize 10000

column query       heading "Query Plan" format a80
column other       heading "PQO/Remote Query" format a60 word_wrap
column x           heading " " format a18

select distinct
   object_node "TQs / Remote DBs"
from
   plan_table
where
   object_node is not null
order by
   object_node;

select lpad(' ',2*(level-1))||operation||' '||options||' '
    ||object_name||' '
    ||decode(optimizer,'','','['||optimizer||'] ')
    ||decode(other_tag,'',decode(object_node,'','','['||object_node||']')
     ,'['||other_tag||' -> '||object_node||']')
    ||decode(id,0,'Cost = '||position) query
   ,null  x
   ,other
from
   plan_table
start with id = 0
connect by prior id = parent_id;

Here is a sample query with parallel full-table scans. Letís examine the different display formats for the execution plans for plan.sql and pq_plan.sql.

select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the standard output from plan.sql:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
OTHER_TAG
------------------------------------------------------------------------------
SELECT STATEMENT
                                                                     5
  MERGE JOIN
                                                                     1
PARALLEL_TO_SERIAL
    SORT
JOIN                                                                 1
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           EMP                                   1
PARALLEL_TO_PARALLEL
    SORT
JOIN                                                                 2
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           BONUS                                 1
PARALLEL_TO_PARALLEL

Here is the same execution plan displayed with pq_plan.sql. Letís take a look at this output and compare the display formats.

TQs / Remote DBs
----------------------------------------------------------------------
:Q36000
:Q36001
:Q36002


Query Plan
----------------------------------------------------------------------
                   PQO/Remote Query
------------------ ---------------------------------------------------
SELECT STATEMENT   [FIRST_ROWS] Cost = 5


  MERGE JOIN   [PARALLEL_TO_SERIAL -> :Q36002]
                   SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) */
                   A1.C0,A1.C1,A2.C1 FROM :Q36000 A1,:Q36001 A2 WHERE
                   A1.C0=A2.C0

    SORT JOIN  [PARALLEL_COMBINED_WITH_PARENT -> :Q36002]


      TABLE ACCESS FULL EMP [PARALLEL_TO_PARALLEL -> :Q36000]
                   SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ENAME"
                   C0,A1."HIREDATE" C1 FROM "EMP" A1
                   WHERE ROWID BETWEEN :B1
                   AND :B2

    SORT JOIN  [PARALLEL_COMBINED_WITH_PARENT -> :Q36002]


      TABLE ACCESS FULL BONUS [PARALLEL_TO_PARALLEL -> :Q36001]
                   SELECT /*+ NO_EXPAND ROWID(A1) */
                   A1."ENAME" C0,A1."COMM" C1
                   FROM "BONUS" A1 WHERE ROWID BETWEEN :B1 AND :B2

Here you see more detail about the internals of the execution plan, including details about the parallel query execution modes (other_tag in plan_table) and details about the tables that participate in the query.

In practice, most SQL tuning professionals keep two copies of scripts to display execution plan information. Letís take a closer look at the other_tag column in the plan_table and investigate the possible values and their meanings.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter