Mauvaise performance sur table partitionnée

[Bilna]

Bonjour,


Dans mon datawarehouse (Oracle 10g), j'ai une table de 8 millions (4Go) d'enregistrements. Pour optimiser les requêtes j'ai décidé de partitionner ma table sur une colonne date comme suit :

Code :

1
2
3
4
5
6
7
8
9
PARTITION BY RANGE(DATE) 
(
PARTITION PART1 VALUES less than (TO_DATE('01/01/2009','DD/MM/YYYY')), 
PARTITION PART2 VALUES less than (TO_DATE('01/01/2010','DD/MM/YYYY')),  
PARTITION PART3 VALUES less than (TO_DATE('01/01/2011','DD/MM/YYYY')), 
PARTITION PART4 VALUES less than (TO_DATE('01/01/2012','DD/MM/YYYY')),
PARTITION PART5 VALUES less than (TO_DATE('01/01/2013','DD/MM/YYYY')),  
PARTITION PART6 VALUES less than (MAXVALUE)
);

Dans ma table j'ai une PK qui inclus la date, un index sur {date, magasin} et un autre sur la date uniquement.
Ces index sont locaux.
Ce qui m'étonne c'est que les performances sur la table partitionnée sont moins bonnes que sur la table non partitionnée.

En effet, la requête ci dessous met 1 minute sur la table partionnée vs 10 secondes sur la table normale.
Ci-dessous les requêtes ainsi que les plans d'exécution :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT date,sum(indicator) FROM PARTITIONNED_TABLE 
WHERE date BETWEEN '01/01/2010' AND '31/01/2010'
GROUP BY date
 
 
Plan
SELECT STATEMENT  FIRST_ROWSCost: 11  Bytes: 100  Cardinality: 10  CPU Cost: 61,864  IO Cost: 11  Time: 1  				
	4 PARTITION RANGE SINGLE  Cost: 11  Bytes: 100  Cardinality: 10  CPU Cost: 61,864  IO Cost: 11  Time: 1  Partition #: 1  Partitions accessed #3			
		3 SORT GROUP BY NOSORT  Cost: 11  Bytes: 100  Cardinality: 10  CPU Cost: 61,864  IO Cost: 11  Time: 1  		
			2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE DWH.PARTITIONNED_TABLE  Cost: 11  Bytes: 100  Cardinality: 10  CPU Cost: 61,864  IO Cost: 11  Time: 1  Partition #: 3  Partitions accessed #3	
				1 INDEX RANGE SCAN INDEX DWH.INDEX_DATE Cost: 3  CPU Cost: 23,364  IO Cost: 3  Time: 1  Partition #: 4  Partitions accessed #3

Code :

1
2
3
4
5
6
7
8
9
SELECT date,sum(indicator) FROM NON_PARTITIONNED_TABLE 
WHERE date BETWEEN '01/01/2010' AND '31/01/2010'
GROUP BY date
 
Plan
SELECT STATEMENT  FIRST_ROWSCost: 10  Bytes: 108  Cardinality: 12  CPU Cost: 76,614  IO Cost: 10  Time: 1  			
	3 SORT GROUP BY NOSORT  Cost: 10  Bytes: 108  Cardinality: 12  CPU Cost: 76,614  IO Cost: 10  Time: 1  		
		2 TABLE ACCESS BY INDEX ROWID TABLE DWH.NON_PARTITIONNED_TABLE Cost: 10  Bytes: 2,030,544  Cardinality: 225,616  CPU Cost: 76,614  IO Cost: 10  Time: 1  	
			1 INDEX RANGE SCAN INDEX DWH.INDEX_DATE Cost: 3  Cardinality: 12  CPU Cost: 23,764  IO Cost: 3  Time: 1

Pour être complet j'ai lancé un calcul de statistiques sur la table avec le script suivant :

Code :

1
2
3
4
5
6
7
8
9
10
exec dbms_stats.gather_table_stats ( -
     ownname          => 'DWH', -
     tabname          => 'PARTITIONNED_TABLE', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', -
     cascade          => true, -
     granularity => 'ALL' , -
     degree           => 5 -
)
/

Merci d'avance de votre aide.

[Waldar]

Je ne vois rien de fondamentalement faux dans ce que vous avez écrit, sauf peut-être la requête :

Code :

1
2
3
4
5
  SELECT date, sum(indicator)
    FROM PARTITIONNED_TABLE 
   WHERE date BETWEEN to_date('01/01/2010', 'dd/mm/yyyy')
                  AND to_date('31/01/2010', 'dd/mm/yyyy')
GROUP BY date;

Mais votre NLS_DATE_FORMAT doit être bien positionné puisque le plan me paraît correct.

Pour savoir où disparaît le temps, il faut une trace serveur + TKPROF.

[mongolic]

Est ce que tu as regardé les aio ? Une piste pourrait être que la partition interrogée se trouve sur un disque peu performant ou sujet à contention...

[PpPool]

OPTIMIZER_MODE à FIRST-ROWS pour du DSS ?????

fait dun test avec ta table partitionnée en ajoutant ce hint /*+ ALL_ROWS */

si les résultats sont probants - modifie la valeur de ce paramétre