Toujours forcer la lecture des blocs depuis le disque dur

**Ikebukuro** · 31/01/2020, 18h51

Salut tout le monde,

Parfois on veut que Oracle lise systématiquement les blocs d'une table non pas depuis le Database Buffer Cache, s'ils sont présents, mais depuis le disque dur.

Quel est l'intérêt? Tester les débits en lecture du disque dur! Cela arrive quand on a tuné un SELECT dans la base, qu'on estime ne plus rien pouvoir faire en tant que DBA et qu'il faut maintenant tuner le hardware, notamment les disques avec l'utilitaire Linux Hdparm.
Le problème est que, suite au premier SELECT, les blocs de la table, lus depuis le disque dur, sont mis en mémoire dans la SGA. Tous les prochains SELECTs liront donc ces blocs depuis la mémoire donc les modifications système apportées sur le tuning des disques durs ne pourront pas être testées.

Comment faire?
Une solution brutale est de vider TOUT le Database Buffer Cache avec la commande ALTER SYSTEM FLUSH buffer_cache; mais cela va ralentir les SELECTs sur les autres tables puisque pour TOUS les users il va falloir relire les données depuis le disque. Et puis c'est trop brutal! Quand je tune un ordre, mon objectif est de faire de la micro-chirurgie, mes tests doivent impacter au strict minimum voir pas du tout les autres users.

Donc comment faire pour lire TOUJOURS les données depuis le disque dur sans avoir à flusher le buffer cache?
La réponse serait de modifier le paramètre caché _serial_direct_read en lui mettant la valeur "always" et faire du parallélisme.

Sauf que, avec mes tests, c'est plus compliqué que prévu et c'est là que j'ai besoin de vos lumières

Voici mes tests.

On utilise Autotrace pour voir les lectures physiques et logiques.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SQL> select * from zztest;
20000 rows selected.
 
SQL> set autotrace on

On voit bien que la deuxième fois qu'il n'y a aucun accès au disque dur avec "0 physical reads" puisque j'ai fais un SELECT précédemment.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select * from zztest;
----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   | 20000 |  2441K|   101   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ZZTEST | 20000 |  2441K|   101   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Si on flush le buffer cache, on revoit les lectures sur le disque dur avec "354 physical reads". Problème, cela arrive pour TOUTES les tables, donc on ralentit les autres SELECTs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> alter system flush buffer_cache;
System altered.
 
SQL> select * from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	355  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Si je relance, que des lectures en SGA : OK.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select * from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Pour forcer une lecture sur disque dur sans flusher le buffer cache, on peut utiliser le hint PARRALEL car celui-ci force ce qu'on appelle le "direct path read".
Ah non, KO... Visiblement il ne faut pas croire TOUT ce qu'on lit sur le net :-)
Vous noterez que sur le plan d'exécution il n'y a pas de parallélisme car mon PC de test a un seul CPU et un seul core... mais cela n'est pas important puisque le direct read path sera bien pris en compte plus loin (sauf erreur de ma part)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select /*+ PARRALEL */ * from zztest;
----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   | 20000 |  2441K|   101   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ZZTEST | 20000 |  2441K|   101   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
	  1  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

En cherchant mieux sur le net, voilà ce que j'ai touvé : il faut AUSSI positionner le paramètre caché _serial_direct_read à "always" (même si celui-ci semble être utilisé surtout par Exadata.
Attention, c'est un paramètre caché donc il faut contacter le support avant de modifier celui-ci en production. MAIS comme la modification est là pour du tuning, vous ne devriez pas le modifier en prod.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SQL> ALTER SESSION SET "_serial_direct_read" = always;
Session altered.

Encore KO...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> select * from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed
 
SQL> select /*+ PARRALEL */ * from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

A noter que lors de mes tests, je constate deux choses :
- la lecture sur le disque dur ne se fait pas immédiatement, il y a un délai entre l'ALTER SESSION et la prise en compte du changement de paramètre... bizarre, cela rend les tests décrits ici plus difficilement reproductibles
- si le select /*+ PARRALEL */ * from zztest; ne provoque pas une lecture physique, essayez select * /*+ PARRALEL */ from zztest; en changeant de place le caractère *, même si cela va à l'encontre de la syntaxe des hints... oui, c'est compliqué et pas hyper fiable comme méthode... j'y comprends plus rien mais je n'abandonne pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select * /*+ PARRALEL */ from zztest;
Statistics
----------------------------------------------------------
	  1  recursive calls
	  3  db block gets
       1665  consistent gets
	353  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Si je relance le SELECT sans parallélisme : aïe, on lit les blocs depuis la SGA!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select * from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  4  db block gets
       1668  consistent gets
	  0  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Avec le hint PARALLEL c'est OK : visiblement il faut les deux conditions pour forcer la lecture sur disque dur : le paramètre "_serial_direct_read" à always et faire du parallélisme.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select * /*+ PARRALEL */ from zztest;
Statistics
----------------------------------------------------------
	  0  recursive calls
	  3  db block gets
       1665  consistent gets
	353  physical reads
	  0  redo size
    2958855  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Bon, voilà... si vous pouvez m'éclaircir sur ce qui se passe ou me proposer une autre solution, je suis preneur.

Bonne journée à tous et merci pour vos réponses

**vanagreg** · 31/01/2020, 22h20

Bonjour,

Quelle est la valeur du paramètre parallel_degree_policy?
As-tu essayé de jouer avec la taille du buffer cache en mettant une valeur très petite pour db_cache_size?

**Ikebukuro** · 01/02/2020, 12h25

Salut vanagreg,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SQL> show parameter parallel_degree_policy
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy		     string	 MANUAL

Concernant la solution de mettre le db_cache_size tout petit tout petit, ce sera en dernière extrémité car je ne veux pas impacter les autres users avec mes tests; le maître mot quand je teste est : micro-chirurgie! sous-entendu "Ne pas perturber les autres sessions".

**Pomalaix** · 01/02/2020, 19h12

Je n'ai pas lu l'intégralité de votre question dans le détail, mais une chose me paraît certaine : si vous mettez /*+ PARRALEL */ au lieu de /*+ PARALLEL */, il ne faut pas vous attendre à ce que ça ait le moindre effet !
Donc déjà, il faut peut-être revoir vos tests et vos conclusions à propos de cette directive.

**pachot** · 01/02/2020, 21h14

Il y a plusieurs choses qui ne vont pas dans ce raisonnement.
Parallel Query va effectivement forcer du direct read (sauf cas de in-memora parallel query) mais là il y a une erreur de syntaxe dans le hint.
"_serial_direct_read"=always va effectivement forcer du direct-path read mais pas pour du serial, qui est l'opposé de parallel query.

Mais maintenant revenons au but initial: Tester les débits en lecture du disque dur. Selon le type de stockage une si petite table va rester dans un autre cache (filesystem si on n'est pas en direct IO, ou SAN, NAS,..) Et le select * va passer plus de temps à envoyer le résultat qu'à vraiment lire sur le disque.

Avec une table beaucoup plus grosse, en 100aine de GB, un count(*) full table scan se fera probablement en direct path read.

**Ikebukuro** · 02/02/2020, 12h48

Envoyé par Pomalaix

Je n'ai pas lu l'intégralité de votre question dans le détail, mais une chose me paraît certaine : si vous mettez /*+ PARRALEL */ au lieu de /*+ PARALLEL */, il ne faut pas vous attendre à ce que ça ait le moindre effet !
Donc déjà, il faut peut-être revoir vos tests et vos conclusions à propos de cette directive.

Merci Pomalaix, je fais la faute de syntaxe dans 100% des cas... impossible de me corriger

Merci à toi aussi Franck, je reprends mes tests demain en tenant compte de tes remarques.

[EDIT]
Bon, a priori c'est OK, il faut deux choses :
1 - utiliser le hint PARALLEL
2 - modifier le paramètre "_small_table_threshold" pour forcer Oracle à considérer ma table comme une grosse table

Je finalise mes tests demain

**Waldar** · 03/02/2020, 14h37

Envoyé par Ikebukuro

Bon, a priori c'est OK, il faut deux choses :
1 - utiliser le hint PARALLEL
2 - modifier le paramètre "_small_table_threshold" pour forcer Oracle à considérer ma table comme une grosse table

Quid de CARDINALITY ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select /*+ PARALLEL CARDINALITY(zztest 1e9) */ * from zztest;

Désolé je n'ai pas de base Oracle sous le coude.

**Ikebukuro** · 03/02/2020, 19h46

Salut Waldar, merci pour la réponse mais cela ne marche pas...

Lecture 1 : lecture sur disque dur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SQL> set autotrace on
SQL> select /*+ PARALLEL */ * from zztest;
Execution Plan
----------------------------------------------------------
Plan hash value: 190125610
 
--------------------------------------------------------------------------------
------------------------------
 
| Id  | Operation	     | Name	| Rows	| Bytes | Cost (%CPU)| Time
|    TQ  |IN-OUT| PQ Distrib |
 
--------------------------------------------------------------------------------
------------------------------
 
|   0 | SELECT STATEMENT     |		| 20000 |  2441K|    56   (0)| 00:00:01
|	 |	|	     |
 
|   1 |  PX COORDINATOR      |		|	|	|	     |
|	 |	|	     |
 
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 20000 |  2441K|    56   (0)| 00:00:01
|  Q1,00 | P->S | QC (RAND)  |
 
|   3 |    PX BLOCK ITERATOR |		| 20000 |  2441K|    56   (0)| 00:00:01
|  Q1,00 | PCWC |	     |
 
|   4 |     TABLE ACCESS FULL| ZZTEST	| 20000 |  2441K|    56   (0)| 00:00:01
|  Q1,00 | PCWP |	     |
 
--------------------------------------------------------------------------------
------------------------------
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
 
 
Statistics
----------------------------------------------------------
	 86  recursive calls
	109  db block gets
	827  consistent gets
	359  physical reads
	  0  redo size
    1312627  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  6  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Lecture 2 : lecture en SGA.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select /*+ PARALLEL */ * from zztest;
Statistics
----------------------------------------------------------
	194  recursive calls
	113  db block gets
       1155  consistent gets
	  0  physical reads
	  0  redo size
    1312430  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	 57  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Lecture 3 : lecture en SGA encore, malgré le hint CARDINALITY.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select /*+ PARALLEL CARDINALITY(zztest 1e9) */ * from zztest;
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
 
 
Statistics
----------------------------------------------------------
	127  recursive calls
	113  db block gets
	745  consistent gets
	  0  physical reads
	  0  redo size
    1312922  bytes sent via SQL*Net to client
      15270  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
	  7  sorts (memory)
	  0  sorts (disk)
      20000  rows processed

Toujours forcer la lecture des blocs depuis le disque dur

Administration Oracle

Discussions similaires

Partager

Partager