Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

No Hint, No Degree, No Auto-DOP, Why my query is going for the parallelism ?

Posted by FatDBA on December 16, 2022

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      MANUAL

Next, 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: