FAQ Histograms

c’est quoi un histogram ?

Un histogram est une structure oracle qui contient pour la colonne sur lequel il porte, le nombre d’occurrence pour chaque valeur de cette colonne, ceci permettra à l’optimiseur Oracle de savoir la répartition de chacune des valeurs, et ainsi de mieux décider de faire un scan total de la table ou utiliser un index pour l’accès à une valeur.
Voilà un exemple qui montre comment l’optimiseur se comporte vis a vis d’une colonne dont la distribution des valeurs n’est pas uniforme.

1- Sans Histogram

DROP TABLE t;
CREATE TABLE t(x NUMBER);
create index t_idx on t (x);
DECLARE i NUMBER;
BEGIN
DELETE FROM t;
FOR i in 1.. 15
LOOP
INSERT INTO t VALUES(2);
END LOOP;
FOR i in 1..100000
LOOP
INSERT INTO t VALUES(8);
END LOOP;
COMMIT;
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'T',
estimate_percent => 100
);
end;
/
set autotrace traceonly explain
Prompt select x from t where x = 8;
select x from t where x = 8;
Prompt select x from t where x = 2;
select x from t where x = 2;
Résultats
select x from t where x = 8
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50008 | 146K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50008 | 146K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=8)
select x from t where x = 2
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50008 | 146K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50008 | 146K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=2)
2- avec Histogram
DROP TABLE t;
CREATE TABLE t(x NUMBER);
create index t_idx on t (x);
DECLARE i NUMBER;
BEGIN
DELETE FROM t;
FOR i in 1.. 15
LOOP
INSERT INTO t VALUES(2);
END LOOP;
FOR i in 1..100000
LOOP
INSERT INTO t VALUES(8);
END LOOP;
COMMIT;
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'T',
estimate_percent => 100
);
end;
/
begin
dbms_stats.gather_table_stats
( ownname => 'HR',
tabname => 'T',
method_opt => 'for all indexed columns size 254',
cascade => TRUE
);
end;
/
set autotrace traceonly explain
Prompt select x from t where x = 8;
select x from t where x = 8;
Prompt select x from t where x = 2;
select x from t where x = 2;
Résultats
select x from t where x = 8
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99988 | 292K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 99988 | 292K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=8)
select x from t where x = 2
Plan d'execution
----------------------------------------------------------
Plan hash value: 2946670127
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 54 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX | 18 | 54 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=2)

Est-il intéressant d’avoir un histogram sur une colonne non indexée ?

Un histogram n’est pas seulement utile pour des colonnes contenant des indexes, il peut l’être aussi pour des colonnes non-indexées, dans ce cas, ceci aidera l’optimiseur à mieux décider des ordres de jointures.

Un histogram est-il toujours utile sur une colonne ?

Non, un histogram n’est utile que s’il y a une grande disparité entre le nombre d’occurences des valeurs d’une colonnes.
Une colonne sur laquelle il y a une contrainte d’unicité ne nécessite pas un histogram puisque toutes les valeurs sont réparties uniformément : une occurrence par valeur.
Par contre les colonnes contenant des valeurs dont le nombre d’occurrences peut varier d’une facon significative, sont très sujettes à utiliser un histogram.

Comment décider de mettre ou pas un histogram sur une colonne ?

1- Il faut tout d’abord être sûr que la colonne est, ou susceptible d’être interrogée par un prédicat de filtrage, exemple : select x from t where x = 8;
pour cela on peut interroger la vue sys.col_usage$

SELECT o.object_name,
c.column_name,
u.equality_preds,
u.like_preds,
u.range_preds,
u.null_preds
FROM sys.col_usage$ u,
all_objects o,
all_tab_columns c
WHERE u.obj# = o.object_id
AND o.object_name = 'T'
AND c.column_id = u.intcol#
AND c.TABLE_NAME = o.object_name

si au moin l’un des champs : u.equality_preds, u.like_preds, u.range_preds, u.null_preds
remonte une valeur >0 ; cela veut dire que la colonne est candidate.
2- vérifier que le nombre d’occurrence des valeurs n’est pas uniformément distribué dans la colonne , pour cela , on peut calculer l’écart-type des occurrences de valeurs
SELECT ROUND(STDDEV(y) *100 / SUM(y), 0)
FROM
(SELECT COUNT(x) y
FROM t
GROUP BY x)

Si le resultats est superieur a 50, l’histogram est utile

Posted in Database, tuning | Commentaires fermés

LogMiner d’Oracle

Installation

1. Vérifier que le paramètre utl_file_dir est activé :
SQL> show parameter utl_file_dir;
Si ce paramètre n’est pas positionné, mettez le à jour avec le nom du répertoire qui contiendra les fichier générés :
SQL>ALTER SYSTEM SET utl_file_dir = "/home/oracle/tmps" COMMENT='Directory de utl_file' SCOPE=SPFILE
Il faudrai redémarrer la base pour qu’on l’instance prenne en compte ce paramètre.

2.
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle/tmp', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
3. Interroger les archivelog
Pour cela la base doit être démarrée en mode RCHIVELOG pour le savoir, il suffit d’intérroger V$databases

SQL> SELECT LOG_MODE FROM V$DATABASE;
Si le résultat est NOARCHIVELOG alors activer le mode avec la commande :

SQL> ALTER SYSTEM SET log_archive_dest = "/u01/app/oracle/oradata/orcl/archive" SCOPE=BOTH
SQL> ALTER SYSTEM SET log_archive_start = TRUE SCOPE=SPFILE

pour activer le mode archivelog il faut que la base soit uniquement « mount » et non open, pour cela :

SQL >startup mount
SQL> alter database archivelog;
SQL> alter database open;

Posted in Database, Tutorials | Commentaires fermés

Comment utiliser dbms_profiler

select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &runid) p,
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;

Posted in Trucs et Astuces | Commentaires fermés

Activer la trace 10053

SQL>ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SQL>explain plan for select * from SCOTT.EMP;
SQL>Explained

SQL>ALTER SESSION SET EVENTS '10053 trace name context off';

SQL>select value
from v$parameter
where name = 'user_dump_dest';

u01/app/oracle/diag/rdbms/orcl/orcl/trace

Aller au répertoire /u01/app/oracle/diag/rdbms/orcl/orcl/trace pour récupérer la trace générée.

Posted in Database, Trucs et Astuces, tuning | Commentaires fermés

Principes du « Cost based optimiser »

L’optimiseur Oracle disposait jusque la de deux modes de fonctionnement :

- Par règles (RBO ; Rule Based Optimiser)

- Par couts (CBO : Cost Based Optimiser)

Le mode par règle est pratiquement abandonné depuis la version 10 G, tous les efforts d’évolution d’Oracle se porte sur le mode ‘CBO’

Dans ce qui suit, nous essayons d’explorer les principes de cet optimiseur.

1. Principe de base

L’approche CBO se base essentiellement sur des statistiques récoltées périodiquement par le serveur Oracle pour l’aider à établir des plans optimaux pour l’exécution des requêtes.

Ces statistiques sont calculées, chaque 24 heures(pour la 10G) par un job Oracle installé par le script oracle catproc.sql

Posted in Database, tuning | Commentaires fermés

Requetes diagnostics tuning

Remonter le mauvais SQL

SELECT disk_reads, executions, disk_reads/executions, sql_text
FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads

SELECT buffer_gets, executions, buffer_gets/executions, sql_text
FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets

Remonter les statistiques sur l’usage des colonnes

select obj.OWNER , obj.OBJECT_NAME, obj.OBJECT_TYPE, usg.*
from all_objects obj, col_usage$ usg
where usg.OBJ#=obj.OBJECT_ID;

Posted in Database, Trucs et Astuces, tuning | Commentaires fermés