Recently I was working on a performance problem where customer reported few of their SQL statements going for parallelism even when they are not forcing DOP via any HINT, and all of the referenced table and underlying Indexes were with degree=1
I was asked to take a look, and I immediately checked if Auto DOP was the reason forcing unwanted parallelism, but parallel_degree_policy was set to MANUAL which means the auto DOP, statement queuing and in-memory parallel execution all were disabled.
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUALNext, I thought to verify Table and Indexes general stats or details and I queried DBA_TABLES & DBA_INDEXES for Instances column, and found one of the table was set to value ‘DEFAULT’. If we have a value of DEFAULT set for INSTANCES, it will always force the query use DEFAULT degree of parallelism.
Let me explain the impact of having DEFAULT value for Instances, and how it forces SQL to spawn parallelism. For demo purpose, I am going to create a test table and an index with INSTANCES value set to DEFAULT.
[oracle@oracleontario ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 19 08:23:12 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
SQL> create table fatdba_table as select * from dba_objects;
Table created.
SQL>
SQL> select count(*) from fatdba_table;
COUNT(*)
----------
74932
SQL>
SQL> create index fatdba_table_idx on fatdba_table(OBJECT_TYPE,object_name) parallel(DEGREE 1 INSTANCES DEFAULT);
Index created.
SQL>
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 DEFAULT
SQL>
Alright the stage is set, lets run a SQL statement and force it to use that Index and see its impact on the execution.
SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name) from fatdba_table
where OBJECT_TYPE='TABLE';
Explained.
SQL> set linesize 400 pagesize 400
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1154043599
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 157 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 66 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,01 | PCWP | |
| 5 | VIEW | VW_DAG_0 | 1558 | 100K| 157 (1)| 00:00:01 | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 1558 | 68552 | 157 (1)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 10 | INDEX FAST FULL SCAN| FATDBA_TABLE_IDX | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter("OBJECT_TYPE"='TABLE')
22 rows selected.
SQL>
As expected, it forced SQL to go with parallelism. Let me set INSTANCE value of the Index to 1 and see what happens next.
SQL> alter index FATDBA_TABLE_IDX noparallel;
Index altered.
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 1
SQL> SQL>
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 1
SQL> SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name)
from fatdba_table where OBJECT_TYPE='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3184007477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 157 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | VIEW | VW_DAG_0 | 1558 | 100K| 157 (1)| 00:00:01 |
| 3 | HASH GROUP BY | | 1558 | 68552 | 157 (1)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| FATDBA_TABLE_IDX | 1561 | 68684 | 156 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_TYPE"='TABLE')
16 rows selected.
And no parallelism was used after set the value of INSTANCES to 1.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 