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

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

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

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

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

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 …

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;

Stored outlines

Pour voir quelle catégorie d’outlines est utilisée au niveau instance il suffit de suivre La note metalink (internal only, you don’t have access) ou de retrouver ses diverses transcription oveurzeunaitte comme par exemple sur le site Oracle Guy ou le blog El Caro histoire de rester sur Blogger.

Donc que est l’intérêt de ce post ??? Le français de France allons, pour les dba qui se vrillent l’œil dès que la Perfide Albion vient envahir leur champ de vision

Alors comment qu’on fait Madame ?

Simplissime, on sort le code hexa d’une partie de la SGA (ou de l’UGA si des fois on fait ça au niveau session) L’enfance de l’art non ?

Les Instructions qui vont bien :

SQL> oradebug setmypid
SQL> oradebug dumpvar sga sgauso

les résultats sont pour le moins cocaces

qolprm sgauso_ [20014F44, 20014F68) = 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000

Qui signifie : « FALSE » … Oui c’est pas forcément évident

qolprm sgauso_ [8001304C, 80013070) = 00000001 00074445 4641554C 54000000 ... 

Qui signifie : « DEFAULT » … Sachant que DEFAULT fait 7 lettres et qu’en hexadécimal DEFAULT s’écrit 4445 4641554c 54, on commence à comprendre, on la refait doucement :

qolprm sgauso_ [8001304C, 80013070) = 00000001 00074445 4641554C 54000000 ... 
7 D E F A U L T

OK ?