> Oracle 10.2.0.1/Sun Solaris 8
>
>
We have tried a number of things like the no_parallel hints, parallel
>
degree 0, outlines, rule hints, but they all end up using parallel query
>
for a particular table (with parallel specified in the DDL). As we are
>
trying to tune one specific query and not all queries against this
>
table, we are not yet interested in changing the system-wide parallel
>
parameters, nor the DDL. Are there any other tricks we can look at other
>
than explicitly altering the session?
May be your syntax is wrong.
$ uname -a
SunOS xxx 5.8
Generic_117350-02 sun4u sparc SUNW,Ultra-60
$ sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production
on Wed May 31 22:13:29 2006
Copyright (c) 1982, 2005, Oracle. All
rights reserved.
Connected
to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t parallel 4 as select
* from all_objects;
Table created.
SQL> sho parameter parallel
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
fast_start_parallel_rollback
string
LOW
parallel_adaptive_multi_user
boolean
TRUE
parallel_automatic_tuning
boolean
FALSE
parallel_execution_message_size
integer
2152
parallel_instance_group
string
parallel_max_servers
integer
40
parallel_min_percent
integer
0
parallel_min_servers
integer
0
parallel_server
boolean
FALSE
parallel_server_instances
integer
1
parallel_threads_per_cpu
integer
2
recovery_parallelism
integer 0
SQL> set autot traceo exp
SQL>
select * from t;
Execution
Plan
----------------------------------------------------------
Plan hash
value: 2865594568
--------------------------------------------------------------------------------------------------------------
|
Id |
Operation |
Name | Rows | Bytes | Cost (%CPU)|
Time | TQ |IN-OUT| PQ Distrib
|
--------------------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 46006 |
5750K| 46 (3)| 00:00:01
| |
|
|
| 1 | PX COORDINATOR
|
| |
|
|
| |
|
|
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | P->S |
QC (RAND) |
| 3 | PX BLOCK ITERATOR
| | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | PCWC
|
|
| 4 | TABLE ACCESS FULL|
T | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | PCWP
|
|
--------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement
SQL> select /*+ NO_PARALLEL(t) */ * from
t;
Execution
Plan
----------------------------------------------------------
Plan hash
value: 2153619298
--------------------------------------------------------------------------
|
Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 46006 |
5750K| 166 (3)| 00:00:02 |
| 1 |
TABLE ACCESS FULL| T | 46006 | 5750K|
166 (3)| 00:00:02
|
--------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement
SQL> select /*+ NOPARALLEL(t) */
* from t;
Execution
Plan
----------------------------------------------------------
Plan hash
value: 2153619298
--------------------------------------------------------------------------
|
Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 46006 |
5750K| 166 (3)| 00:00:02 |
| 1 |
TABLE ACCESS FULL| T | 46006 | 5750K|
166 (3)| 00:00:02
|
--------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement
SQL> select /*+ NO_PARALLEL */ *
from t;
Execution
Plan
----------------------------------------------------------
Plan hash
value: 2865594568
--------------------------------------------------------------------------------------------------------------
|
Id |
Operation |
Name | Rows | Bytes | Cost (%CPU)|
Time | TQ |IN-OUT| PQ Distrib
|
--------------------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 46006 |
5750K| 46 (3)| 00:00:01
| |
|
|
| 1 | PX COORDINATOR
|
| |
|
|
| |
|
|
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | P->S |
QC (RAND) |
| 3 | PX BLOCK ITERATOR
| | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | PCWC
|
|
| 4 | TABLE ACCESS FULL|
T | 46006 |
5750K| 46 (3)| 00:00:01 | Q1,00 | PCWP
|
|
--------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement
Regards
Dimitre
