Discussion :

[Ikebukuro]

Bonjour,

Je voudrais identifier quelles sont les colonnes qui sont utilisées dans un Select.

Par exemple savoir que les colonnes COL1, COL2 et COL10 ont été accédées par l'ordre SQL suivant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COL1, COL2, COL10 FROM TAB1;

et pas les autres colonnes de ma table (COL1 à COL20).

Comme il n'y a pas de trigger sur Select je suis un peu embêté pour tracer ces ordres.
Je pourrais utiliser DBMS_FGA pour tracer l'ordre SQL mais je vais avoir ça : exemple avec la table DEPT.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/* Script de creation d'un audit avec resultats en XML et prise en compte du SELECT. */
BEGIN
	DBMS_FGA.ADD_POLICY (
		object_schema      =>  'USER01',
		object_name        =>  'DEPT',
		policy_name        =>  'AUDIT04',
		statement_types    =>  'SELECT',
		audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED,
		audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
END;
/
 
Test de Select
	SQL> select loc from dept;
	LOC
	-------------
	NEW YORK
	DALLAS
	CHICAGO
	BOSTON
 
Vérification de l'audit du Select.
	SQL> select policy_name, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT from 	V$XML_AUDIT_TRAIL;
 
	POLICY_NAME     OBJECT_SCHEMA     OBJECT_NAME     SQL_TEXT
	-------------------------------------------------------------------------------------------------------------------------
	AUDIT04                USER01		   DEPT	           	          select loc from dept

Dans SQL_TEXT j'ai l'ordre SQL mais je voudrais seulement les colonnes.
Je pourrais utiliser INSTR, SUBSTR pour isoler la chaîne de caractères entre SELECT et FROM mais c'est lourd puis après il faudra séparer les noms des colonnes en sa basant sur les virgules... mais bon, c'est une possibilité.


Si vous avez une idée plus simple, je suis preneur.

[mnitu]

A part l'audit et le traitement des chaines des caractères résultantes je ne vois pas d'autre solution. Bref la trace des traitements récupère également le texte des requêtes mais ça ne change en rien le problème. Je me demande aussi tout simplement à quoi vous servira toute cette information.

[Ikebukuro]

Hello mnitu,

Ca me permettra de voir si certaines colonnes sont vraiment utilisées ou non et, pourquoi pas, des supprimer : des tables moins grosses, des index peut-être supprimés aussi...
Je pourrais aussi identifier les colonnes très souvent utilisées, voir si elles ont des index ou non, et, si non, voir s'il ne faut pas les indexer.

Autre cas : dans le livre de Christian Antognini que j'ai parcouru très vite, il parle du fait que si les colonnes les plus accédées sont loin dans la table, cela peut avoir des répercussions sur les performances dont on pourrait utiliser DBMS_REDEFINITION pour les remonter.

Et puis bon, juste par curiosité aussi, je me posais la question et je n'avais pas de réponse.
En tout cas tu confirmes ce que je pensais...

[mnitu]

Ce phénomène est valable pour toute application qui a vécu un certain temps. Souvent les développeurs ont connaissance des colonnes qui trainent et qui ne sert plus à rien en réalité. Les supprimer par contre peut poser des problèmes surtout si elles sont valorisées et donc c'est à éviter ou penser aux tests de non régression.
Les colonnes présentes dans le select impact les indexes dans un seul cas, les ajouter à l'index pour éviter un accès supplémentaire à la table. C'est plutôt une mesure à prendre dans quelques cas assez rares ou en cas de crise en attendant des jours meilleurs pour une vrai solution.
Souvent il y a des autres chats à fouetter avant de se poser des problèmes des performances du au positionnement

[Ikebukuro]

Bon ben ça y est, j'ai finalement trouvé comment mettre en place cet audit des SELECT sur colonnes.
Peut-être que le code mérite d'être optimisé mais je suis content de moi

Script 01

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--
-- Object : procédure de  création d'audits sur toutes les colonnes d'une table passée en paramètre.
-- Author : 
-- Date : 07/06/2016
--
 
SET SERVEROUTPUT ON;
 
DECLARE
v_s_column_name VARCHAR2(30);
v_s_policy_name VARCHAR2(100);
CURSOR v_cur_list_column_name IS SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '&&1' ORDER BY COLUMN_ID;
 
BEGIN
	-- Création de la liste des polices d'audit : un audit par colonne de la table
	OPEN v_cur_list_column_name;
	WHILE TRUE
	LOOP
 
		v_s_column_name := NULL;
		v_s_policy_name := NULL;
 
		FETCH v_cur_list_column_name INTO v_s_column_name;
		EXIT WHEN v_cur_list_column_name%NOTFOUND;
 
		v_s_policy_name := 'AUDIT_' || '&1' || '_' || v_s_column_name;
 
		DBMS_FGA.ADD_POLICY (
		object_schema      =>  'ACCD_FRA',
		object_name        =>  '&1',
		policy_name        =>  v_s_policy_name,
		audit_column       =>  v_s_column_name,
		statement_types    =>  'SELECT',
		audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
		audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
 
	END LOOP;
	CLOSE v_cur_list_column_name;
 
	EXCEPTION
 
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Une erreur est survenue. Code : ' || TO_CHAR(SQLCODE) || '. Message : ' || SQLERRM);
 
END;
/
 
-- Verification si les polices d'audit ont bien été créées. 
select * from DBA_AUDIT_POLICIES;

Script 02

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Procédure pour calculer les stats sur les colonnes utilisées ou non dans une table avec des SELECT.
-- Avec DBMS_FGA un audit est mis sur la table pour tracer tous les SELECT.
--
-- Author : 
-- Date : 07/06/2016
 
 
-- Verification des Select exécutes.
--select * from DBA_FGA_AUDIT_TRAIL order by EXTENDED_TIMESTAMP;
 
-- Calcul des stats : le nom de la colonne auditée est dans la table DBA_AUDIT_POLICIES, colonne POLICY_COLUMN
SET LINESIZE 400
COLUMN POLICY_COLUMN FORMAT A30
COLUMN "Nb de SELECT" FORMAT A5
COLUMN "Pourcentage" FORMAT A15
 
SELECT 
	P.POLICY_COLUMN AS "Colonnes", 
	COUNT(*) AS "Nb de SELECT", 
	ROUND(COUNT(*)/(SELECT COUNT(*) FROM DBA_FGA_AUDIT_TRAIL WHERE OBJECT_NAME = '&&1') *100,2) AS "Pourcentage" 
FROM DBA_AUDIT_POLICIES P 
	JOIN DBA_FGA_AUDIT_TRAIL T 
	ON (P.POLICY_NAME = T.POLICY_NAME)
WHERE T.OBJECT_NAME = '&1'
GROUP BY P.POLICY_COLUMN
UNION
SELECT COLUMN_NAME, 0, 0 --Select des colonnes de la table sur lesquelles aucun SELECT n'a eu lieu
FROM DBA_TAB_COLUMNS
WHERE 
	TABLE_NAME = '&1' AND
	COLUMN_NAME NOT IN (
		SELECT DISTINCT P.POLICY_COLUMN 
		FROM DBA_AUDIT_POLICIES P JOIN DBA_FGA_AUDIT_TRAIL T ON (P.POLICY_NAME = T.POLICY_NAME)
		WHERE T.OBJECT_NAME = '&1')
ORDER BY 3 DESC, 1;

Exemple avec une table de tests ZZZ_DDU_TAB.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
DESC ZZZ_DDU_TAB;
 
TABLE ZZZ_DDU_TAB
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER                      
 LIB01                                              CHAR(10)                    
 LIB02                                              VARCHAR2(50)                
 DATE_ACHAT                                         DATE                        
 DATE_VENTE                                         DATE

Résultat de l'exécution du script 01.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select * from DBA_AUDIT_POLICIES; 

OBJECT_SCHEMA	OBJECT_NAME	POLICY_OWNER	POLICY_NAME	POLICY_TEXT	POLICY_COLUMN	PF_SCHEMA	PF_PACKAGE	PF_FUNCTION	ENABLED	SEL	INS	UPD	DEL	AUDIT_TRAIL	POLICY_COLUMN_OPTIONS
ACCD_FRA	ZZZ_DDU_TAB	ACCD_FRA	AUDIT_ZZZ_DDU_TAB_LIB02		LIB02				YES	YES	NO	NO	NO	DB+EXTENDED	ANY_COLUMNS
ACCD_FRA	ZZZ_DDU_TAB	ACCD_FRA	AUDIT_ZZZ_DDU_TAB_DATE_ACHAT		DATE_ACHAT				YES	YES	NO	NO	NO	DB+EXTENDED	ANY_COLUMNS
ACCD_FRA	ZZZ_DDU_TAB	ACCD_FRA	AUDIT_ZZZ_DDU_TAB_DATE_VENTE		DATE_VENTE				YES	YES	NO	NO	NO	DB+EXTENDED	ANY_COLUMNS
ACCD_FRA	ZZZ_DDU_TAB	ACCD_FRA	AUDIT_ZZZ_DDU_TAB_ID		ID				YES	YES	NO	NO	NO	DB+EXTENDED	ANY_COLUMNS
ACCD_FRA	ZZZ_DDU_TAB	ACCD_FRA	AUDIT_ZZZ_DDU_TAB_LIB01		LIB01				YES	YES	NO	NO	NO	DB+EXTENDED	ANY_COLUMNS

Je fais quelques SELECT puis je lance le script 02 qui m'affiche bien le pourcentage des colonnes sélectionnées.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Colonnes                       Nb de SELECT Pourcentage
------------------------------ ------------ -----------
ID                                        3          60
DATE_ACHAT                                1          20
LIB01                                     1          20
DATE_VENTE                                0           0
LIB02                                     0           0
 
5 rows selected.