Skip navigation

The other day, a SELECT COUNT(*) of mine did a full-table scan when I knew it could use an index.  This is a big (2.5M rows), partitioned table.

BLOCKS TABLE_NAME NUM_ROWS AVG_ROW_LEN

---------- ------------------------------ ---------- ---------------------------------------

     49932 BIGTABLE                    2484741                                     13

A closer look revealed that the SELECT went parallel:

4B1.bmp

Indeed, the table had a parallel degree of 12.  Oracle figured a parallelized full-table scan would be more efficient than using the PK index.

Indeed, I altered the table to a parallel degree of 1, and now Oracle chose the PK index:

------------------------------------------------------------------| Id  | Operation              | Name      | Rows | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |           | 1 |  2035   (2)| 00:00:25 |       | |

|   1 | SORT AGGREGATE        |           | 1 |            |          |       | |

|   2 | PARTITION LIST ALL   |           | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

|   3 |    INDEX FAST FULL SCAN| MYPKINDEX |  2484K| 2035   (2)| 00:00:25 |     1 | 35 |

 

At some parallel degree, Oracle figures an FTS (Full-Table Scan, which is an operation that can be parallelized and take advantage of db_file_multiblock_read_count) is more efficient than using the PK index.  Let’s see that in action.

 

Degree 11:

----------------------------------------------------------------------------------------------------

| Id  | Operation              | Name           | Rows | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                | 1 |  1369   (1)| 00:00:17 |       | |        |      |            |

|   1 | SORT AGGREGATE        |                |     1 |            |          |       | |        |      |            |

|   2 | PX COORDINATOR       |                | |            |          |       | |        |      |            |

|   3 | PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       | |  Q1,00 | P->S | QC (RAND)  |

|   4 | SORT AGGREGATE     |                |     1 |            |          |       | |  Q1,00 | PCWP |            |

|   5 | PX BLOCK ITERATOR | |  2484K|  1369 (1)| 00:00:17 |     1 |    35 | Q1,00 | PCWC |            |

|   6 | TABLE ACCESS FULL| BIGTABLE | 2484K|  1369   (1)| 00:00:17 |     1 | 35 |  Q1,00 | PCWP |            |

 

Degree 10: same as above (FTS)

Degree 9: same as above (FTS)

Degree 8: same as above (FTS)

Degree 7:

--------------------------------------------------------------------------------------------

| Id  | Operation              | Name      | Rows | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |           | 1 |  2035   (2)| 00:00:25 |       | |

|   1 | SORT AGGREGATE        |           | 1 |            |          |       | |

|   2 | PARTITION LIST ALL   |           | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

|   3 | INDEX FAST FULL SCAN| MYPKINDEX | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

 

Degree 6: same as above (PK)

Degree 2: same as above (PK)

So, on that 11.2.0.3 database (blocksize 8192), with db_file_multiblock_read_count at 45, Oracle considers that at degree 7, parallelism will not be as efficient as an index fast full scan.  While at degree 8, it considers the full table scan (parallelized of course) the most efficient.

 

I tried the same thing on a 12.1.0.2 database (blocksize 8192).  This time the BIGTABLE is 10M-record strong, and db_file_multiblock_read_count is at 66.

BLOCKS    TABLE_NAME    NUM_ROWS AVG_ROW_LEN
56107    BIGTABLE 10107273    35

 

An added boon of version 12c is that EXPLAIN PLAN mentions the degree of parallelism:

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 1 |  5643   (1)| 00:00:01 |        | |            |
|   1 | SORT AGGREGATE        |          |     1 |            |          |        | |            |
|   2 | PX COORDINATOR       |          |       |            |          |        | |            |
|   3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |            |          | Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     | |     1 |            |          | Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10M|  5643   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| BIGTABLE    | 10M|  5643   (1)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 3 because of table property

Here are a few screenshots of my various result tabs in SQLDeveloper, showing the results of my various runs:

4B2.bmp

”d” stands for degree of parallelism

“c” stands for cost

“PK” means that the execution plan used the Primary Key index

“FTS” means that the execution plan used a parallelized full table scan

 

Not only does Oracle, at some point, switch from an index fast full scan to a full table scan, but the cost also decreases as the degree of parallelism increases.  Here the watershed is at degree 3 (full table scan), while with degree 2, Oracle chooses to use the index.

4B3.bmp

Oh, in case you were wondering how I carried out my various tests, here is my code:

set autotrace off

alter table BIGTABLE PARALLEL 2 ; -- change to the desired degree of parallelism here

select degree,table_name,owner from dba_tables where table_name like 'BIGTABLE%' ;

set autotrace on explain only

set timing on

select count(*) from BIGTABLE ;

set timing off

 

Then, using SQLDeveloper’s pin button, I get my results into different tabs, which I can rename with the relevant information:

4B4.bmp