Re: parallel query

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 3 May 2005 10:11:00 -0700
Message-ID: <125140260.000086cf.041@drn.newsguy.com>

In article <1115133187.960623.274690_at_o13g2000cwo.googlegroups.com>, Eugene says...
>
>So, you are saying that I will not be able to use parallel query with
>noparallel clause at the table level. Since nobody answered for a long
>time, I've asked Tom Kyte the same question and the answer was: YES.
>http://asktom.oracle.com/pls/ask/f?p=4950:8:15898147502902143558:::::
>
>I am confused now,
>Eugene
>

ops$tkyte_at_ORA10G> drop table t;
Table dropped.

ops$tkyte_at_ORA10G> create table t ( x int ); Table created.

ops$tkyte_at_ORA10G> exec dbms_stats.set_table_stats( user, 'T', numrows=>1000000, numblks =>100000 );
PL/SQL procedure successfully completed.

ops$tkyte_at_ORA10G> show parameter parallel;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     TRUE
parallel_execution_message_size      integer     4096
parallel_instance_group              string
parallel_max_servers                 integer     20
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

ops$tkyte_at_ORA10G> @plan "select /*+ parallel(t) */ count(*) from t" ops$tkyte_at_ORA10G> delete from plan_table; 7 rows deleted.

ops$tkyte_at_ORA10G> explain plan for &1;
old 1: explain plan for &1
new 1: explain plan for select /*+ parallel(t) */ count(*) from t

Explained.

ops$tkyte_at_ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 545668572

|   0 | SELECT STATEMENT       |          |     1 |... |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |... |      |            |
|   2 |   PX COORDINATOR       |          |       |... |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |... | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |... | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|... | PCWC |            |
|   6 |       TABLE ACCESS FULL| T        |  1000K|... | PCWP |            |
---------------------------------------------------------------------------

13 rows selected.

ops$tkyte_at_ORA10G> spool off

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Tue May 03 2005 - 12:11:00 CDT