You Asked
I was a bit surprised to notice in my development environment a couple of months, a large number of sessions that were invoking parallel operations, especially since I made sure to set all tables in the schema to no parallel. I brushed it off as something I needed to look into when I had the time, because I couldn't find the cause at first glance.
Then two weeks ago I was tasked with determining in a straight forward testable manner for a client the best indexing strategy that should be used in an ODS or data wharehouse, as they had been informed by other consultants that because Oracle can now perform index fast full scans, range scans and skip scans of composite indexes, that all they really needed were b-tree multi column indexes on their tables. So I cracked out your book Expert Oracle Database Architecture, built a big_table, and populated it from all_object multiple times, using your standard script, and proceded to crank out autotraces on common queries. Then I noticed that even though my table was set to noparallel, I was getting parallel operations in my explain plans.
SQL> set autotrace traceonly
SQL> select owner from big_table
2 where owner =USER;
312 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28233 | 165K| 1279 (1)| 00:00:16 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE | 28233 | 165K| 1279 (1)| 00:00:16 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"=USER@!)
Statistics
----------------------------------------------------------
247 recursive calls
0 db block gets
5789 consistent gets
5692 physical reads
376 redo size
3631 bytes sent via SQL*Net to client
570 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
312 rows processed
SQL> create unique index big_tab_comp_uix on big_table
2 (owner, object_type,object_name,created,id)
3 nologging
4 parallel (degree default instances default);
Index created.
SQL> select owner, object_name
2 from big_table
3 where owner=USER;
312 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1440650364
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28233 | 854K| 246 (1)| 00:00:03 |
|* 1 | INDEX RANGE SCAN| BIG_TAB_COMP_UIX | 28233 | 854K| 246 (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"=USER@!)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
26 consistent gets
4 physical reads
0 redo size
4414 bytes sent via SQL*Net to client
570 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
312 rows processed
SQL> select owner, object_name
2 from big_table
3 where object_type ='TABLE';
13768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2757377107
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13062 | 510K| 752 (2)| 00:00:10 | | | |
| 1 | PX COORDINATOR | | | || | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 13062 | 510K| 752 (2)| 00:00:10 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 13062 | 510K| 752 (2)| 00:00:10 | Q1,00 | PCWC | |
|* 4 | INDEX FAST FULL SCAN| BIG_TAB_COMP_UIX | 13062 | 510K| 752 (2)| 00:00:10 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
45 recursive calls
3 db block gets
4155 consistent gets
3423 physical reads
636 redo size
182888 bytes sent via SQL*Net to client
10437 bytes received via SQL*Net from client
919 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13768 rows processed
SQL> alter index big_tab_comp_uix noparallel;
Index altered.
SQL> select owner, object_name
2 from big_table
3 where object_type ='TABLE';
13768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4265662311
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13062 | 510K| 752 (2)| 00:00:10 |
|* 1 | INDEX FAST FULL SCAN| BIG_TAB_COMP_UIX | 13062 | 510K| 752 (2)| 00:00:10 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
262 recursive calls
0 db block gets
4423 consistent gets
3414 physical reads
0 redo size
179036 bytes sent via SQL*Net to client
10437 bytes received via SQL*Net from client
919 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
13768 rows processed
SQL>
So I'm guessing that because Oracle treats the index as a skinny table on an Index Fast Full Scan it uses the degree of parallelzation of the index when executing the query. This is not necessarily a bad thing except for thsoe of use who hate waiting on index builds (like me) who builds all indexs to the defautl degree of parallel in my code, I now have to remember to alter the index after the fact or have multiple sessions invoking parallel operations when they shouldn't.
When are you going to be publishing an update to your other book? I can't wait to get more tips, tidbits and actual real world examples to use in my code,
Thanks
and we said...
... as they had been informed by other consultants that because Oracle can now perform index fast full scans, range scans and skip scans of composite indexes, that all they really needed were b-tree multi column indexes on their tables. ....
wow, is that just about the wrongest statement ever uttered? It ranks up there, way way up there. No, that is absolutely not even close to being remotely true. bitmap indexes play a big role, huge role.
you created your indexes with parallel - they are parallel enabled in addition to being built in parallel.
All of your indexes would be subject to parallel query therefore - you would want to "un-parallel" them if this is not what you desire.
... This is not necessarily a bad thing except for thsoe of use who hate waiting on index builds (like me) who builds all indexs to the defautl degree of parallel in my code, I now have to remember to alter the index after the fact or have multiple sessions invoking parallel operations when they shouldn't. ....
that was always true however, has been true.. It is true of TABLES as well...
ops$tkyte%ORA10GR2> create table t parallel 4 as select * from big_table.big_table;
Table created.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 (2)| 00:00:0
| 1 | SORT AGGREGATE | | 1 | |
| 2 | PX COORDINATOR | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | |
| 4 | SORT AGGREGATE | | 1 | |
| 5 | PX BLOCK ITERATOR | | 100K| 94 (2)| 00:00:0
| 6 | TABLE ACCESS FULL| T | 100K| 94 (2)| 00:00:0
------------------------------------------------------------------------
Note
if you create parallel, you've created parallel....