FTS Performance und Partitionierung

Vor fast zwei Jahren habe ich hier eine Geschichte erzählt, in der ein Full Table Scan für eine partitionierte Tabelle deutlich mehr Zeit benötigte als ein FTS auf eine nicht partitionierte Tabelle des gleichen Inhalts - und dieses Phänomen auf die Verwendung von direct path reads für den seriellen table scan verbucht. Was damals fehlte, war ein Beispiel, und das habe ich dieser Tage im OTN Forum in einem mehr oder minder geeigneten Thread untergebracht. Um hier im Blog auch mal wieder ein wenig Code zu veröffentlichen, zitiere ich mich an dieser Stelle:

-- 11.2.0.1

-- I create a simple partitioned table
-- and a corresponding non-partitioned table
-- with 1M rows
drop table tab_part;
 
create table tab_part (
    col_part number
  , padding varchar2(100)
)
partition by list (col_part)
(
    partition P00 values (0)
  , partition P01 values (1)
  , partition P02 values (2)
  , partition P03 values (3)
  , partition P04 values (4)
  , partition P05 values (5)
  , partition P06 values (6)
  , partition P07 values (7)
  , partition P08 values (8)
  , partition P09 values (9)
);
 
insert into tab_part
select mod(rownum, 10)
     , lpad('*', 100, '*')
  from dual
connect by level <= 1000000;
 
exec dbms_stats.gather_table_stats(user, 'tab_part')
 
drop table tab_nopart;
 
create table tab_nopart
as
select *
  from tab_part;
 
exec dbms_stats.gather_table_stats(user, 'tab_nopart')
 
-- my _small_table_threshold is 1777 and the partitions
-- have a size of ca. 1600 blocks while the non-partitioned table
-- contains 15360 blocks
 
-- I have to flush the buffer cache since
-- the direct path access is only used
-- if there are few blocks already in the cache
alter system flush buffer_cache;
 
-- the execution plans are not really exciting
-- select count(*) from tab_part;
----------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |  8089   (0)| 00:00:41 |       |       |
|   1 |  SORT AGGREGATE     |          |     1 |            |          |       |       |
|   2 |   PARTITION LIST ALL|          |  1000K|  8089   (0)| 00:00:41 |     1 |    10 |
|   3 |    TABLE ACCESS FULL| TAB_PART |  1000K|  8089   (0)| 00:00:41 |     1 |    10 |
----------------------------------------------------------------------------------------

-- select count(*) from tab_nopart;
-------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |  7659   (0)| 00:00:39 |
|   1 |  SORT AGGREGATE    |            |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TAB_NOPART |  1000K|  7659   (0)| 00:00:39 |
-------------------------------------------------------------------------

Auf meinem PC benötigt der FTS der partitionierten Tabelle ca. 3 sec., während der Scan der nicht partitionierten Tabelle nur 1 sec. benötigt, was in v$sesstat (unter anderem) mit folgenden Werten erläutert wird:

-- non partitioned table
NAME                                               DIFF
-------------------------------------------- ----------
table scan rows gotten                          1000000
file io wait time                                 15313
session logical reads                             15156
physical reads                                    15153
consistent gets direct                            15152
physical reads direct                             15152
DB time                                              95
 
-- partitioned table
NAME                                               DIFF
-------------------------------------------- ----------
file io wait time                               2746493
table scan rows gotten                          1000000
session logical reads                             15558
physical reads                                    15518
physical reads cache prefetch                     15202
DB time                                             295

Demnach kann die Segmentgröße von Partitionen einen erstaunlichen Einfluss auf die Laufzeiten von Full Table Scans haben, was aus dem Plan nicht unmittelbar zu ersehen ist.

Nachtrag 04.01.2015: da ich vor kurzem noch mal ein wenig mit der _small_table_threshold experimientiert habe (und dabei mal wieder feststellen musste, dass mein Gedächtnis nicht mehr viel von der Wiederverwendung von Informationen hält), hier noch der ergänzende Hinweis - den man auch bei Tanel Poder findet -, dass eine Änderung des Parameters keine Cursor-Invalidierung mit sich bringt, da er nicht Teil des optimizer environments ist.