 |
|
|
|
|
|
|
|
| |
| |
| |
Technique |
|
Domaines |
|
Divers | |
Contact | |
Pour appuyer également la notion de cache spatial, à chaque lecture d’un bloc, ORACLE demandera à l’OS la lecture d’un paquet de DB_FILE_MULTIBLOCK_READ_COUNT blocs ORACLE. Ce paramètre indique aux serveurs ORACLE combien de blocs supplémentaires (valeur du paramètre -1) ORACLE lira lors de la lecture d’un seul bloc (lors du FTS). Il s’agit bien sûr des blocs contigus fournis par l’OS. Si ce paramètre est trop petit, la lecture est peu efficace, ORACLE ne tire pas profit de tous les blocs lus d’un coup par l’OS. Si ce paramètre est supérieur au rapport entre le buffer de lecture OS et la taille du bloc ORACLE, il n’y a ni danger ni lectures répétées. Voici un test qui prouve que même si nous utilisons une valeur trop élevée, le système ne provoque pas plusieurs lectures. Le test suivant fait usage des statistiques sur PHYRDS et PHYBLKRDS correspondant au fichier qui hébergé la table dont le FULL SCAN sera demandé. Leur rapport nous montrera le nombre de blocs lus à chaque lecture. Dans l’exemple ci-dessous, remarquez la valeur maximale des blocs lus, même dans le cas d’un db_file_multiblock_read_count élevé !
En conclusion, mettre ce paramètre à une valeur qui équivaut :
Taille du cache en lecture (OS) divisé par la taille du bloc Oracle !
La taille du cache OS est soit déduite plus bas, soit après une bière bue avec l’administrateur système.
Créer et peupler la grosse table :
SQL> DROP TABLE anp.big;
Table dropped.
SQL> CREATE TABLE anp.big (c1 VARCHAR2 (50));
Table created.
SQL> INSERT INTO anp.big VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
1 row created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
2 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
4 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
8 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
16 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
32 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
64 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
128 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
256 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
512 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
1024 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
2048 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
4096 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
8192 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
16384 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
32768 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
65536 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
131072 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
262144 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
524288 rows created.
SQL> INSERT INTO anp.big SELECT * FROM anp.big;
1048576 rows created.
SQL> COMMIT;
Commit complete.
La table contient 2097152 lignes, bon sujet du Full Table Scan !
SQL> /* trouver le fichier impacté */
SQL> SELECT file_id FROM dba_data_files WHERE tablespace_name=
2 (SELECT tablespace_name FROM dba_tables
3 WHERE table_name='BIG' AND owner = 'ANP');
FILE_ID
----------
9
Diminuons un peu le cache pour que la table ne soit pas complètement continue en mémoire :-)
SQL> /* 9i, diminuer le cache */ SQL> alter system set db_cache_size=10000000; System altered. SQL> SQL> COLUMN phyrds new_value oldphyrds noprint; SQL> column phyblkrd new_value oldphyblkrd noprint;
Cas 1
SQL> /*
DOC>1.
DOC>Forcer db_file_multiblock_read_count à 4 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 4;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "4 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "4 blocks_per_read"
new 1: select (phyblkrd- 404619)/(phyrds- 29637) "4 blocks_per_read"
4 blocks_per_read
-----------------
3,88835943
Cas 2
SQL> /*
DOC>2.
DOC> Forcer db_file_multiblock_read_count à 8 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 8;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "8 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "8 blocks_per_read"
new 1: select (phyblkrd- 416043)/(phyrds- 32575) "8 blocks_per_read"
8 blocks_per_read
-----------------
7,88470234
Cas 3
SQL> /*
DOC>3.
DOC> Forcer db_file_multiblock_read_count à 16 et provoquer FTS */
SQL> ALTER system set db_file_multiblock_read_count = 16;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "16 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "16 blocks_per_read"
new 1: select (phyblkrd- 426506)/(phyrds- 33902) "16 blocks_per_read"
16 blocks_per_read
------------------
15,7795527
Cas 4
SQL> /*
DOC>4.
DOC> Forcer db_file_multiblock_read_count à 32 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 32;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "32 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "32 blocks_per_read"
new 1: select (phyblkrd- 436384)/(phyrds- 34528) "32 blocks_per_read"
32 blocks_per_read
------------------
31,4585987
Cas 5
SQL> /*
DOC>5.
DOC> Forcer db_file_multiblock_read_count à 64 et provoquer FTS */
SQL> ALTER system set db_file_multiblock_read_count = 64;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "64 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "64 blocks_per_read"
new 1: select (phyblkrd- 446262)/(phyrds- 34842) "64 blocks_per_read"
64 blocks_per_read
------------------
62,1257862
Cas 6
SQL> /*
DOC>6.
DOC> Forcer db_file_multiblock_read_count à 128 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 128;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "128 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "128 blocks_per_read"
new 1: select (phyblkrd- 456140)/(phyrds- 35001) "128 blocks_per_read"
128 blocks_per_read
-------------------
120,463415 <--------- 128
Cas 7
SQL> /*
DOC>7.
DOC> Forcer db_file_multiblock_read_count à 512 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 512;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "512 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "512 blocks_per_read"
new 1: select (phyblkrd- 466018)/(phyrds- 35083) "512 blocks_per_read"
512 blocks_per_read
-------------------
120,463415 <--------- toujours 128
Cas 8
SQL> /*
DOC>8.
DOC> Forcer db_file_multiblock_read_count à 1024 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 1024;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "512 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "512 blocks_per_read"
new 1: select (phyblkrd- 475896)/(phyrds- 35165) "512 blocks_per_read"
512 blocks_per_read
-------------------
120,463415 <--------- toujours 128
Cas 9
SQL> /*
DOC>9.
DOC> Forcer db_file_multiblock_read_count à 2048 et provoquer FTS */
SQL> alter system set db_file_multiblock_read_count = 2048;
System altered.
SQL> select phyrds, phyblkrd from v$filestat where file# = 9;
SQL> select count(*) from anp.big;
COUNT(*)
----------
2097152
SQL> select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "2048 blocks_per_read"
2 from v$filestat where file# = 9;
old 1: select (phyblkrd-&oldphyblkrd)/(phyrds-&oldphyrds) "2048 blocks_per_read"
new 1: select (phyblkrd- 485774)/(phyrds- 35247) "2048 blocks_per_read"
2048 blocks_per_read
--------------------
114,873563 <--------- toujours 128
SQL> spool OFF
En conclusion, le bon paramètre pour mon système est :
db_file_multiblock_read_count = 128;
Comme ma taille du bloc est 8k, j’en déduis que le cache système OS est de 128*8k = 1Mega, ou que dans l’absence du cache, le tète de lecture ramène 1 Mega à chaque I/O.
| Copyright © 1998-2002 Radu Caulea, TAFORA | MAJ 06/11/2006 |  |
| Commentaires et suggestions radu[CHEZ]tafora.fr | ||