Mittwoch, Juni 26, 2013

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:

-- 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.

Kommentare:

  1. I've spend a great deal of time on studying direct path reads.

    What you are describing could be influenced by how direct path reads work, and ASSM work. Every partition starts of with small extents. This means IO is slightly less efficient then once the extents are sized up to maxIO size.

    Probably (haven't investigated) the number of asynchronous slots start off with 2 (the default starting number) for every partition, and start to scale up (if throughput and cpu power permits) after that.

    With a single table, you only go once through the small extents and then are able to do IO's which are close to max IO size (1mb on linux), and the number of concurrent IO's can grow and stay up it's number.

    AntwortenLöschen
    Antworten
    1. Hi Frits,

      thank you for your comment and the information on the (possibility of) changing IO patterns. I made some simple tests with MSSM and uniform sized extents and in this case the difference in performance seems to be smaller than in my initial test (< 2.5 sec for the FTS on the partitioned table - but my example is a little bit small and the timing may be not the strongest indicator for different behaviour). I would have to take a closer look at the problem - and perhaps reread some of the articles in your blog - before I could say something more definite on the situation in this private test system (Windows 7 with 11.2.0.1).

      Regards

      Martin

      Löschen