Redimensionner les redo logs

code php

Je ne sais pas s’il existe vraiement un niveau 0 de l’exploitation des SGBD oracle, mais le redimensionnement, à la hausse, des fichiers de tablespaces est du niveau le plus trivial qui soit. Cependant, à peine plus compliqué, il est parfois nécessaire de redimensionner les redo logs, ou plus précisément les « archived red log files ». C’est pourtant parfois nécessaire quand par exemple on constate un nombre de switch trop élevé. Il est recommandé d’en avoir 3 à 4 par heures et on les comptes soit en mode texte avec SQL*Plus soit de manière plus graphique avec l’outil qui vous semblera bon.

(suite…)

Taille des BDD dans ASM, asmcmd ou SQL ?

choose

Lorsqu’on utilise un OS classique avec un filesystem bien fait trouver la surface des bases de données est assez aisé. Cependant déterminer la Taille des BDD dans ASM est un peu moins évident.

Par exemple :
Mon ami le N2 m’a appelé en panique : Oulalalalaaaaaa, y’a pu d’place sur +DATA, quoi qu’on fait ?
C’est à peu près à ce moment là, si ce n’était déjà fait, que l’on regrette d’utiliser ASM.

(suite…)

Scripts SQL

Branchements et boucles en pur SQL

SQL est pratique mais il est compliqué de produire un script SQL qui s’adapte à toutes les versions de la base oracle ou qui réagit en fonction de certains résultats de requêtes. Cet article pour donner des astuces de scripting afin de gérer des branchements et boucles en pur SQL et de pouvoir créer des scripts adaptifs.
(suite…)

Scripts SQL

Modifications du paramétrage Oracle.

Comment faire ?

La requête suivante permets de trouver l’historique des modifications du paramétrage Oracle qui a pu être effectué au niveau de l’instance dans et stocké dans AWR. La rétention des informations est donc équivalente à celle de rétention des snapshots. Si vous utilisez un AWR Warehouse, potentiellement, vous conservez cet historique depuis son initialisation.

select eit
     , param
     , last_val
     , cur_val
     , is_def
  from ( select end_interval_time eit
              , parameter_name param
              , lag ( value, 1 ) over ( partition by dbid, instance_number, parameter_hash order by snap_id) last_val
              , value cur_val
              , isdefault is_def
           from dba_hist_parameter natural join dba_hist_snapshot
          where dbid = :dbid )
 where last_val != cur_val 
   and last_val is not null
/

En complément

La requête nécessite que la variable DBID soit positionnée afin pouvoir requêter aussi bien la base locale que le reository AWR Warehouse. En local on positionnera la bind variable de la manière suivante :

var dbid number

exec select dbid into :dbid from v$database 
/

Quelle utilité ?

Avoir l’historique des modifications du paramétrage Oracle permet, surtout lorsqu’on travaille à plusieurs sur une même base de données, d’apporter des éléments de réponse à la question : « Pourquoi mon appli marche-t-elle moins bien depuis 12 jour ? ». Donc savoir accéder à ces informations est une aide précieuse surtout lors d’interventions occasionnelles.

Histogrammes en 11gR2

Les performances d’Oracle reposent sur l’analyse des requêtes SQL au travers du Cost Based Optimizer (CBO) qui s’appuie sur les statistiques sur les divers objets de la base. Toute le problématique des statistiques Oracle est d’avoir une représentation la plus fidèle possible des données en base en un minimum d’informations. La solution proposée par Oracle pour le faire est d’utiliser les histogrammes.

Au niveau des tables et sans s’occuper des problématiques de partitionnement les statistiques sont visibles dans des vues diverses qui vont donner des informations au niveau de la table (par exemple DBA_TABLES, ALL_TAB_STATISTICS, USER_TABLES …) puis pour les colonnes des tables (USER_TAB_COLUMNS, DBA_TAB_COL_STATISTICS …) et enfin pour certaines colonnes un histogramme est créé et visible dans des vues telles que DBA_COL_HISTOGRAMS. Reste à juger de leur pertinence afin de décider si oui ou non on en a besoin pour telle ou telle colonne.

Frequency Histograms

Un petit jeu de test ?

create table t0 ( c1 number );

insert into t0
select abs(ceil(dbms_random.normal*3))
from sys.dual
connect by rownum <= 1E5 ;

exec dbms_stats.gather_table_stats(user, 'T0', method_opt=>'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)

On effectue un Explain plan pour toutes les requête dont le prédicat est C1=x où x varie de -2 à 15 que l’on compare au nombre effectif d’occurrence de chaque valeur ( count(*) group by C1 ).

Résultat

 

histo_freq

 

En bleu l’histogramme, en rouge le nombre effectif d’occurrences des valeurs données en abscisse … On a ici un histogramme qui représente parfaitement le contenu de la colonne C1, et qui contredit la documentation officielle du fait qu’il ne décroît pas progressivement au delà des valeurs minimales et maximales.

Pourquoi ?

L’histogramme a été calculé sur un échantillon de 100 %. Le CBO le considère comme « juste » en l’occurrence à raison, mais dans deux semaines ? Au bout de deux semaines aussi, et c’est nettement moins agréable.

Si on laisse Oracle choisir l’échantillon, ça donne quoi ?

exec dbms_stats.gather_table_stats(user, 'T0')

freq_auto

ATTENTION: L’échelle est logarithmique. Mais là on a bien ce que décrit la documentation. Et l’histogramme garde sa pertinence.

Height Balanced Histograms

En résumé: Les Frequency histograms c’est génial mais ça prend de la place donc si le nombre de valeur distinctes d’une colonne est trop important il faut trouver une autre solution pour rendre compte de la répartition non uniforme des données et ne pas trop encombrer le dictionnaire des données; c’est là qu’intervient le second type d’histogramme appelé height balanced histogram.

Oracle a décidé que le nombre maximal de valeurs distinctes qu’il allait stocker pour une colonne était de 254, au delà ou si on demande de calculer l’histogramme pour moins de valeurs qu’il n’en existe pour la colonne (non documenté) l’histogramme devient height balanced.

Un nouveau petit jeu de test ?

create table t1 ( c1 number );

insert /*+ APPEND */ into t1
select ceil(dbms_random.normal*35)
from sys.dual
connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)

create table t2 ( c1 number );

insert /*+ APPEND */ into t2
select (150-abs(c))*sign(c+0.1) 
from (select ceil(dbms_random.normal*35) c
      from sys.dual
      connect by rownum <=1E5 ) ;

On obtient les répartitions de données suivantes :

tables_loiNormale

Cardinalités sans histogramme

Si on calcule les statisitques sur la table en ne calculant pas l’histogramme on obtient la distribution de cardinalités suivantes (en vert):

no_histogram

Comme on pouvait s’y attendre, Oracle considère que chaque valeur est représentée autant que les autres. A noter tout de même: avant la valeur minimale et au delà de la valeur maximale détectée, le CBO estime une cardinalité progrssivement décroissante de la valeur moyenne calculée jusuq’à une valeur de 1. La raison en est que les statistiques n’ont pas vocation à être calculées en permanence et que donc, la table vivant, de nouvelles valeurs peuvent apparaitre.

Histogramme calculé sur un échantillon de 100% et pour 254 buckets

0n obtient la distribution de cardinalités suivantes (en bleu):

normal_h254_100

On constate :

  • Que l’histogramme fonctionne par palliers
  • Que dans chaque pallier chaque valeur n’est pas équitabement représentée.
  • Que l’estimation d’une cardinalité est au pire égale à l’estimation sans histogramme.

Histogramme calculé sur un échantillon de 100% et on laisse à Oracle le choix du nombre de buckets

normal_auto_e100

On constate :

  • Peu de différence avec la distribution précédente

Histogramme calculé sur un échantillon et pour un nombre du buckets nombre de buckets dont les choix sont laissés à Oracle

normal_auto_auto

On constate :

  • Cette fois ci, trois palliers
  • Une moins bonne correspodance des cardinalités estimées avec les cardinalités réelles

Globalement sur ces tests

Les histogrammes sont assez représentatifs des données (ouf !) mais peut-être que ça vient de la forme de la courbe, d’où l’idée d’essayer avec une courbe ‘inversée’.

On recrée le deuxième type d’histogramme (100% buckets auto)

normal_bol_h255_e100

On constate :

  • Une estimation aussi « bonne » ou « mauvaise » que l’estimation faite dans les mêmes conditions sur l’autre répartion de données.

Comment un histogramme est-il calculé ?

les données de la colonne sont ordonnées et le résultat découpé en autant de buckets que vous ou Oracle en avez décidé. A partir de là à chaque point de rupture entre deux buckets, la valeur est enregistrée.A chaque bucket correspond donc le nombre de lignes valuées de la table divisé par le nombre du buckets. A une valeur de la colonne qui est relevée correspond une cardinalité estimée au nombre de fois où elle a été relevée multiplié par le nombre de lignes que contient un bucket

Est-il possible de faire mieux ?

Oui mais c’est très coûteux: en effet, si on fait du dynamic sampling on obtient ça:

dynamic_sampling

Seulement, du coup, le temps de parsing est très fortement allongé et les lectures disques augmentées

Limites des histogrammes

Le calcul des histogrammes parait un bon compromis, cependant il est des cas où il s’avère inutile. Le graphe ci-après représente l’histogramme et les valeurs calculées sur une table de 10 millions de lignes sur une colonne présentant près de 1600 valeurs distinctes.

v1600_h254_100

On constate :

  • L’estimation faite est équivalente à celle qui serait survenue en l’absence d’histogramme. Si le calcul de l’histogramme n’est pas néfaste, il reste contre-productif.

Jusqu’à maintenant les histogrammes étaient calculés sur des colonnes de type numérique. Si on calcule un histogramme sur une colonne de type varchar2 on peut rencontrer un soucis dans le cas où un grand nombre de valeurs distinctes ont leur 32 premiers caractères en commun.

create table t6 ( c1 varchar2(64) );

insert /*+ APPEND */ into t6
select 'Foo Bar Baz Foo Bar Baz Foo Bar Baz '||to_char(ceil(dbms_random.normal*35))
from sys.dual
connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)

varchar

On constate :

  • L’estimation faite est égale au nombre de lignes de la table et donc totalement hors de propos.

Conclusion

Les histogrammes représentent un moyen intéressant d’avoir une estimation de la cardinalité réelle d’une occurrence de valeur pour une colonne donnée. Cependant, si les valeurs sont trop nombreuses ou pour certains types de données trop centrées autour d’une même valeur les statistiques calculées seront au mieux inutiles et au pire complètement erronées.

PS

Cet article est paru précédemment sur le site de mon ancien employeur.

My Oracle Support - Créer un profile SQL avec des hints

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…)

Enregistrement automatique des services par PMON

  • Si votre base est bien installée.
  • Si votre serveur est bien configuré.
  • Si un service est créé dans la base (ok, il y en a toujours un).
  • Si le listener LISTENER est démarré sur le port 1521.
  • Et même si les paramètres local_listener ou remote_listener ne sont pas renseignés.

ALORS pmon enregistre le service auprès du listener.

Oui mais des fois, ça ne marche pas, et on ne sait pas trop pourquoi.
(suite…)

Installer et configurer linux pour se connecter à Oracle avec PHP

Il existe une multitude de moyens pour développer des applications PHP pour Oracle, si vous souhaitez ensuite les mettre à disposition facilement c’est une autre histoire, à moins bien sûr de les livrer sous forme de machines virtuelle.

Cet article décrit pas à pas la démarche qui vous permettra de configurer une machine virtuelle Centos7 contenant un serveur Apache/PHP capable de se connecter à une base Oracle distante.

(suite…)

Installer Oracle Instant Client sur Ubuntu

L’Instant Client, permet la connexion à Oracle passer par la mise en place d’un moteur ou d’un client lourd. Son installation est au départ prévue pour RHEL, Suze ou Unbreakable Linux mais l’installation peut se faire sur d’autres plateformes. Il y a là assez peu de problématiques à surmonter mais tout de même un ou deux détails à mettre en œuvre.

Dans un premier temps il convient d’installer libaio qui est requis pour le bon fonctionnement du client.

root@ojoly # apt-get install libaio1

Ensuite il faut télécharger les fichiers rpm du logiciel en tant que tel sur le site d’Oracle (ce qui requiert un compte OTN). Il faut bien évidemment accepter la licence et télécharger le ou les packages requis (personnellement j’aime bien SQL*Plus, je télécharge donc SQL*plus avec) ce qui me fait 2 packages :

  • oracle-instantclient12.1-basic-12.1.0.1.0-1.i386.rpm
  • oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.i386.rpm

(suite…)

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…)