Catégorie : Support Niveau 3

Développement, Exploitation, Support Niveau 2, Support Niveau 3

Trouver les informations basiques d’une base Oracle à laquelle on est connecté

La requête

SELECT SYS.UTL_INADDR.get_host_address      IP_ADDR
     , SYS.UTL_INADDR.get_host_name         HOSTNAME
     , SYS.DBMS_UTILITY.CURRENT_INSTANCE    INST#
     , SYS.DBMS_UTILITY.port_string         OS
     , platform_id
     , platform_name
     , name
     , DB_UNIQUE_NAME
     , DBID
     , current_scn
     , decode ( parallel, 'YES', 'RAC', 'NOT RAC') IS_RAC
from v$database natural join v$instance;

Le résultat (splitté pour une meilleure lecture)

IP_ADDR                      HOSTNAME     INST# OS                   
---------------------------- ------------ ----- -------------------- 
fe80::4b5:7ff9:2cca:6ca8%3   ALPHAORIOJO      1 IBMPC/WIN_NT64-9.1.0 

PLATFORM_ID PLATFORM_NAME                  NAME       DB_UNIQUE_NAME 
----------- ------------------------------ ---------- -------------- 
         12 Microsoft Windows x86 64-bit   OJOTST     ojotst     

        DBID CURRENT_SCN IS_RAC
------------ ----------- --------
  2831882428     8207733 NOT RAC

C’était pourtant pas si compliqué !

Exploitation, Support Niveau 3

Trouver les “lossy datas”

Vous avez ouvert une SR auprès du support Oracle(c) et l’analyste vous demande de trouver les “lossy datas”. sans plus d’explication que ça …

En fait ça se trouve avec CSSCAN

Il faut l’installer en tant que SYS sous SQLPLUS

set TERMOUT ON
set ECHO ON
spool csminst.log
drop user csmig cascade;
@?/rdbms/admin/csminst.sql

Et il faut le lancer avec une commande shell

csscan tochar=AL32UTF8 full=y

3 fichiers texte sont alors générés :

  • scan.out dans lequel est redirigée la sortie écran
  • scan.err dans lequel sont stockées les éventuelles erreurs de conversion
  • scan.txt qui stocke le rapport d’analyse

C’était pourtant pas si compliqué !

My Oracle Support - Créer un profile SQL avec des hints
Support Niveau 3, Tuning

Créer un profile SQL avec des hints

Créer un profile SQL avec des hints nécessite Tuning Pack mais est utile lorsqu’on fait face à un progiciel qui ne peut aller modifier son code. En effet, quel DBA n’est pas régulièrement confronté au problème suivant :

  • J’ai trouvé ce qui ne marche pas dans mon code SQL.
  • J’ai testé avec certains hints et ça a réglé le problème.
  • L’éditeur (ou le développeur) refuse de modifier son code pour implémenter les hints.

Il est possible depuis la version 10g de positionner des hints sur une requête sans aller modifier le code applicatif. La technique utilise le package dbms_sqltune et est donc soumise à licence.

(suite…)

Métrologie, Support Niveau 3

Les statistiques épisode: 1: où les trouver

Depuis … Poulala, oracle s’appuie sur des statistiques plutôt que sur l’heuristique1 pour optimiser ses plans d’exécution. De version en version, les statistiques dont dispose Oracle s’étoffent offrant ainsi à oracle de plus en plus d’informations sur les données stockées en base.

Les statistiques sont essentiellement stockées au niveau du dictionnaire de donnée dans les tables décrivant les divers segments qu’oracle est capable de manipuler mais elles peuvent aussi être calculées à la volée et sur échantillonnage. Elles sont utilisée entre autre par l’optimiseur de requête d’Oracle lorsqu’il analyse une requête dans le but de produire un2 plan d’exécution efficace dans un temps raisonnable3.

A partir de ces statistiques, oracle associe un coût à un texte SQL par un algorithme complexe et amélioré à chaque nouvelle version d’Oracle qui a été décortiqué pour certaines d’entre elles par l’éminent Jonatan Lewis dans le très vendu et encore plus lu Cost Based Oracle fundamentals lui même rédigé en langue de Shakespeare et malheureusement resté jusqu’à ce jour sans traduction française. Cet ouvrage donne une formule qui permet de calculer certains coûts à partir des statistiques Oracle, reste encore à savoir où les trouver et sur ce dernier point c’est aux table de la liste suivante (et plus particulièrement les vertes qu’il conviendra de s’intéresser. (suite…)

Bonnes pratiques, Développement, Exploitation, SQL, Support Niveau 2, Support Niveau 3

SQLPlus l(e seul)’outil qu’il vous faut pour administrer vos bases de données Oracle

Cet article est repris d’un ancien blog et fusionné avec un autre

SQLPlus est l’outil incontournable du DBA Oracle. Même si beaucoup n’utilisent que des interfaces graphiques, il arrive un moment ou un autre où il doit se connecter à une machine qui ne propose pas cette commodité et dès lors, l’outil basique, livré avec le moteur, devient un passage obligé voire incontournable ou presque.

Je liste ici quelques trucs, astuces, commandes qui permettent d’utiliser SQLPlus de manière sympathique et sans trop de prise de tête.

(suite…)

Métrologie, SQL, Support Niveau 3, Tuning

INDEX FULL SCAN et INDEX FAST FULL SCAN

Suite à un échange à propos du fonctionnement du CBO et plus particulièrement sur les choix de parcours d’index en full scan ou en fast full scan sur le forum developpez.net qui était de la teneur suivante :

Intervenant 1 – Est-il vrai que si on effectue le requête select ename from emp, oracle n’utilisera pas l’index même si celui-ci existe ?
Intervenant 2 – Vrai sauf si la colonne ename ne peut pas être nulle et ce par contrainte, auquel cas un index full scan sera choisi
Intervenant 3 – Ne vouliez vous pas dire index fast full scan ?
Intervenant 2 – Non :
(suite…)

Exploitation, Support Niveau 3

Ajouter une instance RAC en mode silencieux avec DBCA

La commande est “ultra-simple” :

dbca -silent 
     -addInstance 
     -gdbName $DBUN 
     -nodelist $NODELIST 
     -listeners  $LISTENERS 
     -sysDBAUserName SYS 
     -sysDBAPassword $SYSPASSWD

Où DBUN est le Database Unique Name de la base de donnée, NODELIST la liste de hostnames de noeuds du RAC, LISTENERS représente la liste des listener locaux et SYSPASSWD le mot de passe de l’utilisateur SYS

Exploitation, Métrologie, SQL, Support Niveau 2, Support Niveau 3, Tuning

Calcul des statistiques

Afficher les valeurs des paramètres utlisés par défaut par dbms_stats

col sname for a30
col SPARE4 for a50

select SNAME, spare4 from SYS.OPTSTAT_HIST_CONTROL$ ;

SNAME                          SPARE4
------------------------------ --------------------------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                          0
DEBUG                          0
SYS_FLAGS                      1
CASCADE                        DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                         NULL
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                  TRUE
GRANULARITY                    AUTO
AUTOSTATS_TARGET               AUTO

12 rows selected.

Les valeurs ici ne sont déjà pas les valeurs utilisée par oracle par défaut, NO_INVALIDATE a été modifiée

Ecrire une procédure de calcul des statistiques

Pour un client, j’ai écrit très rapidement cette procédure de calcul de statistiques. Très (trop) imparfaite

create or replace procedure sys.calc_stats is
    /*
     * Procédure de calcul des statistiques            ****************************
     *
     * Les statistiques son calculees sur les tables dont le cumul des modifications
     * (insert / delete / updates) dépasse 5% du volume de la table. Elle invalide
     * les plans en memoire et ignore ertains utilisateurs. Son parallelisme est de 4
     * et les statistiques ne sont pas calculees sur les index de la table
     *
     * ******************************************************************************
     * Version 1.0 pour ************ sur ORA 102040
     * ******************************************************************************
     */
begin
  for c in (
   select tm.table_name tbn
        , t.owner tbo
   from ( select table_name
               , table_owner
               , sum(inserts) inserts
               , sum(deletes) deletes
               , sum(updates) updates
          from dba_tab_modifications
          where table_owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                                 , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                                 , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
          group by table_name, table_owner ) tm
        inner join dba_tables t
                   on ( t.owner=tm.table_owner
                    and t.table_name=tm.table_name )
   where 1=1
     and t.owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                        , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                        , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
     and (tm.inserts+tm.updates+tm.deletes)/(nvl(t.num_rows,0)+1) > 0.05
      order by nvl(t.num_rows,0) asc  )
  loop
      dbms_stats.gather_table_stats(c.tbo, c.tbn, null, null, null, 'for all indexed columns size repeat', 4, 'ALL', FALSE, null, null, null, FALSE );
  end loop;
end;
/

Manque du paramétrage et de l’automatisme

Paramétrage
  • seuil de calcul
  • no_invalidate
Automatismes
  • Echantillonnage
  • Cascade

A faire évoluer donc …

Exploitation, Support Niveau 2, Support Niveau 3

Package de diagnostique pour les undotbs

Les scripts se basent sur l’activité AWR pour faire leur diagnostique, en toute logique leur utilisation est soumise à licence. 

set serveroutput on 

DECLARE
 tsn    VARCHAR2(40);
 tss    NUMBER(10);
 aex    BOOLEAN;
 unr    NUMBER(5);
 rgt    BOOLEAN;
 retval BOOLEAN;
BEGIN
  retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
  dbms_output.put_line('Undo Tablespace Is: ' || tsn);
  dbms_output.put_line('Undo Tablespace Size Is: ' || TO_CHAR(tss));

  IF aex THEN
    dbms_output.put_line('Undo Autoextend Is Set To: TRUE');
  ELSE
    dbms_output.put_line('Undo Autoextend Is Set To: FALSE');
  END IF;

  dbms_output.put_line('Undo Retention Is: ' || TO_CHAR(unr));

  IF rgt THEN
    dbms_output.put_line('Undo Guarantee Is Set To: TRUE');
  ELSE
    dbms_output.put_line('Undo Guarantee Is Set To: FALSE');
  END IF;
END;
/

Résultat 

Undo Tablespace Is: UNDOTBS1
Undo Tablespace Size Is: 32768
Undo Autoextend Is Set To: TRUE
Undo Retention Is: 900
Undo Guarantee Is Set To: FALSE

Estimer le meilleur paramétrage pour “undo_retention” Entre 2 dates 

SELECT  dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;

En général 

SELECT  dbms_undo_adv.best_possible_retention
FROM dual;

Entre 2 snapshots AWR 

SELECT dbms_undo_adv.best_possible_retention(10, 20)
FROM dual;