How to stop using parallel slaves?

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

How to stop using parallel slaves?

How to stop using parallel slaves?

2005-10-10 - By Sami Seerangan

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

1) This query runs very often & it consumes all possible 50 parallel slaves
(max limit).

2) Because of the above, our replication Push Job fails which requires
parallel slaves.

ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job 61
ORA-23386 (See ORA-23386.ora-code.com): replication parallel push cannot create slave processes
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_DEFER_SYS", line 1716
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_DEFER_SYS", line 1804
ORA-06512 (See ORA-06512.ora-code.com): 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

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 
 
1) This query runs very often & it consumes all possible 50 parallel slaves
(max limit). 
 
2) Because of the above, our replication Push Job fails which requires parallel
slaves. 
 
ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job 61 
ORA-23386 (See ORA-23386.ora-code.com): replication parallel push cannot create slave processes 
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_DEFER_SYS", line 1716 
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_DEFER_SYS", line 1804 
ORA-06512 (See ORA-06512.ora-code.com): 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&nbsp
;           
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 
    &nbsp
;     ) 
  
   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            &nbsp
;   
NESTED LOOPS* (Cost=17 Card=439 Bytes=7463)  :Q55028000 
  12  
11            &nbsp
;     
INDEX* (FAST FULL SCAN) OF 'RES_RESTRICTED :Q550280_CNTRY_CHK1'
(NON-UNIQUE) (Cost=17 Card=107992 Bytes=107992000) 
  13  
11            &nbsp
;     
INDEX* (RANGE SCAN) OF 'CNTRY_CHK1' (NON-U :Q550280NIQUE)  00 
  14  
10            &nbsp
;   
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 
 -- ---- ---- ---- -----
-- ---- ---- ---- ---- ---- ---- -- 
    &nbsp
;      0  recursive calls 
    &nbsp
;      0  db block gets 
    &nbsp
;   1769  consistent gets 
    &nbsp
;      0  physical reads 
    &nbsp
;      0  redo size 
    &nbsp
;    608  bytes sent via SQL*Net to client 
    &nbsp
;    651  bytes received via SQL*Net from client 
    &nbsp
;      2  SQL*Net roundtrips to/from client
 
    &nbsp
;      4  sorts (memory) 
    &nbsp
;      0  sorts (disk) 
    &nbsp
;      5  rows processed