Turn Off Oracle Index Parallel Query

Parallel query can be useful for reducing elapsed run time of Oracle index scans. In some circumstances, parallel query can cause longer elapsed run times.

As the schema owner, determine the index parallel query settings:

SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

DEGREE INSTANCES COUNT(*)
--------- ---------------------------------------- ----------
0 0 4
1 1 1303
6 1 8
10 1 3
DEFAULT 1 38

Run SQL to create a SQL file that will set noparallel:

$ cat bill_ix_alter_nopq_gen.sql
set pages 0 lines 100 feedback off
spool bill_ix_alter_nopq.sql
prompt spool bill_ix_alter_nopq

select 'alter index ' || index_name || ' noparallel;' from user_indexes where degree > '1'
order by 1
/
spool off

Run the generated SQL file (bill_ix_alter_nopq.sql).

Verify the new settings:

SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

DEGREE INSTANCES COUNT(*)
--------- ---------------------------------------- ----------
0 0 4
1 1 1352