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

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