Re: Is there an alternative to "alter session disable parallel query"?
- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
- To: <sac@xxxxxxxxxxxxx>
- Date: Wed, 31 May 2006 22:18:16 +0200
> 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
Other related posts:
