Re: Is there an alternative to "alter session disable parallel query"?

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Wed, 31 May 2006 22:18:16 +0200
Message-ID: <015101c684ef$5bfd3990$2308310a@MPILA9>

> 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

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

| 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

| 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

| 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

Note

dynamic sampling used for this statement

Regards
Dimitre

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 31 2006 - 15:18:16 CDT