 |
|
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.
