Identifier les causes des verrous par l'historique

Depuis quelques temps grâce à Oracle Enterprise Manager, nous avons remarqué des verrous empêchaient ou plutôt retardaient certains traitements de se compléter dans un temps normal.

Pour identifier les sessions et/ou les requêtes en cause, j'ai utilisé la requête ci-dessous. Enterprise Manager me fournissait déjà les "session id" donc, assez simple d'extraire les infos et de plus, je savais à quel moment les verrous se sont produits :

col sql_text format a60 wrap
col event format a30 wrap
select count(*), s.sql_text, h.event
from GV$active_session_history h, gv$sql s
where h.sql_id = s.sql_id
and h.blocking_session in (1474,1260,200,592,1165,1351,584,18,780,1164,595)
and h.sample_time between to_date('2010-08-03 03:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-08-03 12:00:00','YYYY-MM-DD HH24:MI:SS')
group by s.sql_text, h.event
order by 1 DESC;

Dans mon cas, j'ai remarqué beaucoup d'attente lié à une requête qui impliquait l'événement "enq: TX - row lock contention".

Ps. J'ai utilisé la vue GV$... parce que mon cas s'était produit sur un environnement Oracle RAC 11gR2.

Enlever un énoncé SQL de la cache (SHARED POOL)

Voici un script que j'utilise pour supprimer une requête (énoncé SQL) de la cache partagée nommé "shared pool". Ce script est fort utile lorsque vous effectuez du "tuning" de requête.

REM *******************************************************************
REM * Script: FlushSQL.sql
REM * Titre : Chercher et générer la commande pour enlever un énoncé
REM * SQL du SHARED POOL
REM * Auteur: Eric Cloutier
REM * Date modif. : 15-06-2010
REM * Parametres :
REM * Aucun
REM *******************************************************************
set pagesize 9999 feed off linesize 200 trimspoo on verify off
--
-- Sous 10g (10.2.0.4) :
-- Alter session set events '5614566 trace name context forever';
--
spool FlushSQL.log

Accept sql_text prompt 'Inscrire une partie de la requête (LIKE est utilisé alors inscrire %): '

col cmd_sql format a80
col username format a20
col executions format 999999
col sql_text format a30 wrap

select 'exec sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',1);' cmd_sql,
sql_id, child_number, executions, u.username, sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and u.user_id = s.parsing_user_id
/

spool off
set feed on linesize 5000
Prompt
Prompt -- Résultat dans le fichier : FlushSQL.log
Prompt

Cette fonctionnalité peut être utilisé en 10g (10.2.0.4) cependant, vous devez initialiser un "event" soit à la session ou au niveau de la base de données que qu'elle fonctionne.

Un merci particulier à Kerry Osborne pour son blog.

ORA-01078 au démarrage d'une instance Oracle 11g sous ASM

J'ai rencontré cette erreur lors d'un redémarrage de la base de données suite à des modifications de paramètres de bases de données qui sont stockés dans un SPFILE.

La base de données est de la version Oracle 11gR2 et elle est liée à un Grid Infrastructure qui utilise Oracle ASM et Oracle Restart.

Voici les étapes que j'ai suivi pour résoudre le problème :

--
oracle@romeo:/tmp> srvctl stop database -d F100
oracle@romeo:/tmp> srvctl start database -d F100
PRCR-1079 : Failed to start resource ora.f100.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.f100.db' on 'romeo' failed

--
-- Tentative de démarrage avec SQL*Plus
--
oracle@romeo:/tmp> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mer. Mai 5 12:07:49 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORA-01078: failure in processing system parameters
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1577058304 cannot be set to more than MEMORY_TARGET 1358954496.

--
-- Tentative de correction de la valeur du paramètre en erreur
--
SQL> alter system set SGA_MAX_SIZE=100 scope=spfile;
alter system set SGA_MAX_SIZE=100 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
ID de processus : 0
ID de session : 0, Numéro de série : 0

--
-- Essai de création du pfile à l'image du spfile
--
SQL> create pfile='/tmp/pfileEC.ora' from spfile;
create pfile='/tmp/pfileEC.ora' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

--
-- Afficher l'emplacement exact du SPFILE
--
SQL> ! cat $ORACLE_HOME/dbs/initF100.ora
SPFILE='+SYSDG01/F100/spfileF100.ora'

--
-- Création du pfile à l'image du spfile
--
SQL> create pfile='/tmp/pfileEC.ora' from spfile='+SYSDG01/F100/spfileF100.ora';

File created.

SQL> exit

--
-- Éditer le pfile pour enlever la ligne SGA_MAX_SIZE
--
oracle@romeo:/tmp> vi pfileEC.ora

--
-- Redémarrer la base de données avec le PFILE pour s'assurer
-- aucune autre erreur
--
oracle@romeo:/tmp> sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Mer. Mai 5 12:12:40 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to an idle instance.

SQL> startup pfile='/tmp/pfileEC.ora'

ORACLE instance started.

Total System Global Area 1871208448 bytes
Fixed Size 2149152 bytes
Variable Size 1325405408 bytes
Database Buffers 536870912 bytes
Redo Buffers 6782976 bytes
Database mounted.
Database opened.

--
-- Création du spfile à l'image du pfile
--
SQL> Create spfile='+SYSDG01/F100/spfileF100.ora' from pfile='/tmp/pfileEC.ora';

File created.

--
-- Arrêt de la base de données
--
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

--
-- Redémarrer la base de données à partir du Grid Infrastructure
--
oracle@romeo:/tmp> srvctl start database -d F100

Calculer le MEMORY_TARGET sous 11g

À partir des valeurs actuelles du SGA et du PGA, vous pouvez facilement déterminer les valeurs des nouveaux paramètres relatifs à la gestion automatique de la mémoire. Pour simplifier le calcul, je me suis bâtit une requête qui me propose les valeurs de base. La voici :

Select '-- Memory Target = '
||round(to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
'Alter system set memory_target='
||to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;'||chr(10)||
'-- Memory Max Target = '
||round(to_char((qry_sga_max.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
'Alter system set memory_max_target='
||to_char((qry_sga_max.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;'||chr(10)||
'Alter system reset sga_target scope=spfile;'||chr(10)||
'Alter system reset pga_aggregate_target scope=spfile;'||chr(10)||
'-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET'||chr(10)||
'Alter system set sga_max_size='
||to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;' CMD_SQL
from (select value from v$parameter
where name='sga_target') qry_sga_target,
(select value from v$parameter
where name='pga_aggregate_target') qry_pga_target,
(select value from v$pgastat
where name='maximum PGA allocated') qry_pga_alloc,
(select value from v$parameter
where name='sga_max_size') qry_sga_max;


Il ne vous reste qu'à estimer le MEMORY_MAX_TARGET.

Utile ce SQL_ID !

Actuellement, nous travaillons à concevoir un outil de vérification de la performance des requêtes SQL entre une base de données Oracle 10g et 11g. Ceci nous permettra d'évaluer les impacts et d'être proactif dans l'identification, la configuration et, s'il y a lieu, dans les changements à apporter.

Pour débuter, nous collectons les requêtes et leurs statistiques qui sont exécutées par les utilisateurs via leurs applications sur la base de données 10g et nous stockons tout simplement ces informations dans des tables de travail. En faisant cela, nous sommes certains d'avoir des requêtes réelles, des statistiques claires et précises. Lorsque nous aurons migrés la base de données à Oracle 11g, la plupart des requêtes collectées précédemment seront sans aucun doute exécutées sur la nouvelle base de données. Lors de la conception de notre script, nous nous demandions comment nous pourrions retrouver et jumeler les requêtes identiques pour comparer les statistiques. À notre grande surprise, nous avons remarqué que le valeur contenue dans la colonne SQL_ID est identique entre les deux versions de bases de données. Vous comprendrez que ça vient de nous simplifier la vie et, pas juste un peu. Ce sera simple de regrouper les requêtes par SQL_ID et de les comparer puis faire ressortir celles ayant des valeurs de statistiques dont l'écart est prononcé.

Voici une démonstration :

SQL> conn system@A009
Enter password:
Connecté.
SQL> Select count(*) from sys.v_$parameter;

COUNT(*)
------------
263

SQL> select a.SQL_ID, a.executions, a.disk_reads, a.buffer_gets, a.sql_text
2 from v$sql a, all_users b
3 where a.parsing_user_id = b.user_id
4 and b.username = 'SYSTEM'
5 and a.sql_text = 'Select count(*) from sys.v_$parameter'
6 order by 1 desc;

SQL_ID EXECUTIONS DISK_READS BUFFER_GETS SQL_TEXT
------------- ------------ ------------ ------------ --------------------------------------
3yq5vhv923584 1 1 2 Select count(*) from sys.v_$parameter

SQL> conn system@mig11201
Enter password:
Connecté.
SQL> Select count(*) from sys.v_$parameter;

COUNT(*)
------------
343

SQL> select a.SQL_ID, a.executions, a.disk_reads, a.buffer_gets, a.sql_text
2 from v$sql a, all_users b
3 where a.parsing_user_id = b.user_id
4 and b.username = 'SYSTEM'
5 and a.sql_text = 'Select count(*) from sys.v_$parameter'
6 order by 1 desc;

SQL_ID EXECUTIONS DISK_READS BUFFER_GETS SQL_TEXT
------------- ------------ ------------ ------------ --------------------------------------
3yq5vhv923584 1 0 2 Select count(*) from sys.v_$parameter

Utilisation de la commande "tar"

La commande "tar" permet de regrouper plusieurs fichiers sous un même fichier. Cette commande fusionne les fichiers sans les compresser.

Concevoir un archive

tar -vcf nom_fichier_archive.tar nom_fichier

• tar: la commande
• vcf: les options
• v : fournit des informations lors de la conception de l’archive (optionnelle)
• c: Indique de créer un archive
• f: permet de spécifier le nom du fichier d’archive
• nom_fichier_archive.tar: nom donné au fichier d’archive
• nom_fichier : nom du dossier (ou fichiers) à archiver

Extraire le contenu d’un archive

La commande tar est aussi utilise pour extraire les fichiers contenu dans l’archive.
Extraire tout le contenu d’un archive

tar -vxf nom_fichier_archive.tar

• tar: la commande
• vxf: les options
• v: Afficher des informations lors de l’extraction (optionnelle)
• x: Indique d’extraire les fichiers
• f: Indique que l’archive contient des fichiers
• nom_fichier_archive.tar: Nom du fichier d’archive à traiter

Extraire certains fichiers d’un archive

tar -xvf nom_fichier_archive.tar "*.dbf"

Afficher le contenu d’un fichier d’archive

tar -tf nom_fichier_archive.tar

Manipuler avec soins car contient des paramètres standards, cachés et Hints

L'une des premières vérifications que je vous suggère fortement de faire lorsque vous effectuez de l'optimisation de requêtes est de vérifier la valeur des paramètres de bases de données qui ont été personnalisées (modifiées).

Dans plusieurs cas que j'ai rencontrés, surtout dans le cadre de projet de migration, certains paramètres avaient été modifiés pour contourner des problèmes (bugs) et, suite à la migration, ceux-ci sont la plupart du temps plus nécessaires.

Utiliser la commande "ALTER SESSION" pour ceux qui sont modifiable dynamiquement. Vous pourrez vérifier le comportement de vos requêtes avant d'appliquer le changement de façon permanente (ALTER SYSTEM) sur la base de données.

Autres conseils, évitez de modifier les paramètres de base de données, surtout ceux cachés. Vous aurez des problèmes lors des futures migrations. Si vous n'avez pas le choix, je vous conseille de bien documenter leur utilisation.

C'est la même situation pour les HINTS de requêtes, ce n'est que des diachylons et… souvenez-vous qu'un diachylon fini toujours par se décoller :)