Discussion :

[ldiaz]

Salut a tous
en lisant les differents forum, j'ai capté une table de corespondance entre le pourcentage qu'il faut donner a la commande "ANALYSE" et le nombre de lignes dans la table.
Voici cette correspondance:

Code :

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

1
2
3
4
5
Fewer than 10,000 rows: Exact calculation (COMPUTE)
Fewer than 100,000 rows: ESTIMATE with 30 % of the table
Fewer than 1,000,000 rows: ESTIMATE with 10 % of the table
Fewer than 10,000,000 rows: ESTIMATE with 3 % of the table
More than 10,000,000 rows: ESTIMATE with 1% of the table

Vous confirmez cette table? ou avez-vous une approche differente?
D?autre part, je voudrais avec SQL ou PL/SQL (surement plus facil en PL)
faire une requete qui consulte les tables du shema, et en fonction du nombre de lignes, crée dynamiquement les commande ANALYSE qui correspondent.
J'ai commencé mais je coince dans les IF, peu etre ce script existe deja...

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
DECLARE 
v_table_Name VARCHAR(50);
v_nrow number;
begin
SELECT TABLE_NAME, NUM_ROWS INTO v_table_name, v_nrow
FROM DBA_TABLES;
 
 IF v_nrow >0 AND v_nrow <10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS COMPUTE');
 ELSE
 IF v_nrow >10001 AND v_nrow <100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 IF v_nrow >100001 AND v_nrow <1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 ...
end;

Ca marche ca?
D'avance merci pour le coup de main

[orafrance]

Bah teste le pour vérifier

Sinon, depuis la 9i ANALYZE est remplacé par DBMS_STATS

[ldiaz]

DBMS_STAT
oui oui ca je savais, mais ici ils veulent pas changer pour le moment .... etrange non?
Le script que j'ai fait tu crois que ca marche?
J'ai l'impression qu'il manque une boucle ou qque chose pour creer un dbms_output pour chaque table existente dans la base.
Non?

[ldiaz]

Avec ce code:

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
DECLARE 
v_table_Name VARCHAR(50);
v_nrow NUMBER;
BEGIN
SELECT TABLE_NAME, NUM_ROWS INTO v_table_name, v_nrow
FROM DBA_TABLES
WHERE OWNER='MON_USER';
 
 IF v_nrow BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF v_nrow BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF v_nrow BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END;
/

J'ai cette erreur:

Code :

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

1
2
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

si le select renvoie plus de une ligne...alors dans ce cas il faut une boucle non? un curseur?? je nage un peu la..
Ciao

[ldiaz]

Je l'ai modifier comme ceci, mais ca deconne toujours:

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
DECLARE 
v_tbl DBA_TABLES.table_name%TYPE;
v_nrow DBA_TABLES.NUM_ROWS%TYPE;
BEGIN
SELECT TABLE_NAME, NUM_ROWS INTO v_tbl, v_nrow
FROM DBA_TABLES
WHERE OWNER='RHUTST';
 
 IF v_nrow BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF v_nrow BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF v_nrow BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END;
/

Erreur:

Code :

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

1
2
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

[Severn]

Il faut boucler sur un curseur puisqu'il y a plus d'une table dans ton schema.

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
FOR c in (SELECT TABLE_NAME, NUM_ROWS
FROM DBA_TABLES
WHERE OWNER='RHUTST')
LOOP
 
 IF c.NUM_ROWS BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF c.NUM_ROWS BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF c.NUM_ROWS BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END LOOP;

[Waldar]

C'est dommage parce qu'avec DBMS_STATS Oracle fourni une estimation automatique du sample à utiliser :

Code :

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

1
2
3
4
5
exec dbms_stats.gather_table_stats                       (
          ownname          => '<user>'                   ,
          tabname          => '<table>'                  ,
          estimate_percent => dbms_stats.auto_sample_size,
          cascade          => TRUE                       );

Vous pouvez même récupérer toutes les statistiques d'un schéma en une seule commande avec dbms_stats.gather_schema_stats.

[mnitu]

C'est un serpent qui se morde la queue!

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mni@DIANA> create table t_stat as select * from user_objects;
 
Table crÚÚe.
 
mni@DIANA> select table_name, num_rows
  2  from dba_tables
  3  where table_name = 'T_STAT';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T_STAT
 
mni@DIANA>

Sans analyse NUM_ROWS ne contient pas l'information et à la prochaine analyse l'information est périmée pour prendre des décisions.

[ldiaz]

Bonjour a tous
tout d'abord merci de vos differents messages, ca aide beaucoup.
Le script marche super bien, a part un message d'overflow du cache pour le output, le reste c'est niquel. J'ai compris la boucle, mais du coup je pige pas dans quel cas utiliser un curseur en le declarant. Bref je chercherais...
.
En ce qui concernce DBMS_STAT je suis d'accord c'est dommage, mais grace a ton info, peu etre ca servira d'argument pour faire evoluer les choses.
Je vais proposer ca...

Le dernier post je pige pas trop. Tu veux dire qu'il faut analyser les tables beaucoup pour avoir toujours les dernieres mise a jour?
Merci

[mnitu]

Bonjour a tous
...
Le dernier post je pige pas trop. Tu veux dire qu'il faut analyser les tables beaucoup pour avoir toujours les dernieres mise a jour?
Merci

Non. Je dis que c’est incorrect de se baser sur DBA_TABLES.NUM_ROWS pour prendre des décisions d’analyse des tables qui modifieront DBA_TABLES.NUM_ROWS. A la limite tu devrais la faire en calculant toi même le num_rows sur chaque table. Mais, c’est tellement du gaspillage par rapport au package DBMS_STAT que personnellement je ne le ferais pas.

[ldiaz]

Bonjour,
ok .. oui je suis d'accord. Esperon que les arguments apportés seront sufisant.

Une derniere aller c'est mardi matin:
imaginons qu'ils refusent !!!
est il possible d'ajouter DBMS_STAT dans le bloc PL/SQL dont on parlait un peu plus haut, afin d'analyser les tables EXTERNES dans la foulée?
Je vais essayer, mais j'aimerais votre avis aussi...pour si jamais.

A la final j'aurais ceci:

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
BEGIN
 
 FOR c IN (SELECT TABLE_NAME, NUM_ROWS
 FROM DBA_TABLES
 WHERE OWNER='RHUPROD'
 AND TABLE_NAME LIKE 'RHU%')
 LOOP
 
 IF c.NUM_ROWS BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF c.NUM_ROWS BETWEEN 10000 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF c.NUM_ROWS BETWEEN 100000 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
 IF c.NUM_ROWS BETWEEN 1000000 AND 10000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 3 PERCENT');
 END IF;
 IF c.NUM_ROWS > 10000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 1 PERCENT');
 END IF;
 END LOOP;
 
 dbms_stats.gather_schema_stats(ownname => 'SCHEMA',
 tabname          => '<les_table_ext>'
 estimate_percent => dbms_stats.auto_sample_size, 
 method_opt=>'for all columns size auto', cascade=>true);
END;

Ca marche ca? tout ensemble? SI y'a plusieurs tables ext j'ajoute juste un tag tabname en dessous de l'autres?
merci (promis apres j'arrete)

[mnitu]

Code :

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

1
2
3
4
5
6
7
8
9
 
SQL> exec dbms_stats.gather_table_stats(user,'TRACE');
 
begin dbms_stats.gather_table_stats(user,'TRACE'); end;
 
ORA-20000: Unable to analyze TABLE "MNI"."TRACE", sampling on external table is not supported
ORA-06512: à "SYS.DBMS_STATS", ligne 13056
ORA-06512: à "SYS.DBMS_STATS", ligne 13076
ORA-06512: à ligne 2

[ldiaz]

Salut
tu veux dire que l'option qui laisse oracle faire le sample auto sur une table externe ne marche pas?
Alors il faut faire un compute complet?

J'ai trouvé ca via google:

Code :

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

1
2
3
4
5
KUP-04088 data sampling is not supported by this access driver
 
      Cause: A query of an external table requested a sample of the data returned. The access driver for the external table does not support sampling.
 
      Action: Do not try to use sampling for this external table.

a cette adresse

cette commande fonctionne:
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'schema',tabname => 'TABLE_EXT',CASCADE=> TRUE);

Donc sans aucune information de SAMPLE, ca veut dire que cette commande fait comme un COMPUTE?

D'avance merci