Lenteurs de résultats après déplacement d'index de tablespace

**Ikebukuro** · 22/09/2016, 21h23

Un truc qui serait aussi sympa à faire MAIS qui va être long : tu fais SHOW PARAMETERS sur les deux bases et tu postes ici les paramètres et leur valeur de ceux qui sont différents entre TEST et PROD.
Car si le flush du buffer cache ne corrige pas le décalage du nb de physical reads, alors il doit y avoir un paramètre qui met la grouille MAIS il faut absolument corriger ce problème du nb de physical reads car 50% d'écart c'est trop!

Dernière idée : peut-être qu'il y a des hidden parameters dans ton fichier d'initialisation; ceux ci ne ressortent pas avec le SHOW PARAMETERS, sauf erreur de ma part et il commencent avec un underscore.
Si vous utilisez des fichiers textes appelés PFILE (INIT....ORA), regarde s'il n'y a pas de paramètres commençant par un underscore.
Si vous utilisez des fichiers binaires appelés SPPFILE (SPFILE....ORA), génère un PFILE depuis ce fichier binaire (CREATE PFILE FROM ...) et regarde dans le PFILE généré s'il n'y a pas de paramètres commençant par un underscore.

Si tout ça ne donne rien, je suis sec...

**fredodeveloppeur** · 23/09/2016, 14h06

Bonjour,

Waldar :
> PCTFREE, de COMPRESS : parfaitement identique entre les systèmes test et prod.

>show parameters db_file_multiblock_read_count;

PROD = 128

TEST = 8

Je continue avec les idées de Ikebukuro et 13thFloor !

Merci.

**fredodeveloppeur** · 23/09/2016, 14h36

Re,

Alors pour 13thFloor :

PROD :
PARTITION_NAME = null
240Mo
30720 blocks

TEST :
PARTITION_NAME = null
160Mo
20480 blocks

Merci.

**13thFloor** · 23/09/2016, 14h56

Bon, on a la cause des 50% de plus de disk reads : la table en prod occupe 50% d'espace de plus qu'en test.
La question à présent est de savoir pourquoi 139351 lignes occupent 160 Mo en TEST et 140296 lignes occupent 240Mo en PROD.

**fredodeveloppeur** · 23/09/2016, 15h09

Re,re,

Voici les différence de paramétrage entre les deux systèmes (cote à cote séparés par un "|" pour mise en forme en tableur ;-) )

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
*  ###################   SERVEUR ENVIRONNEMENT 282  ######################    *	|	*  ########################   SERVEUR PROD  ##############################    *
_allow_resetlogs_corruption          boolean     TRUE	|	
parallel_execution_message_size      integer     16384	|	parallel_execution_message_size      integer     2152
parallel_max_servers                 integer     160	|	parallel_max_servers                 integer     320
parallel_servers_target              integer     64	|	parallel_servers_target              integer     128
resource_manager_cpu_allocation      integer     4	|	resource_manager_cpu_allocation      integer     8
SQL> show parameters	|	SQL> show paarameters
_allow_resetlogs_corruption          boolean     TRUE	|	
archive_lag_target                   integer     1800	|	archive_lag_target                   integer     0
audit_trail                          string      NONE	|	audit_trail                          string      DB
compatible                           string      11.2.0	|	compatible                           string      11.1.0.0.0
cpu_count                            integer     4	|	cpu_count                            integer     8
db_file_multiblock_read_count        integer     8	|	db_file_multiblock_read_count        integer     128
db_recovery_file_dest                string      /oracle/ERPLN/index/flash_recovery_area	|	db_recovery_file_dest                string      /oracle/ERPLN/data/oradata/LNPRD/flash_recovery_area
db_recovery_file_dest_size           big integer 350G	|	db_recovery_file_dest_size           big integer 25G
dml_locks                            integer     5948	|	dml_locks                            integer     5068
job_queue_processes                  integer     2	|	job_queue_processes                  integer     1000
log_buffer                           integer     16613376	|	log_buffer                           integer     22986752
memory_max_target                    big integer 8304M	|	memory_max_target                    big integer 15936M
memory_target                        big integer 8304M	|	memory_target                        big integer 15936M
optimizer_index_cost_adj             integer     10	|	optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS	|	optimizer_mode                       string      CHOOSE
parallel_execution_message_size      integer     16384	|	parallel_execution_message_size      integer     2152
parallel_max_servers                 integer     160	|	parallel_max_servers                 integer     320
parallel_servers_target              integer     64	|	parallel_servers_target              integer     128
pga_aggregate_target                 big integer 900M	|	pga_aggregate_target                 big integer 0
processes                            integer     887	|	processes                            integer     750
recyclebin                           string      OFF	|	recyclebin                           string      on
resource_manager_cpu_allocation      integer     4	|	resource_manager_cpu_allocation      integer     8
result_cache_max_size                big integer 15424K	|	result_cache_max_size                big integer 40800K
sessions                             integer     1352	|	sessions                             integer     1152
sga_max_size                         big integer 3008M	|	sga_max_size                         big integer 15936M
sga_target                           big integer 3008M	|	sga_target                           big integer 0
shared_pool_reserved_size            big integer 84M	|	shared_pool_reserved_size            big integer 87241523
spfile                               string      /oracle/home/11g/product/11.2.0/db_1/dbs/spfileLNPRD.ora	|	spfile                               string
transactions                         integer     1487	|	transactions                         integer     1267

**13thFloor** · 23/09/2016, 15h39

Si les statistiques sont récentes sur la table concernée, tu peux faire une analyse des lignes chainées :

Code :

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

1
2
3
4
5
6
7
8
9
10
create table CHAINED_ROWS (
    owner_name         varchar2(30),
    table_name         varchar2(30),
    cluster_name       varchar2(30),
    partition_name     varchar2(30),
    subpartition_name  varchar2(30),
    head_rowid         urowid,
    analyze_timestamp  date
 );
analyze table TTFACR200301 list chained_rows;

Puis aller vérifier le taux de chainage :

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
  SELECT tc.col#, ts.*
    FROM (SELECT compression, ' || ' compress_for, '  || ' pct_free, blocks, num_rows, cr.chain_cnt, DECODE (num_rows, 0, 0, ROUND ( (cr.chain_cnt / num_rows) * 100)) AS chain_percent,
                 avg_row_len,
                 last_analyzed,
                 cr.subpartition_name,
                 cr.partition_name,
                 cr.table_name
            FROM dba_tab_subpartitions ts,
                 (  SELECT table_name, partition_name, subpartition_name, COUNT (*) AS chain_cnt
                      FROM chained_rows
                     WHERE subpartition_name <> 'N/A'
                  GROUP BY table_name, partition_name, subpartition_name) cr
           WHERE     cr.table_name = ts.table_name
                 AND ts.partition_name = cr.partition_name
                 AND ts.subpartition_name = cr.subpartition_name
          UNION ALL
          SELECT compression, ' || ' compress_for, ' || ' pct_free, blocks, num_rows, cr.chain_cnt, DECODE (num_rows, 0, 0, ROUND ( (cr.chain_cnt / num_rows) * 100)) AS chain_percent,
                 avg_row_len,
                 last_analyzed,
                 NULL AS subpartition_name,
                 cr.partition_name,
                 cr.table_name
            FROM dba_tab_partitions ts,
                 (  SELECT table_name, partition_name, COUNT (*) AS chain_cnt
                      FROM chained_rows
                     WHERE partition_name <> 'N/A' AND subpartition_name = 'N/A'
                  GROUP BY table_name, partition_name) cr
           WHERE cr.table_name = ts.table_name
                 AND ts.partition_name = cr.partition_name
          UNION ALL
          SELECT compression, ' || ' compress_for, '  || ' pct_free, blocks, num_rows, cr.chain_cnt, DECODE (num_rows, 0, 0, ROUND ( (cr.chain_cnt / num_rows) * 100)) AS chain_percent,
                 avg_row_len,
                 last_analyzed,
                 NULL AS subpartition_name,
                 NULL AS partition_name,
                 cr.table_name
            FROM dba_tables ts,
                 (  SELECT table_name, COUNT (*) AS chain_cnt
                      FROM chained_rows
                     WHERE partition_name is null AND subpartition_name = 'N/A'
                  GROUP BY table_name) cr
           WHERE cr.table_name = ts.table_name) ts,
         (  SELECT COUNT (*) col#, table_name
              FROM dba_tab_cols
          GROUP BY table_name) tc
   WHERE 1 = 1 AND tc.table_name = ts.table_name
ORDER BY chain_cnt / num_rows DESC;

Si le pourcentage est important, il faudra envisager de réorganiser la table avec alter table move (+ alter index rebuild pour ses index mais je crois me souvenir que tu as indiqué qu'elle n'en avait pas).
En espérant qu'on a bien la même valeur (ou très proche) avg_row_len pour les 2 environnements (longueur moyenne d'une ligne) :

Code :

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

select avg_row_len from dba_tables where table_name='TTFACR200301'

**Ikebukuro** · 23/09/2016, 16h00

Plusieurs paramètres sont "bizarres" en prod par rapport à TEST :
Ceux qui me semblent les plus pénalisants sont en gras : pourquoi avoir des valeurs à 0 en PROD alors que ce n'est pas le cas en TEST et, deuxièmement, pourquoi avoir la valeur CHOOSE pour optimizer_mode alors que, sauf erreur de ma part, c'est une valeur inconnue en 10 et 11g et ça fait référence à une valeur obsolète.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
cpu_count                            integer     4	|	cpu_count                            integer     8
db_file_multiblock_read_count        integer     8	|	db_file_multiblock_read_count        integer     128
log_buffer                           integer     16613376	|	log_buffer                           integer     22986752
memory_target                        big integer 8304M	|	memory_target                        big integer 15936M
optimizer_index_cost_adj             integer     10	|	optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS	|	optimizer_mode                       string      CHOOSE
pga_aggregate_target                 big integer 900M	|	pga_aggregate_target                 big integer 0
resource_manager_cpu_allocation      integer     4	|	resource_manager_cpu_allocation      integer     8
result_cache_max_size                big integer 15424K	|	result_cache_max_size                big integer 40800K
sga_max_size                         big integer 3008M	|	sga_max_size                         big integer 15936M
sga_target                           big integer 3008M	|	sga_target                           big integer 0

**13thFloor** · 23/09/2016, 16h09

Coté paramètres, il y a quelques différences notables pour les performances entre les 2 environnements :
db_file_multiblock_read_count et optimizer_index_cost_adj plus faibles en TEST => les accès par index seront favorisés

sga_target et pga_target à 0 : il est en Automatic Memory Management sur la prod.

Sans parler de db_recovery_file_dest_size et optimizer_mode, mais cela mériterait un autre post.

**Ikebukuro** · 23/09/2016, 18h23

J'avais vu optimizer_index_cost_adj mais fredodeveloppeur a dit qu'il n'y a pas d'index sur les tables sélectionnées donc je n'ai pas mentionné ce paramètre.

**fredodeveloppeur** · 10/10/2016, 12h35

Bonjour à tous,

Tout d'abord, je tiens à vous remercier tous pour nous avoir aiguillés sur la voie de la résolution de ce problème.

Les performances sont revenues comme avant suite à la remise à niveau du paramétrage Oracle.

Ceci à aussi permis de mettre en évidence d'autres anomalies de performances sur les baies et serveurs accédants aux données... mais ce n'est plus dans notre cours maintenant.

Donc merci encore à tous.

**dell68** · 10/10/2016, 16h10

Salue,

Pour savoir d'ou vient ce probleme de performance des requêtes utilisant les indexes que tu as déplacés, je
te suggère ce lien qui utilise une fonctionnalité d'analyse de performance des requêtes apres changements
au niveau de la base :
https://oracle-base.com/articles/11g...analyzer-11gr1

Lenteurs de résultats après déplacement d'index de tablespace [11g]

Administration Oracle

Discussions similaires

Partager

Partager