Toujours lire les data blocs depuis le disque dur sans vider le Buffer Cache - Always read data blocks from the hard drive
Introduction
C'est article fait suite à "Comment vider des données en Shared Pool ?" : http://dbaoraclesql.canalblog.com/archives/2020/01/20/37957327.html
sauf que cette fois on va s'intéresser au Database Buffer Cache.
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 par exemple! Cela arrive quand on a tuné un SELECT dans la base, qu'on estime ne plus rien pouvoir faire au niveau Oracle et que donc il faut maintenant tuner le hardware, notamment les disques durs avec l'utilitaire Linux Hdparm.
Le problème est que si les blocs sont lus depuis le disque dur lors du premier SELECT, ils sont placés en SGA et les prochains SELECTs les liront dans le Buffer Cache. Donc le tuning des disques ne pourra pas être testé...
Comment faire?
Points d'attention
Ne jamais vider le Database Buffer Cache en production sous peine de fortement ralentir les traitements.
Base de tests
Une base Oracle 18c.
Exemples
============================================================================================
Méthode 1 : flusher le Database Buffer Cache
============================================================================================
Une solution brutale est de vider TOUT le Database Buffer Cache avec la commande FLUSH. Problème, cela va ralentir les SELECTs à venir de tous les users puisque Oracle va devoir relire les données depuis le disque.
On utilise Autotrace pour voir les lectures physiques et logiques.
SQL> set autotrace on
Ma base vient de démarrer : le SELECT lit les données sur disque dur.
SQL> select * from zztest;
Statistics
----------------------------------------------------------
142 recursive calls
4 db block gets
1856 consistent gets
381 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
8 sorts (memory)
0 sorts (disk)
20000 rows processed
On relance le SELECT : il n'y a que des lectures en SGA.
SQL> select * from zztest;
20000 rows selected.
SQL> select * from zztest;
Plan hash value: 3582063246
----------------------------------------------------------------------------
| 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
----------------------------------------------------------
235 recursive calls
4 db block gets
2048 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
14 sorts (memory)
0 sorts (disk)
20000 rows processed
Si on flush le buffer cache, on revoit les lectures sur le disque dur avec "381 physical reads". Problème, cela concerne TOUTES les tables, donc on ralentit tous les SELECTs et tous les utilisateurs. Le résultat est OK mais ce n'est pas ce que je veux!
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from zztest;
Statistics
----------------------------------------------------------
142 recursive calls
4 db block gets
1856 consistent gets
381 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
8 sorts (memory)
0 sorts (disk)
20000 rows processed
============================================================================================
Méthode 2 : le Direct Path Read
============================================================================================
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. C'est pourquoi le flush du Buffer Cache n'est pas une bonne solution.
Donc comment lire TOUJOURS les données depuis le disque dur sans flusher le Buffer Cache?
La solution est de provoquer un Direct Path Read!
Que dit la doc Oracle? Qu'il s'agit de la lecture physique de blocs et que ceux-ci sont mis en cache non pas dans la SGA mais dans la PGA. "A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory".
A noter qu'il n'existe pas de hint pour forcer un "direct path read", que ce hint soit documenté ou non.
SQL> select name from v$sql_hint where UPPER(name) like '%DIRECT%' or UPPER(name) like '%PATH%' or UPPER(name) like '%READ%' order by 1;
name
------
NO_XDB_FASTPATH_INSERT
VECTOR_READ
VECTOR_READ_TRACE
XDB_FASTPATH_INSERT
Le hint PARALLEL seul ne suffit pas
On peut utiliser le hint PARALLEL (avec un seul R et deux L) car celui-ci force ce qu'on appelle le "direct path read".
Le plan d'éxcution a bien changé mais aucune lecture physique... Internet aurait-il menti? Ce serait bien la première fois :-)
SQL> select /*+ PARALLEL */ * from zztest;
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
----------------------------------------------------------
8 recursive calls
113 db block gets
680 consistent gets
0 physical reads
0 redo size
1312850 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
Transformer ma table en grosse table avec le paramètre _small_table_threshold
Visiblement le hint PARALLEL n'est pas suffisant...
En cherchant mieux sur le net j'ai trouvé cela : "The hidden parameter "_small_table_threshold" defines the number of blocks to consider a table as being "small". If size of the table or segment > 5 * _small_table_threshold or 10% of buffer cache, then the table/segment is considered Large table and always uses Direct Path Read."
En clair, il faut que je trompe Oracle pour lui faire croire que ma table est une grosse table en disant que celle-ci dépasse de 5 fois la valeur du paramètre _small_table_threshold (c'est plus simple que de travailler sur les 10% du Buffer cache).
Attention, seul SYS peut requêter les structures X$ et récupérer la valeur de ce paramètre.
SQL> select ksppstvl
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ksppinm='_small_table_threshold';
from x$ksppi x, x$ksppcv y
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> connect SYS as sysdba
Enter password:
Connected.
SQL> select ksppstvl
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ksppinm='_small_table_threshold';
KSPPSTVL
-----------
1171
La taille en blocs de ma table est près de 400 comme vu ci-dessus... On va donc mettre le paramètre à 50 blocs ==> 5*50 = 250 pour transformer ma petite table en GROSSE table.
Attention, c'est un paramètre caché donc il faut contacter le support avant de modifier celui-ci en production. MAIS comme cette modification est là pour du tuning sur une base de recette, vous ne devriez pas le modifier en prod.
On se reconnecte avec le user de tests.
[oracle@vbgeneric ~]$ sqlplus HR
Enter password:
SQL> alter session set "_small_table_threshold" = 50;
Session altered.
SQL> show parameter _small_table_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_small_table_threshold integer 50
Héhé, ça marche :-)
SQL> select /*+ PARALLEL */ * from zztest;
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
----------------------------------------------------------
171 recursive calls
61 db block gets
680 consistent gets
356 physical reads
0 redo size
1313035 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
Une deuxième fois? C'est encore OK :-) Youppppppeeeeee!
SQL> select /*+ PARALLEL */ * from zztest;
Statistics
----------------------------------------------------------
6 recursive calls
57 db block gets
524 consistent gets
353 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
0 sorts (memory)
0 sorts (disk)
20000 rows processed
A noter que sans le hint PARALLEL, cela ne fonctionne pas : les données sont lues depuis la SGA. C'est donc bien le parallélisme qui provoque le Direct Path Read.
SQL> select * from zztest;
Plan hash value: 3582063246
----------------------------------------------------------------------------
| 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
----------------------------------------------------------
57 recursive calls
6 db block gets
1808 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
3 sorts (memory)
0 sorts (disk)
20000 rows processed
Les autres sessions ne sont pas impactées
Dernier test : je me connecte comme un autre user pour voir si mes modifications n'impactent vraiment que ma session.
Oui, dans ce cas, les données sont bien lues en SGA et pas sur disque dur.
[oracle@vbgeneric ~]$ sqlplus HR2
Enter password:
SQL> set autotrace on
SQL> select * from HR.zztest;
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| 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
----------------------------------------------------------
215 recursive calls
4 db block gets
1955 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
6 sorts (memory)
0 sorts (disk)
20000 rows processed
Vider sous Linux le cache du disque dur
ATTENTION, il y a un piège au niveau Linux! Sur le net il est précisé qu'il faut vider le cache disque car, la table étant petite, le résultat tient dans ce cache...
Je lance une commande de flush du cache disque (en étant root car il faut certains droits pour cette commande).
[oracle@vbgeneric ~]$ su - root
Password:
Last login: Fri Oct 4 10:40:29 EDT 2019 on pts/0
[root@vbgeneric ~]# echo 3 > /proc/sys/vm/drop_caches
Pas de changement au niveau Oracle, j'ai les même stats mais avec l'Autotrace on n'a pas le temps d'exécution de la requête.
SQL> select /*+ PARALLEL */ * from zztest;
Statistics
----------------------------------------------------------
6 recursive calls
57 db block gets
524 consistent gets
353 physical reads
0 redo size
1311991 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
Conclusion
Il faut deux conditions pour forcer systématiquement les lectures de blocs depuis le disque dur plutôt que depuis la SGA :
- le hint PARALLEL
- le paramètre _small_table_threshold avec une valeur telle que ma table devienne une grosse table
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)