Parallel Query and Instances in Oracle

Posted in Oracle by Ankit Goel on April 24, 2010

Each and every day of your life you learn something. I am sharing something which I learnt a few days back.

Some days back I was working on a query on a newly migrated database (from 8i to 10g).A end user was having a problem with a report and said that every time he ran the report ,it would run for hours and after waiting for hours, in frustration he would kill his session.

After working on the query i found that a number of parallel slave processes were created for the query. And to my surprise 56 parallel slaves were created for that single sql.The parallel query coordinator actually waits for the
execution servers running in parallel to finish and thus the query would wait for all the 56 parallel slaves to complete execution.

but why was the query creating parallel slaves ??? The degree for the tables and indexes used in the query was “1”.Then i came to know a new thing.One of the indexes which was being used by the query had a degree of “1” but the instances value was set to “default”.

I have tried to show the effect of instances value through a test case.I created a table my_objects by selecting rows from the all_objects view and an index on the column object_id.


SQL>  select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table my_objects as select * from all_objects;

Table created.

SQL> create index indx_my_objects on my_objects(object_id);

Index created.

SQL>
SQL> select index_name,degree,instances from all_indexes where index_name='INDX_MY_OBJECTS';

INDEX_NAME                     DEGREE                                   INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
INDX_MY_OBJECTS                1                                        1

SQL> select count(*) from my_objects where object_id>12;

 COUNT(*)
----------
 49910

Execution Plan
----------------------------------------------------------
Plan hash value: 1996840010

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |    13 |    30   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                 |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| INDX_MY_OBJECTS | 48378 |   614K|    30   (7)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("OBJECT_ID">12)

SQL> alter index  indx_my_objects parallel(degree 1 instances default);

Index altered.

SQL>  select index_name,degree,instances from all_indexes where index_name='INDX_MY_OBJECTS';

INDEX_NAME                     DEGREE                                   INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
INDX_MY_OBJECTS                1                                        DEFAULT

SQL> select count(*) from my_objects where object_id>12;

 COUNT(*)
----------
 49910

Execution Plan
----------------------------------------------------------
Plan hash value: 318682625

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    13 |    30   (7)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |                 |     1 |    13 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |                 |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000        |     1 |    13 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                 |     1 |    13 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |                 | 48378 |   614K|    30   (7)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       INDEX FAST FULL SCAN| INDX_MY_OBJECTS | 48378 |   614K|    30   (7)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter("OBJECT_ID">12)

So after changing the value of instances of the index to default the query started using parallel slaves.So whether the query need to create parallel slaves or not is also determined by the value of instances of an index.

So after the instances value of the index was set to 1 the query stopped creating parallel salves and ran in pretty quick time 🙂

Ankit Goel's Blog