How to stop using parallel slaves?

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Mon, 10 Oct 2005 23:03:52 -0400
Message-ID: <f09dd6280510102003j4e4da040l33ed11dd7941859e@mail.gmail.com>

Hi:

I don't know how optimizer decides to use PARALLEL SLAVES to run such a small query.

One of my non-resource intensive query(look at the statistics) is started using parallel slave unnecessarily even though 1) any of the tables and indexes involved in the query does not have parallel degree
2) no parallel hint in SQL statement

I am thinking it may be because of parallel_automatic_tuning is set to TRUE. Since it is static parameter I cannot do anything now to stop using parallel slaves for this small query.

The reasons why I don't want this query to use parallel slaves are

This query runs very often & it consumes all possible 50 parallel slaves
(max limit).
Because of the above, our replication Push Job fails which requires parallel slaves.

ORA-12012: error on auto execute of job 61
ORA-23386: replication parallel push cannot create slave processes
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1716
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1804
ORA-06512: at line 1

I cannot modify the query to put NO PARALLEL hint either.

Mu question:

Is there any work around to stop using parallel slaves for this small query?

SQL> show parameter parallel

NAME TYPE VALUE

------------------------------------ -----------
------------------------------

fast_start_parallel_rollback string LOW
log_parallelism integer 1

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 50
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean TRUE
parallel_server_instances integer 3
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

SQL> set autotrace on
SQL> get res.sql
1* SELECT DISTINCT RES_REGION.REGION FROM RES_ISSUE RESI,RES_REGION,(SELECT RES_PK FROM RES_ISSUE MINUS (SELECT RESI.RES_PK FROM RES_ISSUE RESI,RES_RESTRICTED_CNTRY,CNTRY WHERE
RESI.RES_PK=RES_RESTRICTED_CNTRY.RES_PK AND RES_RESTRICTED_CNTRY.CNTRY_PK= CNTRY.CNTRY_PK AND CNTRY.CNTRY_CODE='GB')) RES_FILTERED_LIST WHERE

RESI.TIER<= 100 AND
RESI.DELETE_DOC=0 AND RES_FILTERED_LIST.RES_PK=RESI.RES_PK AND
RESI.RES_PK=RES_REGION.RES_PK AND RESI.FILE_FMT='PDF' ORDER BY

RES_REGION.REGION ASC
SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=341 Card=5 Bytes=215 )

 1 0 SORT (UNIQUE) (Cost=273 Card=5 Bytes=215)
 2 1 HASH JOIN (Cost=145 Card=33618 Bytes=1445574)
 3 2 HASH JOIN (Cost=126 Card=18862 Bytes=546998)

4 3 TABLE ACCESS (FULL) OF 'RES_ISSUE' (Cost=57 Card=18862 Bytes=301792) 5 3 VIEW (Cost=70 Card=37884 Bytes=492492) 6 5 MINUS
7 6 SORT (UNIQUE)
8 7 INDEX (FAST FULL SCAN) OF 'P_RES_ISSUE' (UNIQUE) (Cost=6 Card=37884 Bytes=227304)
9 6 SORT (UNIQUE)
10 9 NESTED LOOPS* (Cost=17 Card=439 Bytes=10097) :Q55028000 11 10 NESTED LOOPS* (Cost=17 Card=439 Bytes=7463) :Q55028000 12 11 INDEX* (FAST FULL SCAN) OF 'RES_RESTRICTED :Q550280_CNTRY_CHK1'
(NON-UNIQUE) (Cost=17 Card=107992 Bytes=107992000)
13 11 INDEX* (RANGE SCAN) OF 'CNTRY_CHK1' (NON-U :Q550280NIQUE) 00 14 10 INDEX* (UNIQUE SCAN) OF 'P_RES_ISSUE' (UNIQU :Q550280 E) 00 15 2 INDEX (FAST FULL SCAN) OF 'RES_REGION_CHK1' (NON-UNIQUE) (Cost=19 Card=49824 Bytes=697536)
10 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE X(A2 "P_RES_ISSUE") */ A1.C0,A1.C1,A

 11 PARALLEL_COMBINED_WITH_PARENT
 12 PARALLEL_COMBINED_WITH_PARENT
 13 PARALLEL_COMBINED_WITH_PARENT
 14 PARALLEL_COMBINED_WITH_PARENT

Statistics

0 recursive calls
0 db block gets
1769 consistent gets
0 physical reads
0 redo size
608 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed

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

Received on Mon Oct 10 2005 - 22:06:41 CDT